Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Array formula and multiplying conditions | Excel Worksheet Functions | |||
SumProduct Formula Help | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |