Help - Looking for a Sumproduct formula
Hello Friends - I am searching for a formula that will count how many
quotes and orders Jane is processing on a daily basis for a specific region. The spreadsheet I am working on has two sheets: Sheet one looks like this: A B C D Date Region Quote/Order Name 10/02/06 East Q Jane 10/02/06 West O Jane 10/02/06 West O Bill 10/02/06 West O Jane 10/02/06 West Q Jane 10/03/06 East O Bill Sheet two looks like this: Monday (10/02/06) A B C D Name Quote Orders Region Jane 1 2 West Bill 1 0 West Is there a sumproduct formula that will calculate the amount of quotes and orders Jane has processed for the west region on 10/02/06 in sheet one onto column B and C of the second sheet? My guess is I am going to need two formula's: one for column B (Quotes) and one for column C (Orders). In this example Jane processed 1 quote and 2 orders for the west region on 10/02/06. Any assistance is truly appreciated. Regards, Joe |
Help - Looking for a Sumproduct formula
One way to use only a single start formula is to set it up read the leftmost
character off the col labels in Sheet2, something like this .. Assume source data in Sheet1 cols A to D, within row2 to 100 (real dates assumed in col A) In Sheet2, Assume A1 houses a real date, say: 10/02/06 while A2:D2 houses the labels: Name Quote Orders Region Cols A and D are presumed filled with names and regions Place this in B3: =SUMPRODUCT((Sheet1!$A$2:$A$100=$A$1)*(Sheet1!$B$2 :$B$100=$D3)*(Sheet1!$C$2:$C$100=LEFT(B$2))*(Sheet 1!$D$2:$D$100=$A3)) Copy B3 to C3, fill down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ups.com... Hello Friends - I am searching for a formula that will count how many quotes and orders Jane is processing on a daily basis for a specific region. The spreadsheet I am working on has two sheets: Sheet one looks like this: A B C D Date Region Quote/Order Name 10/02/06 East Q Jane 10/02/06 West O Jane 10/02/06 West O Bill 10/02/06 West O Jane 10/02/06 West Q Jane 10/03/06 East O Bill Sheet two looks like this: Monday (10/02/06) A B C D Name Quote Orders Region Jane 1 2 West Bill 1 0 West Is there a sumproduct formula that will calculate the amount of quotes and orders Jane has processed for the west region on 10/02/06 in sheet one onto column B and C of the second sheet? My guess is I am going to need two formula's: one for column B (Quotes) and one for column C (Orders). In this example Jane processed 1 quote and 2 orders for the west region on 10/02/06. Any assistance is truly appreciated. Regards, Joe |
Help - Looking for a Sumproduct formula
One way to use only a single start formula is to set it up read the leftmost
character off the col labels in Sheet2, something like this .. Assume source data in Sheet1 cols A to D, within row2 to 100 (real dates assumed in col A) In Sheet2, Assume A1 houses a real date, say: 10/02/06 while A2:D2 houses the labels: Name Quote Orders Region Cols A and D are presumed filled with names and regions Place this in B3: =SUMPRODUCT((Sheet1!$A$2:$A$100=$A$1)*(Sheet1!$B$2 :$B$100=$D3)*(Sheet1!$C$2:$C$100=LEFT(B$2))*(Sheet 1!$D$2:$D$100=$A3)) Copy B3 to C3, fill down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Hello Friends - I am searching for a formula that will count how many quotes and orders Jane is processing on a daily basis for a specific region. The spreadsheet I am working on has two sheets: Sheet one looks like this: A B C D Date Region Quote/Order Name 10/02/06 East Q Jane 10/02/06 West O Jane 10/02/06 West O Bill 10/02/06 West O Jane 10/02/06 West Q Jane 10/03/06 East O Bill Sheet two looks like this: Monday (10/02/06) A B C D Name Quote Orders Region Jane 1 2 West Bill 1 0 West Is there a sumproduct formula that will calculate the amount of quotes and orders Jane has processed for the west region on 10/02/06 in sheet one onto column B and C of the second sheet? My guess is I am going to need two formula's: one for column B (Quotes) and one for column C (Orders). In this example Jane processed 1 quote and 2 orders for the west region on 10/02/06. Any assistance is truly appreciated. Regards, Joe |
Help - Looking for a Sumproduct formula
Hi Joe,
Provided you input your date in sheet2 cell E1, then in sheet2 cell B2 formula is : =SUMPRODUCT(--(Sheet1!$D$2:$D$7=Sheet2!$A2)*--(Sheet1!$C$2:$C$7=LEFT(B$1,1))*--(Sheet1!$B$2:$B$7=Sheet2!$D2)*--(Sheet1!$A$2:$A$7=Sheet2!$E$1)) HTH Carim |
All times are GMT +1. The time now is 08:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com