Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Elementary SUMPRODUCT Question
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? Thanks a lot |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
|
|||
|
|||
Thanks so much -- what a great resource! Bob
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Elementary SUMPRODUCT Question
Errata (typo).... I wrote:
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). Correction: because each of B1:B20<"" returns FALSE (0). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function using autofill and auto select - elementary arithmetic | Excel Discussion (Misc queries) | |||
Elementary(?) GetChartElement Question | Charts and Charting in Excel | |||
Elementary question | Excel Discussion (Misc queries) | |||
Im sure this question is elementary... | Excel Worksheet Functions | |||
SumProduct question | Excel Discussion (Misc queries) |