Counting Unique Elements of filtered list with Blank Cells

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

Guest

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
 
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


DOTjake said:
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
Click to expand...
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
Click to expand...
the count for only the rows which show after the filter is applied.
 
=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).

DOTjake said:
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
Click to expand...
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
Click to expand...
the count for only the rows which show after the filter is applied.
 
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.
 
Daniel,

Good point.

Aladin

Daniel.M said:
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.
Click to expand...
 
Back
Top