Strange formula - now sure how it works

[musicgold]

Hi,

The following formula counts the unique values in a coulumn. The formula works fine but I am not sure how it works.
I understand how the SUMPRODUCT and COUNTIF functions work. However, I don't understand two things in the following formula.

1. Why is there a division sign (/) in the formula?
2. What is the meaning of the criteria part of the COUNTIF function?


=SUMPRODUCT((E22:E27<>"") / COUNTIF(E22:E27,E22:E27&""))

Thanks,

MG.


Cross posted at http://www.mrexcel.com/forum/showthread.php?t=523476

[khushii]

Hi,

Criteria part of Countif means the values in Each cell of the range.
Hence the Countif returns count of each cell values

condition (E22:E27<>"") returrns True for nonblank cells.( True = 1)


i.e True(1) is divided by count of each cell values
and then sum of all which evaluates to count of unique values in the range.

Hope it will be useful to undertand the formula.
khushii

[mancubus]

you may find an answer:

http://www.mrexcel.com/forum/showthread.php?t=70835


ps: cross replied....