count unique values in a filtered range
 

I would like a unique count to return in a filtered range the same way I have
a total returned using a subtotal function (Subtotal(9,range)). This would be
on a unique idenitier for each row. Is there any way to do this in a formula,
as I'd like this to be easy to use for others? I don't want to use the
Advanced filter.

count unique values in a filtered range
 

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)0,1))

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

Hope this helps!

In article ,
ChrisR wrote:

I would like a unique count to return in a filtered range the same way I have
a total returned using a subtotal function (Subtotal(9,range)). This would be
on a unique idenitier for each row. Is there any way to do this in a formula,
as I'd like this to be easy to use for others? I don't want to use the
Advanced filter.

count unique values in a filtered range
 

Thank you , thank you!!!!

"Domenic" wrote:

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)0,1))

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

Hope this helps!

In article ,
ChrisR wrote:

I would like a unique count to return in a filtered range the same way I have
a total returned using a subtotal function (Subtotal(9,range)). This would be
on a unique idenitier for each row. Is there any way to do this in a formula,
as I'd like this to be easy to use for others? I don't want to use the
Advanced filter.