Counting the Unique Numbers

  • Thread starter Thread starter Gihan Hanwella
  • Start date Start date
G

Gihan Hanwella

Hi ALL,

I need to get the number of unique entries in a particular column in
an excel table using a formula to a defined cell. how can I do that?
and this number has to change when I filter the rows using other
columns.

e.g. A table with Col A and Col B has 60 rows.
Col A has 50 Unique entries. so the Number should be 50.
I filter using Col B which will give 10 rows. in these 10 Rows there
are 4 unique entries in Col A. So the Number should be 4.

Please Help. Thank you in advance.
 
Assuming the full unfiltered range is A2:A60. Adjust to suit.

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A60,ROW(A2:A60)-ROW(A2),0,1)),MATCH("~"&A2:A60,A2:A60&"",0)),ROW(A2:A60)-ROW(A2)+1)>0,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Back
Top