X

How To Get Total Number Without Repetition?

I’ve got a list right now, and  I need to know how many people are on it.

I took a look and I knew there were 19 people, but I realized that there were a lot of names that were repeated.   So how do I quickly figure out the total number of people without repetition?

Well, there is a very easy way.

Just type =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)) in cell E6, and press Enter key.

What if I want to know not only the number of people but also a list of no duplicate names? That’s easy too.

Just enter =INDEX($A$1:$A$20,MATCH(,COUNTIF(E$1:E1,A$2:A$20),)+1)&”” in cell E2,remember to press Ctrl, Shift and Enter keys together. Then use the fill handle. ( The small black square in the lower-right corner of the selection.)

There are a lot of ways to get data that doesn’t duplicate.  It’s just that everything we’ve done today has to do with the COUNTIF function.  Did you learn?

Categories: Excel Tips
Sandra: