Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Additional criteria
The formula below adds up the number of reports by the month.
=SUMPRODUCT((MONTH($E$8:$E$75)=1)*($e$8:$e$75<"") I need to add additional criteria to the formula which includes the name of the sales person. The end result needs to be the number of reports by month by sales person. Thank you for any help -- Dewayne |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Additional criteria
Well, create a list of salesperson's from Z1 to Z10. Then, assuming
that the salesperson's are contained within the data in column B1:B10, use this formula: =SUMPRODUCT((MONTH($E$8:$E$75)=1)*($e$8:$e$75<"") *($b$1:$b$10=Z1)) Drag that formula down from AA1 - AA10 (so that it refers to the name's of each salesperson). Ensure you press ctrl-shift-enter to enter the formula the first time of course. Also, try creating named ranges as it will make things a lot easier to understand. Dewayne wrote: The formula below adds up the number of reports by the month. =SUMPRODUCT((MONTH($E$8:$E$75)=1)*($e$8:$e$75<"") I need to add additional criteria to the formula which includes the name of the sales person. The end result needs to be the number of reports by month by sales person. Thank you for any help -- Dewayne |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Additional criteria
"S Davis" wrote in message oups.com... Well, create a list of salesperson's from Z1 to Z10. Then, assuming that the salesperson's are contained within the data in column B1:B10, use this formula: =SUMPRODUCT((MONTH($E$8:$E$75)=1)*($e$8:$e$75<"") *($b$1:$b$10=Z1)) Drag that formula down from AA1 - AA10 (so that it refers to the name's of each salesperson). Ensure you press ctrl-shift-enter to enter the formula the first time of course. This is not an array formula, so there is no need for Ctrl-Shift-Enter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Criteria Via Cell Reference?? | Excel Worksheet Functions | |||
SumProduct with criteria list | Excel Worksheet Functions | |||
Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |