Link to home
Start Free TrialLog in
Avatar of sbjmurrieta
sbjmurrieta

asked on

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?


Avatar of bromy2004
bromy2004
Flag of Australia image
=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
Avatar of nutsch
nutsch
Flag of United States of America image
you should be able to use

--not(isna(match($D88:$D15999,{"A","B","C"},0)))
 
instead of
--($D88:$D15999= A or B or C)

Thomas
Avatar of nutsch
nutsch
Flag of United States of America image
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)
Avatar of byundt
byundt
Flag of United States of America image
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)
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
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
Avatar of byundt
byundt
Flag of United States of America image
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)

Brad
Avatar of sbjmurrieta
sbjmurrieta

ASKER

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!
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
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
 
Avatar of sbjmurrieta
sbjmurrieta

ASKER

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.

ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sbjmurrieta
sbjmurrieta

ASKER

Awesome support! These are true experts! Thank you!