• Status: Solved
• Priority: Medium
• Security: Public
• Views: 366

# SUMPRODUCT with MULTIPLE Criteria

Column G=Employ Number (G88:G15999)
Column C= Month
Column D = Equipment Type either A,B, or C
Column Q = \$ Sum of equipment sold

I want to Sum the Sales of model "A, B, or C' equipment sold in each month by employee number.

My formula =SUMPRODUCT(--(\$G\$88:\$G\$15999=\$C16),--(\$D88:\$D15999= A or B or C),--(\$C88:\$C15999>=Month Start),--(\$C88:\$C15999<=<Month End),\$Q\$88:Q\$15999

My issue is the second item and wanting it to search for A or B or C and in any one of those cases sum the total in Q88:Q15999 if all the other criteria are met as well.

Any ideas?

0
sbjmurrieta
• 3
• 3
• 2
• +2
1 Solution

Commented:
=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= A or B or C)*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*(\$Q\$88:Q\$15999))
try that.
Change  A or B or C to a Cell reference that has the value
0

Commented:
you should be able to use

--not(isna(match(\$D88:\$D15999,{"A","B","C"},0)))

--(\$D88:\$D15999= A or B or C)

Thomas
0

Commented:
or as total formula:
=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*not(isna(match(\$D88:\$D15999,{"A","B","C"},0)))*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)
0

Commented:
I don't believe you need the NOT(ISNA(MATCH    as long as you can enter the actual name being sought instead of "A", "B" and "C" in the SUMPRODUCT

=SUMPRODUCT(--(\$G\$88:\$G\$15999=\$C16),--(\$D88:\$D15999= {"A","B","C"}),--(\$C88:\$C15999>=Month Start),--(\$C88:\$C15999<=<Month End),\$Q\$88:Q\$15999)
0

Commented:
Surely that won't work, Brad? If you use ={"A","B","C"} syntax then you need to use * between conditions rather than ,--

Personally I'd use Thomas' suggestion but instead of NOT(ISNA you can use ISNUMBER

Regards, barry
0

Commented:
sbjmurrieta,
Barry is correct--I used the * between conditions when I tested before posting. And sure enough, it doesn't work with the commas when I test right now.

You lose the ability to tolerate text in column Q with this formula, but it shows what I should have posted:
=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= {"A","B","C"})*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)

0

Author Commented:
My "A,"B,"C is actually a list with dozens of possibilities.  WHw would the formula change i I changed from ext to search  range? For example:

=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= {"A","B","C"})*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)

=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= {\$GW5:\$GW\$15})*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)

Thank you!
0

Commented:
If your range to check is a column like yours (i.e. GW5:GW15) then the easiest way is to revert to Thomas' suggestion with MATCH, although I'd use ISNUMBER as indicated above, i.e.
=SUMPRODUCT((\$G88:\$G15999=\$C16)*ISNUMBER(MATCH(\$D88:\$D15999,\$GW5:\$GW15,0))*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End),\$Q88:\$Q15999)
regards, barry

0

Author Commented:
When I use the formula you suggested above, it returns a "N/A." in each cell I put it in. Not sure what's wrong.

0

Commented:
OK, I tested and found a typo with <=< and I assume Month End and Month Start are named ranges, in which case they can't have spaces so I changed to MonthStart and MonthEnd. I assume column C has dates?
I used this version in a test sheet and it gave me a result with some dummy data
=SUMPRODUCT((\$G88:\$G15999=\$C16)*ISNUMBER(MATCH(\$D88:\$D15999,\$GW5:\$GW15,0))*(\$C88:\$C15999>=MonthStart)*(\$C88:\$C15999<=MonthEnd),\$Q88:\$Q15999)
That formula is in M2 (shaded yellow) and criteria cells (included names ranges) are coloured red as are the rows that satisfy the criteria, see attached
regards, barry

25029396.xls
0

Author Commented:
Awesome support! These are true experts! Thank you!
0

## Featured Post

• 3
• 3
• 2
• +2
Tackle projects and never again get stuck behind a technical roadblock.