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

Count Unique Values but not Filtered or Hidden Values 3
Return the filtered value into a specific cell 4
Count of unique filtered values 1
Average of Subtotals in Pivot Table 0
Sum after Filter, Count after Fliter 2
SUMPRODUCT has mismatched range sizes error when sheet opened inGoogle Excel. 1
Count Unique Values in 1 Column based on Date Range in another Column 28
Subtotal of Abs of a range? 3

Top