count unique values in a filtered range

  • Thread starter Thread starter ChrisR
  • Start date Start date
C

ChrisR

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.
 
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!
 
Thank you , thank you!!!!

Domenic said:
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!
Click to expand...
 
Back
Top