Sorting & Counting with multiple criteria

[Monica_Shook]

I have the following columns. The data I want to count are in PRI INS ID and CLAIM ID. Each column has duplicates in them. How do I get Excel to count the number of claims per insurance ID?

For example, if PRI INS ID is 36066 and there is a total of 17 claims, some with the same claim ID number, how can I make it count each claim number only once.

DOS

LAST NAME

FIRST NAME

PT ID

PRIMARY INS NAME

PRI INS ID

MD

CLAIM ID

CPT

PYMT

INFO

Thanks in advance. I have used the COUNTIF, which will count the number of PRI INS ID but I can't figure out how to have it count the claim ID only once for each claim ID number. 

Monica Shook

757.366.0295

[#2]

@Monica_Shook 

Monica, do you consider PivotTable as an option? Perhaps that will be the easiest way - create PivotTable on your range, creating it check Add data to data model, select Distinct Count in field settings for Claim ID.

Please check attached.

[#3]

Hi,

Using your attached workbook and assuming you put your choice of PRI INS ID in O2, then, in P2:

1) If you have access to the Office 365 functions UNIQUE and FILTER:

=COUNT(UNIQUE(FILTER(H$2:H$1000,F$2:F$1000=O2)))

2) Otherwise, array formula (CRTL+SHIFT+ENTER):

=SUM(IF(FREQUENCY(IF(F$2:F$1000=O2,H$2:H$1000),H$2:H$1000),1))

Copy down to give similar results for PRI INS ID entries in O3, O4, etc.

The one difference in results generated by these two set-ups is for PRI INS ID 127679. There is one blank entry in the CLAIM ID column for this PRI INS ID: of the two solutions given above, 1) excludes this result, 2) includes it. If 2) is the only option for you and you don't wish blank CLAIM IDs to be included then use instead:

=SUM(IF(FREQUENCY(IF(F$2:F$1000=O2,IF(H$2:H$1000<>"",H$2:H$1000)),H$2:H$1000),1))

Regards

[#4]

Hi @Monica_Shook,

Please see the attached file with your requested result, to quickly solve your query you need the new Excel Dynamic Arrays feature. Which is only available for Office365 & Excel Online

One thing to note that there are not 17 records against the PRI INS ID is 36066, there are 20 records out of which 10 Unique.

 

If something is not working as you mention, please attached file with your desired output manually entered.

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more