Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can somebody explain to me how Bob Phillips' unique record counter works?
While browsing the forum for useful tips I spotted this formula posted
by Bob Phillips On Sept 21. It counts the number of unique records in a list (A2:A20 in the example): =SUMPRODUCT((A2:A20<"")/COUNTIF(A2:A20,A2:A20&"")) I found this formula very interesting and I just had to try it out. It works, but I can't for the life of me understand how it works. I've read the SUMPRODUCT Help file but that hasn't helped me. COUNTIF Help file didn't help either. I can't see how A2:A20&"" works as the Criterion for the COUNTIF part of the formula. Can anybody help me understand Bob's incredible formula? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can somebody explain to me how Bob Phillips' unique record counter works?
Ken,
Spookily, Richard Buttrey asked the same question this morning. Here is my reply re-posted Let's start by defining the range A1:A20 to talk specifics. Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max or data in just A1:A10 The basic formula to count unique items is =SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10)) The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}. As can be seen, each occurrence of the repeated value is counted, so there are four occurrences of Bob in the array. There will always be the same number of occurrences of value as the count of that value, unless two or more items are repeated the same number of times, in which case it will be some multiple of that count. Thus the item that is repeated 4 times has 4 instances of that count, dividing 1 by the count of 4, gives 0.25 4 times. The full array of values is {0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0 ..5;0.5;1}. The item that repeats 4 times sums to 1. The item that repeats 3 times also sums to 1. It should be clear from this that every value works in the same way and sums to 1. In other words, 1 is returned for every unique item. The sum of these values becomes the count of unique items. As our test range is A1:A20, and some of the items in A1:A20 are blank, extending this formula to A1:A20 would return a #DIV/0! Error. The reason for the error is blank cells in the full range A1:A20. Each blank cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0! Error when divided into 1. The solution to this is to force it to count the empty cells as well, and not return a zero. Adding &"" to the end of the COUNTIF formula forces a count of the blanks. This addition on its own removes the #DIV/0! error, but will cause the blanks to be counted as a unique item. A further addition to the formula resolves this by testing for those blanks. Instead of dividing the array of counts into 1 each time, adding the test creates an array of TRUE/FALSE values to be divided by the equivalent element in the counts array. Each blank will resolve to FALSE in the dividend array, and the count of the blanks in the divisor array. The result of this will be 0, so the blanks do not get counted. -- HTH Bob Phillips "Ken Johnson" wrote in message oups.com... While browsing the forum for useful tips I spotted this formula posted by Bob Phillips On Sept 21. It counts the number of unique records in a list (A2:A20 in the example): =SUMPRODUCT((A2:A20<"")/COUNTIF(A2:A20,A2:A20&"")) I found this formula very interesting and I just had to try it out. It works, but I can't for the life of me understand how it works. I've read the SUMPRODUCT Help file but that hasn't helped me. COUNTIF Help file didn't help either. I can't see how A2:A20&"" works as the Criterion for the COUNTIF part of the formula. Can anybody help me understand Bob's incredible formula? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can somebody explain to me how Bob Phillips' unique record counter works?
Bob,
Thanks for that, it's going to take me a while to work through it. It's easily the most amazing bit of excel wizardry I've ever seen. Is it your own invention? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can somebody explain to me how Bob Phillips' unique record counter works?
I wish I could lay some claim to that but I cannot. It is doubtful that
anyone can claim these things absolutely as so many contribute to their evolution. I first saw it in a more simplistic form =SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20) and it has evolved over the years to what I showed that day. There are better ones out there IMO, although that does have the appeal of brevity. I saw one from Domenic a few weeks back that sums in non-contiguous cells that I really liked =SUMPRODUCT(--ISNUMBER(MATCH(COLUMN(C1:N1)-COLUMN(C1)+1,{1,3,6,8,12},0)),--( C1:N1=1)) This sums C1,E1,H1,J1,N1 only. I had a solution to this problem, but not as elegant. Here is one of mine for extracting the numbers from a delimited string and sum them. So if A1 contains 1,12,123 it will get the 1 and 123 out and sum them =SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(IND IRECT("1:"&L EN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1)))), FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(INDIRECT(" 2:"&LEN(A1)-LEN(SUBSTITUTE (A1,B1,""))+2))))- FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(INDIRECT(" 1:"&LEN(A1)-LEN(SUBSTITUTE (A1,B1,""))+1))))-1)) If you think that all this is useful, take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH Bob Phillips "Ken Johnson" wrote in message ups.com... Bob, Thanks for that, it's going to take me a while to work through it. It's easily the most amazing bit of excel wizardry I've ever seen. Is it your own invention? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can somebody explain to me how Bob Phillips' unique record counter works?
Bob,
thanks for that Bob! I'm just starting a two week break from teaching (High school science) and you've given me plenty to think about and keep me off the streets. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Record retrieval. | Excel Discussion (Misc queries) | |||
Copy Unique Record | Excel Discussion (Misc queries) | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Display unique record | Excel Discussion (Misc queries) | |||
How can I update an MS Access table record from within Execl works | Excel Discussion (Misc queries) |