SUMPRODUCT - Additional criteria

D

Dewayne

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
 
S

S Davis

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.
 
B

Bob Phillips

S Davis said:
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.
Click to expand...

This is not an array formula, so there is no need for Ctrl-Shift-Enter
 
Top