Unique Count Based on Certain Criteria

**MattJ147** · 10-08-2014, 06:27 PM

Hello! I appreciate in advance anyone who helps me with this.

In this example, I have one sheet with each individual charge from different doctors. Column A is the doctor, column B is the charge code, and column C is the claim number. Since a claim can have multiple charge codes, there are many duplicate claim numbers in column C.

What I am trying to do on a separate sheet is create a formula to pull the number of unique claims for each doctor. I came across the following formula on another site:

=SUM(IF(FREQUENCY(IF(LEN('reference sheet'!C:C)>0,MATCH('reference sheet'!C:C,'reference sheet'C:C,0),""), IF(LEN('reference sheet'C:C)>0,MATCH('reference sheet'C:C,'reference sheet'C:C,0),""))>0,1))

This formula successfully returns the number of unique claim numbers, but what I need to do now is essentially insert "if column A is Dr. Smith". I have tried and tried to figure out how and where to insert this variable, but I have not figured it out. I am wondering now whether the formula above will even work for this, or if I need to use a DCOUNT/DCOUNTA/COUNTIF/COUNTIFS or something like that.

Thanks again for any and all help!

**daddylonglegs** · 10-08-2014, 07:00 PM

It's probably best with these type of formulas not to use entire columns - that will slow the formula down - for data in rows 2 to 1000 you can use this version in F2 copied down assuming you have doctors names in E2 down

=SUM(IF(FREQUENCY(IF('reference sheet'!C$2:C$1000<>"",IF('reference sheet'!A$2:A$1000=E2,MATCH('reference sheet'!C$2:C$1000,'reference sheet'!C$2:C$1000,0))),ROW('reference sheet'!C$2:C$1000)-ROW('reference sheet'!C$2)+1),1))

That works for any type of data in column C - if you have numbers in there (not text or alphanumeric) you could try this shorter version:

=SUM(IF(FREQUENCY(IF('reference sheet'!A$2:A$1000=E2,'reference sheet'!C$2:C$1000),'reference sheet'!C$2:C$1000),1))

both formulas need to be "array entered"

**MattJ147** · 10-08-2014, 08:28 PM

Thank you for the info. I put the name of my sheet in place of 'reference sheet', replaced =E2 with ="SMITH, DOCTOR" and did the Ctrl + Shift + Enter. I'm getting an error saying "formula refers to empty cells". Is it ok to do the ="SMITH, DOCTOR" like I did? Because I don't have the doctor's names on my current sheet listed exactly like they are on the reference sheet.

Also, not to jump ahead, but if I have multiple variables, how/where would I put them in? For example, I also need to add conditions that say if column L = "This" and if column M = "That".

Thanks so much for the help!