count unique values in a filtered range

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.
 
D

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

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

Hope this helps!
 
C

ChrisR

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autonumber field in Excel that behaves the same as Access 0
Counting dates in filtered column 1
Count Unique Values but not Filtered or Hidden Values 3
need help on either doing a loop and counting specific values orusing subtotal function 2
How to count the numbers in a Word document? 0
Count Number of Unique Cells That Don't Have Certain Characters 0
histogram dynamic bin count 0
Counting 3

Top