Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Counting Unique Elements of filtered list with Blank Cells

18 views
Skip to first unread message

DOTjake

unread,
Dec 16, 2003, 11:51:18 AM12/16/03
to
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

Peo Sjoblom

unread,
Dec 16, 2003, 12:38:06 PM12/16/03
to
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" <anon...@discussions.microsoft.com> wrote in message
news:F733E686-D8A4-4EC3...@microsoft.com...

Aladin Akyurek

unread,
Dec 16, 2003, 4:51:49 PM12/16/03
to
=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" <anon...@discussions.microsoft.com> wrote in message
news:F733E686-D8A4-4EC3...@microsoft.com...

Daniel.M

unread,
Dec 16, 2003, 6:30:41 PM12/16/03
to
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" <aky...@xs4all.nl> wrote in message
news:3fdf7fa7$0$229$e4fe...@news.xs4all.nl...

Aladin Akyurek

unread,
Dec 18, 2003, 6:30:08 PM12/18/03
to
Daniel,

Good point.

Aladin

"Daniel.M" <prenom...@bigfoot.inutil.com> wrote in message
news:eebDguCx...@TK2MSFTNGP11.phx.gbl...

0 new messages