# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Count Distinct Values?

## bill_morgan

The sheet contains 30,000 rows. I need to determine the number of distinct
(unique) values in column A.  What is the most efficient way to do this in
Excel?

For example, I can pull the data into Access and use a select query to
"group by" column A to get the answer.  But I need to get the answer within
Excel.

Thanks for your help ...

Bill Morgan

----------


## JulieD

Hi Bill

check out
http://www.cpearson.com/excel/duplicat.htm
about half way down the page there's a section entitled counting unique
entries in a range which give a couple of methods.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"bill_morgan" <billmorgan@discussions.microsoft.com> wrote in message
news:C8CBAAA9-A658-475A-8343-F8242E211802@microsoft.com...
> The sheet contains 30,000 rows. I need to determine the number of distinct
> (unique) values in column A.  What is the most efficient way to do this in
> Excel?
>
> For example, I can pull the data into Access and use a select query to
> "group by" column A to get the answer.  But I need to get the answer
> within
> Excel.
>
> Thanks for your help ...
>
> Bill Morgan
>
>

----------


## RagDyeR

Try this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000

With this formula, you *cannot* use an entire column as a reference (A:A).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
&""))

"bill_morgan" <billmorgan@discussions.microsoft.com> wrote in message
news:C8CBAAA9-A658-475A-8343-F8242E211802@microsoft.com...
The sheet contains 30,000 rows. I need to determine the number of distinct
(unique) values in column A.  What is the most efficient way to do this in
Excel?

For example, I can pull the data into Access and use a select query to
"group by" column A to get the answer.  But I need to get the answer within
Excel.

Thanks for your help ...

Bill Morgan

----------


## RagDyeR

For some reason, the end of the formula was cut off.

Try this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))
--

Regards,

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

"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:u44lMilSFHA.3156@TK2MSFTNGP15.phx.gbl...
Try this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000

With this formula, you *cannot* use an entire column as a reference (A:A).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
&""))

"bill_morgan" <billmorgan@discussions.microsoft.com> wrote in message
news:C8CBAAA9-A658-475A-8343-F8242E211802@microsoft.com...
The sheet contains 30,000 rows. I need to determine the number of distinct
(unique) values in column A.  What is the most efficient way to do this in
Excel?

For example, I can pull the data into Access and use a select query to
"group by" column A to get the answer.  But I need to get the answer within
Excel.

Thanks for your help ...

Bill Morgan

----------


## bj

fantastic

"RagDyeR" wrote:

> For some reason, the end of the formula was cut off.
>
> Try this:
>
> =SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:u44lMilSFHA.3156@TK2MSFTNGP15.phx.gbl...
> Try this:
>
> =SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000
>
> With this formula, you *cannot* use an entire column as a reference (A:A).
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
> &""))
>
> "bill_morgan" <billmorgan@discussions.microsoft.com> wrote in message
> news:C8CBAAA9-A658-475A-8343-F8242E211802@microsoft.com...
> The sheet contains 30,000 rows. I need to determine the number of distinct
> (unique) values in column A.  What is the most efficient way to do this in
> Excel?
>
> For example, I can pull the data into Access and use a select query to
> "group by" column A to get the answer.  But I need to get the answer within
> Excel.
>
> Thanks for your help ...
>
> Bill Morgan
>
>
>
>
>

----------


## CEJ

I have a similar situation. However, I have 3 sheets inside my spreadsheet and I need to count the unique values in the same column across all three sheets.

For example, I can use the following formula (that I found in this thread) to count the values in one sheet:

=SUMPRODUCT((G12:G247<>"")/COUNTIF(G12:G247,G12:G247&""))

If my sheets are named SheetA, SheetB, SheetC, how can I modify this forumula to look at all three sheets? The column and row values are the same in each sheet. Since it is very likely that the same value will appear in one or more of the sheets, I can't just add the individual sheet sums together.

----------


## RagDyeR

Thanks for the feed-back.
--

Regards,

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

"bj" <bj@discussions.microsoft.com> wrote in message
news:02E21F1D-5AFE-45CB-8223-9FC3F90A8AA0@microsoft.com...
fantastic

"RagDyeR" wrote:

> For some reason, the end of the formula was cut off.
>
> Try this:
>
> =SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:u44lMilSFHA.3156@TK2MSFTNGP15.phx.gbl...
> Try this:
>
> =SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000
>
> With this formula, you *cannot* use an entire column as a reference (A:A).
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
> &""))
>
> "bill_morgan" <billmorgan@discussions.microsoft.com> wrote in message
> news:C8CBAAA9-A658-475A-8343-F8242E211802@microsoft.com...
> The sheet contains 30,000 rows. I need to determine the number of distinct
> (unique) values in column A.  What is the most efficient way to do this in
> Excel?
>
> For example, I can pull the data into Access and use a select query to
> "group by" column A to get the answer.  But I need to get the answer
within
> Excel.
>
> Thanks for your help ...
>
> Bill Morgan
>
>
>
>
>

----------


## bill_morgan

Thank you, Julie.  I'm going there now to check it out....

"JulieD" wrote:

> Hi Bill
>
> check out
> http://www.cpearson.com/excel/duplicat.htm
> about half way down the page there's a section entitled counting unique
> entries in a range which give a couple of methods.
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "bill_morgan" <billmorgan@discussions.microsoft.com> wrote in message
> news:C8CBAAA9-A658-475A-8343-F8242E211802@microsoft.com...
> > The sheet contains 30,000 rows. I need to determine the number of distinct
> > (unique) values in column A.  What is the most efficient way to do this in
> > Excel?
> >
> > For example, I can pull the data into Access and use a select query to
> > "group by" column A to get the answer.  But I need to get the answer
> > within
> > Excel.
> >
> > Thanks for your help ...
> >
> > Bill Morgan
> >
> >
>
>
>

----------


## bill_morgan

RagDyeR,

This works...!  Not sure how, yet, but I am working on that.  Thanks so much
for your reply.

"RagDyeR" wrote:

> For some reason, the end of the formula was cut off.
>
> Try this:
>
> =SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:u44lMilSFHA.3156@TK2MSFTNGP15.phx.gbl...
> Try this:
>
> =SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000
>
> With this formula, you *cannot* use an entire column as a reference (A:A).
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
> &""))
>
> "bill_morgan" <billmorgan@discussions.microsoft.com> wrote in message
> news:C8CBAAA9-A658-475A-8343-F8242E211802@microsoft.com...
> The sheet contains 30,000 rows. I need to determine the number of distinct
> (unique) values in column A.  What is the most efficient way to do this in
> Excel?
>
> For example, I can pull the data into Access and use a select query to
> "group by" column A to get the answer.  But I need to get the answer within
> Excel.
>
> Thanks for your help ...
>
> Bill Morgan
>
>
>
>
>

----------

