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

Count Unique Values in a entire column, not just range

114 views
Skip to first unread message

Brian

unread,
May 15, 2009, 5:47:30 PM5/15/09
to
Howdy All,

I have a formula:

=Sum(IF(Frequency(Match(A1:A14000,A1:A14000,0),Match(A1:A14000,A1:A14000,0))>0,1))

Which will give me a count if the unique values in the in column A, Rows 1
through 14000.

What I need is a formula I can use to count the unique values in and entire
Column, A:A. The above formula does not seem to allow that.

Any ideas are greatly appreciated.

Thanks,
Brian


RagDyer

unread,
May 15, 2009, 5:55:30 PM5/15/09
to
How about just leaving out 1 row?

=SUMPRODUCT((A2:A65536<>"")/COUNTIF(A2:A65536,A2:A65536&""))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Brian" <wolf...@charter.net> wrote in message
news:%23hxADba...@TK2MSFTNGP06.phx.gbl...

T. Valko

unread,
May 15, 2009, 6:07:54 PM5/15/09
to
What version of Excel are you using?

For an entire column, (65,536 or 1,048,576 rows) using Excel's built-in
functions are pretty much out of the question. My machine locks up when I
try this on more than 50,000 rows of data.

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternative download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

Then use the COUNTDIFF function. However, this function won't accept an
entire column as a range reference. So, you'd have to set the range to one
less cell:

A1:A65535
A2:A65536

Also, see the help on COUNTDIFF since it has a few options as to
exclusions.

This function calculates very fast compared to a formula using buit-in
functions.

--
Biff
Microsoft Excel MVP


"Brian" <wolf...@charter.net> wrote in message
news:%23hxADba...@TK2MSFTNGP06.phx.gbl...

Bernd P

unread,
May 15, 2009, 7:09:45 PM5/15/09
to
Hello,

I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach
(please see entry 3 of my Excel Dont's:
http://www.sulprobil.com/html/excel_don_ts.html
) and NOT to use the Morefunc addin (entry 1 of that same list) but to
take Charles Williams' COUNTU function.

Please find my analysis on this here:
http://www.sulprobil.com/html/count_unique.html

Regards,
Bernd


T. Valko

unread,
May 15, 2009, 10:41:54 PM5/15/09
to
If the OP wants the most efficient method they *should* use the Morefunc
add-in. Not only is it the most efficient method you also get a library of
useful functions.

I tested the COUNTU function against the COUNTDIFF function (using Charles
Williams RangeTimer method).

Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation
time (5 calculations):

COUNTU(A2:A65536) = 1.42 secs
COUNTDIFF(A2:A65536) = 0.04 secs

Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries.
Average calculation time (5 calculations):

COUNTU(A2:A65536) = 0.38 secs
COUNTDIFF(A2:A65536) = 0.07 secs

--
Biff
Microsoft Excel MVP


"Bernd P" <bplu...@gmail.com> wrote in message
news:3735f7a0-3e89-4d84...@v17g2000vbb.googlegroups.com...

Domenic

unread,
May 16, 2009, 12:25:28 PM5/16/09
to
> The reason I choose it was because I have to distribute the workbook and not
> everyone will have the MoreFunc add-in.

Actually, the add-in has an option where it allows one to embed it
within the workbook. So other's don't need to install on their
computer. Note, however, it's not compatible with Mac computers.

--
Domenic
http://www.xl-central.com

In article <Ospzw1j1...@TK2MSFTNGP06.phx.gbl>,
"Brian" <wolf...@charter.net> wrote:

> Thanks to All.
>
> I've load the CountU and I am using that one.
> The reason I choose it was because I have to distribute the workbook and not
> everyone will have the MoreFunc add-in.
>
> Thanks again,
> Brian
>
> "T. Valko" <biffi...@comcast.net> wrote in message
> news:Oh0Bh$c1JHA...@TK2MSFTNGP03.phx.gbl...

Brian

unread,
May 16, 2009, 11:46:04 AM5/16/09
to
Thanks to All.

I've load the CountU and I am using that one.
The reason I choose it was because I have to distribute the workbook and not
everyone will have the MoreFunc add-in.

Thanks again,
Brian

"T. Valko" <biffi...@comcast.net> wrote in message
news:Oh0Bh$c1JHA...@TK2MSFTNGP03.phx.gbl...

Domenic

unread,
May 16, 2009, 12:49:52 PM5/16/09
to
In addition, it doesn't look like COUNTU accepts a conditional
statement, such as...

=COUNTU(IF(A2:A100="X",B2:B100))

It returns #VALUE! Or is this because the VBA code is not compatible
with my Mac computer?

--
Domenic
http://www.xl-central.com

In article <Oh0Bh$c1JHA...@TK2MSFTNGP03.phx.gbl>,

T. Valko

unread,
May 16, 2009, 1:40:21 PM5/16/09
to
>It returns #VALUE! Or is this because the VBA code
>is not compatible with my Mac computer?

Nope, only takes a range argument.

COUNTDIFF will take conditionals.

Array entered:

=COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALSE)

Didn't time this but the "eyeball test" says it's still very fast.


--
Biff
Microsoft Excel MVP


"Domenic" <som...@nospam.com> wrote in message
news:someone-742742...@msnews.microsoft.com...

Domenic

unread,
May 16, 2009, 2:41:55 PM5/16/09
to
> Nope, only takes a range argument.

That's great, thanks Biff!

--
Domenic
http://www.xl-central.com


In article <uYjVk1k1...@TK2MSFTNGP05.phx.gbl>,

Bernd P

unread,
May 16, 2009, 3:48:15 PM5/16/09
to
Hello Biff,

I see but its the license argument.

Regards,
Bernd

0 new messages