ExcelBanter - Elementary SUMPRODUCT Question

"Happens" wrote:
I have used this formula to count unique names in a list
(from another Banter thread from 2010):
=SUMPRODUCT((B1:B20<"")/COUNTIF(B1:B20,B1:B10&""))
I do not know how this works, especially the / operator.
Would someone be willing to explain how this formula works?

The "/" operator should be the easiest part to understand: it is simply
"divide by". For example, 1/3 is 1 divided by 3.

The formula above is incorrect as written. If you "have used" that formula
(without error), presumably that is not the formula that appears in the
Formula Bar.

For future reference, it is "good practice" to copy-and-paste from the
Formula Bar into your postings, especially if you are asking a syntax
question.

The correct formula is:

=SUMPRODUCT((B1:B10<"")/COUNTIF(B1:B20,B1:B10&""))
or
=SUMPRODUCT((B1:B20<"")/COUNTIF(B1:B20,B1:B20&""))

Presumably the latter, based on your purpose (count unique names).

The formula returns the sum of 1/(number of matches) for each non-blank
value in B1:B20. In effect, it is:

=(B1<"")/COUNTIF($B$1:$B$20,B1&"")
+ (B2<"")/COUNTIF($B$1:$B$20,B2&"")
+ ... + (B19<"")/COUNTIF($B$1:$B$20,B19&"")
+ (B20<"")/COUNTIF($B$1:$B$20,B20&"")

For example, suppose: "foo" is in B1, B5 and B10; "bar" is in B3, B7, B8,
B12 and B13; and the other 12 cells are empty.

The formula computes the following (for B1 through B20):
1/3 + 0/12 + 1/5 + 0/12 + 1/3 + 0/12 + 1/5 + 1/5 + 0/12 + 1/3 + 0/12
+ 1/5 + 1/5 + 0/12 + ... + 0/12

where 0/12+...+0/12 represents the last 7 counts.

The numerator is 1 for each non-blank value in B1:B20 because each of
B1:B20<"" returns TRUE (1).

The numerator is 0 for each empty cell (or cell with a null-string value) in
B1:B20 because each of B1:20="" returns FALSE (0).

The COUNTIF parameter B1:B20&"" (i.e. appending a null string) avoids a
#DIV/0 error (division by zero) for each B1:B20 that is empty (or whose
value is the null-string). COUNTIF does not treat an empty cell in the
first parameter range (B1:B20) to be the same as an empty cell referenced in
the second parameter. It is an anomaly of COUNTIF.

In the sum above, note that there are 3 of 1/3 and 5 of 1/5. So we expect
3*(1/3) = 1 and 5*(1/5) = 1. Thus, we expect to count 1 for each unique
value in B1:B20.

However, n*(1/n) is not always exactly 1 in Excel due to anomalies of the
native computer arithmetic (64-bit binary floating-point).

Therefore, it would be more reliable to write:

=ROUND(SUMPRODUCT((B1:B20<"")/COUNTIF(B1:B20,B1:B20&"")),0)