count unique values with auto-filter on

[Francisco Rodriguez]

Hello,

I'm trying to count unique numbers in a resulting list after filtering. I
have already learned how to count unique number in an unfiltered list using:

=SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)>0,1))

but now I need to know how many unique numbers remain after I filter for
another column. Help!

Cisco

 

[T. Valko]

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW(A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A40)-ROW(A12)+1)>0,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Francisco Rodriguez]

That worked like a charm. Thanks for your help and expertise!

Francisco

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Santos Santiago]

Hello, Instead of count numbers, how does this array formula can be modified to count names?

 

[Gord Dibben]

Count unique names in D1100

=SUMPRODUCT((D1100<>"")/COUNTIF(D1100,D1100&""))

Not an array formula.


Gord Dibben MS Excel MVP

 

[Jerry van Kalleveen]

Another thing.
What if the unique values to be counted are not in one column? What if, as in my case, that would be range A12:B40 instead of A12:A40 ?
Could the same basic formula construction still be used and if yes what would have to be the changes to it? My comprehension of it is insufficient to work that out for myself. I know it is not simply replacing all A40 with B40.

Jerry