# Counting Distinct Values

1. ## Counting Distinct Values

Hi,

Hope you all had a good Christmas. Is there a way to count the number of distinct values that appear in a column either by a formula or macro?

ie. in this list it would return the value 4 as orange appears twice so should only be counted once.

apple
orange
pear
orange
grape

GW

2. This should work for you.

=SUMPRODUCT((A20:A25<>"")/COUNTIF(A20:A25,A20:A25&""))

Where A20:A25 is your range of data.

Cheers,

Steve

3. ## Re: Counting Distinct Values

Visit Chip's page www.cpearson.com
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"giantwolf" <giantwolf.20t27a_1135866000.6149@excelforum-nospam.com> wrote
in message news:giantwolf.20t27a_1135866000.6149@excelforum-nospam.com...
>
> Hi,
>
> Hope you all had a good Christmas. Is there a way to count the number
> of distinct values that appear in a column either by a formula or
> macro?
>
> ie. in this list it would return the value 4 as orange appears twice so
> should only be counted once.
>
> apple
> orange
> pear
> orange
> grape
>
>
> GW
>
>
> --
> giantwolf
> ------------------------------------------------------------------------
> giantwolf's Profile:
> http://www.excelforum.com/member.php...o&userid=24718
>

4. ## Re: Counting Distinct Values

one way:

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

(adjust the range to match, but don't use the whole column)

giantwolf wrote:
>
> Hi,
>
> Hope you all had a good Christmas. Is there a way to count the number
> of distinct values that appear in a column either by a formula or
> macro?
>
> ie. in this list it would return the value 4 as orange appears twice so
> should only be counted once.
>
> apple
> orange
> pear
> orange
> grape
>
>
> GW
>
> --
> giantwolf
> ------------------------------------------------------------------------
> giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718

--

Dave Peterson

5. Thanks guys, the sumproduct/countif formula worked perfectly. Much appreciated.

Have a good New Year,

GW

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1