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

Thanks in advance,

GW


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=496644

This should work for you.


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

Where A20:A25 is your range of data.

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=496644

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

"giantwolf" wrote
in message ...

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

Thanks in advance,

GW


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile:
http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=496644

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

Thanks in advance,

GW

--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=496644

--

Dave Peterson

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

Have a good New Year,

GW


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=496644