Using Sumproduct for multiple criterias

pmgmd · Jun 15th 2006

I am trying to calculate a total count with in a certian criteria. I have 4 Columns.

Report: Yes or No
Health Plan: Listing health Plans
Contracted: Yes or No
Final Disposistion: Uphold But Pay By Exception and Upheld by Utilization Management Committee

I am trying to set a criteria of Reports that will list Reports as yes under a certain health plan with a Contract Status of Yes and with a Final Disposistion of Uphold But Pay By Exception and Upheld by Utilization Management Committee

I am getting a value of zero when using the following formula.

=SUMPRODUCT(--(A1:A15="Yes"),--(B1:B15="Blue Cross MediCal"),--(G1:G15="No"),--(I1:I15="Uphold But Pay By Exception"),--(I1:I15="Upheld by Utilization Management Committee"))

Any suggestions?

Thanks in advance,
Brian

Anonymous · Jun 15th 2006

Re: Using Sumproduct for multiple criterias

Your "And" logic in th formula does not work. One of the arrays will always be 0 for every row, thus gining you a zero. You need to Add two SUMPRODUCTS together to get the AND.

=SUMPRODUCT(--(A1:A15="Yes"),--(B1:B15="Blue Cross MediCal"),--(G1:G15="No"),--(I1:I15="Uphold But Pay By Exception"))+SUMPRODUCT(--(A1:A15="Yes"),--(B1:B15="Blue Cross MediCal"),--(G1:G15="No"),--(I1:I15="Upheld by Utilization Management Committee"))

Anonymous · Jun 15th 2006

Re: Using Sumproduct for multiple criterias

Surely you are trying to test for both conditions in the I1:I15 range, so one or the other will be 0? (If I understand it...)

pmgmd · Jun 15th 2006

Re: Using Sumproduct for multiple criterias

I am new at this. Thank you

**Domenic** · Jun 15th 2006

Re: Using Sumproduct for multiple criterias

Here's another way...

=SUMPRODUCT(--(A1:A15="Yes"),--(B1:B15="Blue Cross MediCal"),--(G1:G15="No"),--ISNUMBER(MATCH(I1:I15,{"Uphold But Pay By Exception","Upheld by Utilization Management Committee"},0)))

Hope this helps!

Using Sumproduct for multiple criterias

Participate now!

Share