Thanks:  0
Likes:  0

# Thread: Count unique values in filtered list

1. ## Count unique values in filtered list

Is it possible to count the number of unique values in a list that is filtered without using a helpcolumn? I guess I need to use the subtotals in some way.

2. ## Re: Count unique values in filtered list

try this:
and name the range a10:a16 data

Sheet2

 A 10 1000000006 11 1000000006 12 1000000006 13 1000000007 14 1000000008 15 1000000008 16 1000000008 17 18 3

 Cell Formula A18 {=COUNTIF(data,data)}
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

martin

3. ## Re: Count unique values in filtered list

Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER. Adjust the range, accordingly.

Fantastic!!

5. ## Re: Count unique values in filtered list

Originally Posted by Domenic
Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER. Adjust the range, accordingly.
Domenic,
I was searching for a similar solution, and the one you posted worked perfectly for me.

My needs were to have a unique task count on colum C data starting at row 7, that is where my autofilters were set. So, as filters are set on other colums I am getting the correct unique value counts based on those filters.
Thank you!
Lloyd