Counting Unique Occurences in a Range

Originally Posted by ShredDude

I found the following formula in another post that provides one with the ability to count the unique occurences of a text string in a range.

=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))

I also found the following modification to account for the situation where some cells in the range may be blank:

=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

Both of these work well for me, but I don't understand WHY they work. I feel that I have a good understanding of using SUMPRODUCT with multiple condition tests using the * operator for example, but I'm not understanding why the formulas above give the correct answers.

Is there someone out there that could explain in simple terms what these formulas are doing so as to help me understand the WHY in this case? It would be most apreciated.

Thanks in advance.

Shred

Hi,

http://www.xldynamic.com/source/xld.SUMPRODUCT.html is the best place to have the Sumproduct explained, but for your question, if an item occurs two times, then 1 is divided by two, two times, and two halves added to give one.

If the product occurs 3 times then 1 is divided by 3, 3 times, and the 3 x .33 added to give one.

hth
---