Checking on sumproduct formula

[bbenaway]

In the attached photo, am needing a formula that will pick up on a month (Jan) in column B, and pick up on both names (nancy and Lynn) in column D, and then look throughout the spreadsheet E9:X24 and give the total number. The tab for the sheet with the data is called "Production". The formula is embedded on another worksheet.

This is the formula that I used, but for some reason it is not adding up the numbers correctly.

=SUMPRODUCT(((Production!B9:B24="Jan")+(Production!d9:d24="nancy")+(Production!d9:d24="lynn")*Production!e9:x24))

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT
(
(Production!B9:B24="Jan")*
(Production!d9:d24="nancy")*
(Production!d9:d24="lynn")*
(Production!e9:x24)
)
[/tt]
it is the PRODUCT of all that is SUMMED.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Replace the first "+" with a "*".

Also, look at your logic - you are asking for only records where Production!d9:d24="nancy" AND only records where Production!d9:d24="lynn". A cell can't very well meet both criteria, can it?

So split it up into two different SumProduct formulas and add them together. Something like"
=[blue]SUMPRODUCT((Production!B9:B24="Jan") * (Production!d9:d24="nancy") * ...)[/blue] + [red]SUMPRODUCT(((Production!B9:B24="Jan") * (Production!d9:d24="lynn")* ...)[/red]

Also, I'd recommend using real dates rather than the text string "Jan"

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Nice catch, John. I missed conflicting criteria values.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}