Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help please - looking for a formula
Hello Friends - I am hoping someone can help guide me to the end result
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 Tuesday 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). Your assistance is truly appreciated. Regards, Joe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help please - looking for a formula
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help please - looking for a formula
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help please - looking for a formula
Thank you for your time and willingness to help. However, I did change
my first table to TBL as instructed and the result returned was #name? Do I need to include an exclamation after TBL? Harlan Grove wrote: wrote... Thank you for the formulas! However, I cannot get them to work (user error I am certain). Considering the result I am getting is #name? Any suggestions? ... First suggestion, for follow-up questions respond to responses rather than to your original posting. Second, if you didn't name your first table TBL then replace the instances of Tbl in my formulas with the range reference for your first table. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help please - looking for a formula
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
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 |