Checking if different items are in a list

L

LAF

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.
 
R

Ron Coderre

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
 
Top