Counting Unique Elements of filtered list with Blank Cells

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,OFFS ET(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" wrote in message
...
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.

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

=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" wrote in message
...
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.

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

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" wrote in message
...
=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" wrote in message
...
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.

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,

Good point.

Aladin

"Daniel.M" wrote in message
...
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" wrote in message
...

=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" wrote in message
...
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.

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