Counting Unique Elements of filtered list with Blank Cells

[DOTjake]

I am trying to sho the count of unique elements in a filtered column
I can count the unique elements (text and number, excluding blanks) in the entire range using the following

=SUM(IF(FREQUENCY(IF(LEN($A$6:$A$10)>0,MATCH($A$6:$A$10,$A$6:$A$10,0),""),IF(LEN($A$6:$A$10)>0,MATCH($A$6:$A$10,$A$6:$A$10,0),""))>0,1)

I got this from
Microsoft Knowledge Base Article - 268001 Counting Unique Elements with Blank Cell

My problem is that I have an autofilter on the column and would ike to see the count for only the rows which show after the filter is applied.

I tried to use the subtotal function withoput any luck, although I'm not sure I tried the right thing

Any help is greatly appreciated

TIA
DOTjake

 

[Peo Sjoblom]

Daniel Maher posted this formula

=SUM(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW(A2:A100)),,1)),(MMULT(((A2
:A100=TRANSPOSE(A2:A100)*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW
(A2:A100)),,1))))*(ROW(A2:A100)>=TRANSPOSE(ROW(A2:A100)))),ROW(A2:A100)*0+1)
=1)*1))

entered with ctrl + shift & enter

change the range to your range

--

Regards,

Peo Sjoblom

I am trying to sho the count of unique elements in a filtered column.
I can count the unique elements (text and number, excluding blanks) in the

entire range using the following:

=SUM(IF(FREQUENCY(IF(LEN($A$6:$A$10)>0,MATCH($A$6:$A$10,$A$6:$A$10,0),""),IF
(LEN($A$6:$A$10)>0,MATCH($A$6:$A$10,$A$6:$A$10,0),""))>0,1))

I got this from:
Microsoft Knowledge Base Article - 268001 Counting Unique Elements with Blank Cells

My problem is that I have an autofilter on the column and would ike to see

the count for only the rows which show after the filter is applied.

 

[Aladin Akyurek]

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),A6:A10)
)-1

which must be confirmed with control+shif+enter instead of just with enter.

The formula requires the morefunc.xll add-in
(http://longre.free.fr/english/index.html).

I am trying to sho the count of unique elements in a filtered column.
I can count the unique elements (text and number, excluding blanks) in the

entire range using the following:

=SUM(IF(FREQUENCY(IF(LEN($A$6:$A$10)>0,MATCH($A$6:$A$10,$A$6:$A$10,0),""),IF
(LEN($A$6:$A$10)>0,MATCH($A$6:$A$10,$A$6:$A$10,0),""))>0,1))

I got this from:
Microsoft Knowledge Base Article - 268001 Counting Unique Elements with Blank Cells

My problem is that I have an autofilter on the column and would ike to see

the count for only the rows which show after the filter is applied.

 

[Daniel.M]

Hi Aladin,

It will be off by 1 when the filter doesn't remove any rows (all rows
qualify). So instead of removing 1, remove 1 if COUNTA() and SUBTOTAL(3,) are
producing different values.

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),
A6:A10))-(COUNTA(A6:A10)<>SUBTOTAL(3,A6:A10))

Regards,

Daniel M.

 

[Aladin Akyurek]

Daniel,

Good point.

Aladin

Hi Aladin,

It will be off by 1 when the filter doesn't remove any rows (all rows
qualify). So instead of removing 1, remove 1 if COUNTA() and SUBTOTAL(3,) are
producing different values.

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),
A6:A10))-(COUNTA(A6:A10)<>SUBTOTAL(3,A6:A10))

Regards,

Daniel M.