Checking if different items are in a list
 

I have a range with 1 column and 100 rows. Sometimes the range has some
blank cells and sometimes it has items in it that may be the same or
different. Is there a function that I can use to tell if the range has
more than 1 different type value in it, excluding the blank cells? For
example, if I have 5 rows with "apple" in that range and the rest are
blank, I don't need to do anything. If I have 5 rows with "apple" and
3 rows with "grapes" in that range, then I want to know that does exist
and display a message.


--
LAF
------------------------------------------------------------------------
LAF's Profile: http://www.excelforum.com/member.php...fo&userid=9656
View this thread: http://www.excelforum.com/showthread...hreadid=500164

Checking if different items are in a list
 

If the range is A1:A100...try this:

B1: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))
That function counts the number of non-blank unique values.

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=500164

Checking if different items are in a list
 

Yes, that worked. Thank you!


--
LAF
------------------------------------------------------------------------
LAF's Profile: http://www.excelforum.com/member.php...fo&userid=9656
View this thread: http://www.excelforum.com/showthread...hreadid=500164