# SUMPRODUCT/IF/COUNTIFS formula returning a #DIV/0 error

1. ## SUMPRODUCT/IF/COUNTIFS formula returning a #DIV/0 error

Hi Excel grand wizards,

I am currently creating a workbook to calculate data for the reporting of dental healthcare. I am using Excel 2010, it has to be 2010 because that is what is on my work PC.

I have successfully created the following formula that takes the patient level data I receive from the service provider (don't worry I have put fake patient data in tab "PLD") and returns the number of individual patients that received services within a set time frame (see tab "MONTHLY" in the attached workbook in cell's D7, H7, N7, T7, Z7). one of tricks is that an individual patients details will appear multiple times for each incidence of care they receive.

=SUMPRODUCT(IF((PLD!A2:A5004<=E9)*(PLD!A2:A5004>=C9),1/COUNTIFS(PLD!A2:A5004,"<="&E9,PLD!A2:A5004,">="&C9,PLD!B2:B5004,PLD!B2:B5004),0))

This works very well and was created with the help of trawling this website (thanks).

However on tabs "INDG" and "RURAL" I want to add one more criteria, so in the tab "INDIG" in cell D7 I have placed the following formula.

=SUMPRODUCT(IF((PLD!\$A\$2:\$A\$5004<=E9)*(PLD!\$A\$2:\$A\$5004>=C9),1/COUNTIFS(PLD!K2:K5004,O2,PLD!A2:A5004,"<="&E9,PLD!A2:A5004,">="&C9,PLD!B2:B5004,PLD!B2:B5004),0))

All I have added is the additional criteria "PLD!K2:K5004,O2" but now it returns the #DIV/0 error. I have spent a collective 7 hours trying to figure t out but I am at a loss.

2. ## Re: SUMPRODUCT/IF/COUNTIFS formula returning a #DIV/0 error

try below array fromula
``Please Login or Register  to view this content.``

3. ## Re: SUMPRODUCT/IF/COUNTIFS formula returning a #DIV/0 error

Welcome to the forum!

What result are you expecting in D7?

4. ## Re: SUMPRODUCT/IF/COUNTIFS formula returning a #DIV/0 error

Thank you Samba!! That works perfectly! it is very much appreciated.

5. ## Re: SUMPRODUCT/IF/COUNTIFS formula returning a #DIV/0 error

There are currently 1 users browsing this thread. (0 members and 1 guests)