Using the OR function with SUMPRODUCT in Excel 2003

January 4, 2012 at 15:36:52
Specs: Windows XP
I am currently using the SUMPRODUCT function in Excel 2003 to calculate how many entries there are for specific criteria.

Current Formula:
=SUMPRODUCT((Query1!$A$2:$A$223="A - Critical")*(Query1!$B$2:$B$223="Severity 1 - Critical")*(Query1!$G$2:$G$223="Production Validation (Smoke)"))

Now I need to be able to calculate on multiple values in the G column. However, I can not figure out the correct formula for calculating multiple values using the OR function. Can it be done using the SUMPRODUCT function or do I need to use a different function?

Below is what I need calculate:
=SUMPRODUCT((Query1!$A$2:$A$223="A - Critical")*(Query1!$B$2:$B$223="Severity 1 - Critical")*(Query1!$G$2:$G$223="Production Validation (Smoke)" OR Query1!$G$2:$G$223="Testing - Systems Integration" OR Query1!$G$2:$G$223="Testing - System"))

Any help would be appreciated!

Thanks,
Tim


See More: Using the OR function with SUMPRODUCT in Excel 2003

Report •


#1
January 4, 2012 at 16:29:33
Try:

=SUMPRODUCT(($A$2:$A$223="A - Critical")*($B$2:$B$223="Severity 1 - Critical")*ISNUMBER(MATCH($G$2:$G$223,{"Production Validation (Smoke)","Testing - Systems Integration","Testing - System"})))

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question