Home |
Search |
Today's Posts |
#1
|
|||
|
|||
counting unique items
I've seen formulas like this posted as a solution for counting unique items
in a column: =SUMPRODUCT((A1:A20000<"")/COUNTIF(A1:A20000,A1:A20000&"")) What purpose does the &"" at the end serve? -- tj |
#2
|
|||
|
|||
It seems to stop blanks in the range from causing the formula to return an
error message, but I'm not sure I see why. -- tj "tjtjjtjt" wrote: I've seen formulas like this posted as a solution for counting unique items in a column: =SUMPRODUCT((A1:A20000<"")/COUNTIF(A1:A20000,A1:A20000&"")) What purpose does the &"" at the end serve? -- tj |
#3
|
|||
|
|||
Hi!
That's exactly what it does. If a cell is blank, then: A1:A20000<"" will return FALSE in that cells position in the array. Then: COUNTIF(A1:A20000,A1:A20000) will return 0 for that empty cell. Then: FALSE/0 will return #DIV/0! So, the empty text string is used so that: A1:A20000<"" will return TRUE, then: TRUE/0 will return 0. To see how this formula works, try this in a small test range: Put some random entries in A1:A5. In B1 enter this formula and copy down to B5: =A1<"" In C1 enter this formula and copy down to C5: =COUNTIF(A$1:A$5,A1&"") In D1 enter this formula and copy down to D5: =B1/C1 And finally, enter this formula in E1: =SUM(D1:D5) Try experimenting by putting dupes in A1:A5. Then try removing the empty string from the Countif formulas and delete some of the entries in A1:A5. See what happens! Biff "tjtjjtjt" wrote in message ... It seems to stop blanks in the range from causing the formula to return an error message, but I'm not sure I see why. -- tj "tjtjjtjt" wrote: I've seen formulas like this posted as a solution for counting unique items in a column: =SUMPRODUCT((A1:A20000<"")/COUNTIF(A1:A20000,A1:A20000&"")) What purpose does the &"" at the end serve? -- tj |
#4
|
|||
|
|||
Ooops!
I goofed in my explanation: So, the empty text string is used so that: A1:A20000<"" will return TRUE, then: TRUE/0 will return 0. Should be: So, the empty text string is used so that when: A1:A20000<"" returns FALSE The Countif will count the empty string so that: FALSE/1 will return 0 Biff "Biff" wrote in message ... Hi! That's exactly what it does. If a cell is blank, then: A1:A20000<"" will return FALSE in that cells position in the array. Then: COUNTIF(A1:A20000,A1:A20000) will return 0 for that empty cell. Then: FALSE/0 will return #DIV/0! So, the empty text string is used so that: A1:A20000<"" will return TRUE, then: TRUE/0 will return 0. To see how this formula works, try this in a small test range: Put some random entries in A1:A5. In B1 enter this formula and copy down to B5: =A1<"" In C1 enter this formula and copy down to C5: =COUNTIF(A$1:A$5,A1&"") In D1 enter this formula and copy down to D5: =B1/C1 And finally, enter this formula in E1: =SUM(D1:D5) Try experimenting by putting dupes in A1:A5. Then try removing the empty string from the Countif formulas and delete some of the entries in A1:A5. See what happens! Biff "tjtjjtjt" wrote in message ... It seems to stop blanks in the range from causing the formula to return an error message, but I'm not sure I see why. -- tj "tjtjjtjt" wrote: I've seen formulas like this posted as a solution for counting unique items in a column: =SUMPRODUCT((A1:A20000<"")/COUNTIF(A1:A20000,A1:A20000&"")) What purpose does the &"" at the end serve? -- tj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique text entries in a sheet with a condition | Excel Worksheet Functions | |||
Counting Unique Cells When Spread Sheet is Filtered | Excel Worksheet Functions | |||
Counting Unique Records with multiple conditions | Excel Worksheet Functions | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
Count of unique items meeting condition | Excel Worksheet Functions |