Counting Unique Cells When Spread Sheet is Filtered
 

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

"carl" wrote:

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.