Count how many distinct values (or get list of distinct values) in a filtered column

Question

Is there a way to count the number of distinct values in a filtered column in Excel?

Using the formula at https://exceljet.net/formula/count-unique-values-in-a-range-with-countif I can count the number of distinct values in a range, but when that range has been filtered with an auto-filter I still get the unfiltered count.

For example:

     A          B
1    Scarf      Blue
2    Hat        Red
3    Gloves     Green
4    Coat       Blue
5    Balloon    Red
6    Shoes      Blue

Counting unique values in B with =SUMPRODUCT((B1:B6<>"") / COUNTIF(B1:B6,B1:B6 & "")) should return 3 as the distinct values are Red, Green and Blue.

If I auto filter Column B to just select Red items, the resulting table will look like:

     A          B
2    Hat        Red
5    Balloon    Red

In this case the number of distinct values retuned should be 1. But the formula above still returns 3.

The formula should also cope with multiple selections in the auto-filter, so for example filtering for Blue and Green should result in the following table:

     A          B
1    Scarf      Blue
3    Gloves     Green
4    Coat       Blue
6    Shoes      Blue

From which the formula should return 2 (Blue, Green).

Finally, if I am filtering on column A rather than B, the formula should still work. So If I am only interested in Hat, Scarf and Coat, filtering column A for these values would result in:

     A          B
1    Scarf      Blue
2    Hat        Red
4    Coat       Blue

From which the formula should return 2.

(I'm using Excel 2013 and need to do this in a formula rather than using VBA etc)

I also found this page on office.com which I thought might help, but alas I can't get it to work for me.

Tom Sharpe Tom Sharpe · Accepted Answer · 2017-01-11T13:36:06

This reference shows how you can exclude hidden rows using AGGREGATE

Excluding hidden rows with AGGREGATE

You can then use a standard way of counting unique values like this

Counting unique values with FREQUENCY

So if you were counting values in column B, you would need a helper column (say C) containing

=IF(AGGREGATE(3,5,B2),B2,"")

Then plug in the form of count unique that ignores empty cells

=SUM(IF(FREQUENCY(IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""), IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""))>0,1))

Or your formula if you prefer

=SUMPRODUCT((C2:C10<>"") / COUNTIF(C2:C10,C2:C10 & ""))

Count how many distinct values (or get list of distinct values) in a filtered column

1 Answers