Count Unique Filtered Records

[BobZZ]

There are many examples of how to count filtered records, but how can I count unique filtered records. Meaning, I have set a filter on a different column, and I would like to count the unique records in column 'C'. Suppose I set a filter on some other column like column 'X' and the filtered data in column 'C' would look like:

A
A
A
B
C
C
C
C
D
D

So the number of unique records should be 4 for column 'C'.

[leelnich]

Hi Bob - Use the SUBTOTAL function in a helper column to identify visible rows (column D in the example workbook):

Please Login or Register  to view this content.

Then use the following ARRAY FORMULA* to count unique visible labels:

Please Login or Register  to view this content.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

[kvsrinivasamurthy]

ARRAY Formula to get total count of unique records in filtered data without actually not filtering data.
Data A2:C12
unique count of records with A column="aa' and B column="bb" is

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

[leelnich]

Bob - thank you for the rep! If "complete", please mark your thread as SOLVED (Thread Tools, up top). Regards - Lee