Counting Duplicates in a list...how does this formula work???

[imaguy77]

This formula will count duplicates in a list. If you have a list that says:

eggs
eggs
beef
beef
beef
chicken

the formula will return:

2
2
3
3
3
1

because thats the number of instances of each unique item.

here's the formula:

=COUNTIF($A$1:$A$10,$A$1:$A$10&"")

but i can't figure out HOW this works. can someone explain it please? Thanks!!!

 

[Art15651]

You really don't need the second part of the COUNTIF to be a range. Just using A1 will work as it will change as you copy it down. It works, because at the end of the statement you are comparing, for example, A1 to A1&"" which is just A1 with a null value appended. Apparently Excel calls this a different value than just plain A1 so a circular reference is avoided. Clever actually...nice formula.

So, formula could be as follows and still work:
=COUNTIF($A$1:$A$10,A1&"")

 

[barry houdini]

The difference between

=COUNTIF(A$1:A$10,A1&"")

and

=COUNTIF(A$1:A$10,A1)

is the way that blank cells are treated. The former will actually count the blank cells, the latter will return a zero against each blank cell, no matter how many there are