count unique values with auto-filter on

  • Thread starter Thread starter Francisco Rodriguez
  • Start date Start date
F

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
 
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.
 
Hello, Instead of count numbers, how does this array formula can be modified to count names?
 
Count unique names in D1:D100

=SUMPRODUCT((D1:D100<>"")/COUNTIF(D1:D100,D1:D100&""))

Not an array formula.


Gord Dibben MS Excel MVP
 
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
 
Back
Top