Counting Duplicates as 1

Hi Everyone,

I have reached my ceiling of excel knowledge here though the task doesn't sound as if it should be this difficult. I am trying to summate the total number of patients that a particular physician has referred to us. The data I'm given lists multiple duplicates of the same patient and evaluation date for various reasons.... . The issue I"m running into is trying to find a way to count my duplicates as 1 before summing my total. Key point: Duplicates are denoted by having the same Patient Name and Evaluation Date. The columns in the workbook are named as "ReferringMD", "PatientName" and "EvalDate" repectively; All names (which are now numbers), dates and physician names (Gameshow host Bob Barker ) on the attached sheet are ficticious. All raw data is listed in the "Data Input" tab.

I would really appreciate any help here.

THANK YOU!

Hi Glenn,

I was expecting formulas such as Countifs( with others embeded would be a solution here. If Excel Formulas and Equations is the wrong place to post this, could you please direct me to the correct area?

Thank You.

Hi Everyone,

THANK YOU!!! I have not been able to get back to work to check these solutions due to my school schedule though I have today. Both solutions gave me a path to a final product. By the way, nice recognition noting that my patient numbers were extended fractions and thus would never equal each other. Ultimately I used KDibbins solution adjusting the final output to an integer of 1 if the counifs( returned a value above 1.

Please Login or Register  to view this content.

This allowed me to integrate this product into other formulas creating a much more thurough report. Thank you both again. In case anyone is interested in how this was used. I checked it with the equation below to verify if a patient had been evaluated within our specified date range while also excluding any duplicate patient accounts for the same evaluation date. I was unable to get a functional array count to work to count patients whom did or didn't have a filled in discharge date which would have made this formula much cleaner, but adding each individual formula seperately worked just fine.

Please Login or Register  to view this content.