Home |
Search |
Today's Posts |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
wrote...
.... I am trying to accomplish. I am searching for a formula that will count how many quotes and orders each person is completing on a daily basis for a specific region. 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/03/06 West Q Jane 10/03/06 East O Bill Sheet two looks like this: Monday .... A B C D Name Quote Orders Region Jane 1 1 West Bill 1 0 West In this example Jane processed one quote and one order on 10/02/06 for the west region. Is there a formula that will calculate those qty's from 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). Formulas for the values in the Quote and Order columns in the second table are fairly simple. If your entire first table were named TBL and your second table began in cell A1 of the other worksheet, try these formulas. B2 [Jane's West Quotes]: =SUMPRODUCT(--(INDEX(TBL,0,4)=$A2),--(INDEX(TBL,0,1)=DATE(2006,10,2)), --(INDEX(Tbl,0,2)=$D2),--(INDEX(Tbl,0,3)="Q")) C2 [Jane's West Orders]: =SUMPRODUCT(--(INDEX(TBL,0,4)=$A2),--(INDEX(TBL,0,1)=DATE(2006,10,2)), --(INDEX(Tbl,0,2)=$D2),--(INDEX(Tbl,0,3)="O")) The awkward bit here is using the relatively hardcoded DATE(2006,10,2) for the date sought in Tbl. Otherwise these are straightforward conditional sums using SUMPRODUCT. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |