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

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

Ask Your Question

Weekly Poll