Counting Unique Cells When Spread Sheet is Filtered

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table like so:

ColA ColB
A 1
B 1
C 1
D 2
A 3
B 2
C 3

Is there a formula that I can place in ColA that will count the unique
cells, even when cColB is filtered>

For example, if ColB is not filtered, the formula would count 4 unique cells
(A,B,C,D)

If ColB were filtered to 1, the formula would count 3 unique cells (A,B,C).

Thank you in advance.
 
Try

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-MIN(ROW(A2:A100)),0,1)),MATCH(A2:A100,A2:A100,0)),IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-MIN(ROW(A2:A100)),0,1)),MATCH(A2:A100,A2:A100,0)))>0,1,0))

entered with ctrl + shift & enter

Assumes data starts in A2


Regards,

Peo Sjoblom
 
Back
Top