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.
Thank you in advance for your help!
Bookmarks