How to Count Differences in Excel

JBeaucaire · #1 Mar 21, 2008, 08:34 AM

I have to believe this is possible, but I'm stumped. If I have a list:

Dog
Dog
Cat
Cat
Hamster
Dog
Cat
Bird

I want a formula that will search Column A and tell me how many different things are in there...in this instance Formula result = 4

Any ideas?

MaggieMouse · Apr 16, 2008, 03:54 PM

you can look up help in excel-count unique value among duplicates.

mdosh01 · Apr 17, 2008, 06:57 AM

Enter "Count unique values" in Excel help and you will get two different methods. One is to use a filter, but I'll assume you prefer to use a function. Their example is for comparing two lists. In this case we only have one so you can use the following:

=SUM(IF(FREQUENCY(MATCH(B2:B9,B2:B9,0),MATCH(B2:B9 ,B2:B9,0))>0,1))

This assumes your list is in cells B2 to B9. There cannot be any blanks in the list for this to work. Don't ask me how it works. I haven't bothered to figure it out.



At Ask Me Help Desk you can ask questions in any topic and have them answered for free by our experts. To ask questions or participate in answering them you must register for a free account. By registering you will be able to: Get free answers from experts in any of our 300+ topics. Accept money for answers that you provide. Communicate privately with other members (PM). See fewer ads. Sign Up!