Sorting & Counting with multiple criteria

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