Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Unique values with criteria
Hello
I have customer numbers in column A and in column B I have numbers like for example "200408", "200409", "200410" meaning August 2004, September 2004 and October 2004. Now I want to count all items in column B for a certain month if the customer number in column A is unique. Number of rows in column A (and B) will be vary. Any help? I have searched Google and found a discussion primarily between Stephen Dunn and Harlan Grove back in the summer 2003, but quite honestly I think their discussion level in this case is far beyond my reach. http://groups.google.com/groups?hl=d...gbl%26rnum%3D2 Hans Knudsen |
#2
|
|||
|
|||
Hi Hans
try the following array formula (entered with CTRL+SHIFT+ENTER): =COUNT(1/FREQUENCY(IF((B1:B20=200408)*(A1:A20<""),MATCH(A1 :A20,A1:A20,0)),ROW(INDIRECT("1:" &COUNTA(A1:A20))))) "Hans Knudsen" wrote: Hello I have customer numbers in column A and in column B I have numbers like for example "200408", "200409", "200410" meaning August 2004, September 2004 and October 2004. Now I want to count all items in column B for a certain month if the customer number in column A is unique. Number of rows in column A (and B) will be vary. Any help? I have searched Google and found a discussion primarily between Stephen Dunn and Harlan Grove back in the summer 2003, but quite honestly I think their discussion level in this case is far beyond my reach. http://groups.google.com/groups?hl=d...gbl%26rnum%3D2 Hans Knudsen |
#3
|
|||
|
|||
Hi Frank,
You should always use ROWS(A1:A20) instead of COUNTA(A1:A20) because the result of MATCH() can produce a number higher than COUNTA(). Therefore the FREQUENCY() will distribute ALL those values in the SAME bucket (the last one). Ex with A1:A5 range: blank 34 blank 34 blank 34 1 200408 2 200408 Returns 1 but should return 2. Regards, Daniel M. |
#4
|
|||
|
|||
Thank you very much!
Hans "Frank Kabel" skrev i en meddelelse ... Hi Hans try the following array formula (entered with CTRL+SHIFT+ENTER): =COUNT(1/FREQUENCY(IF((B1:B20=200408)*(A1:A20<""),MATCH(A1 :A20,A1:A20,0)),ROW(INDIRECT("1:" &COUNTA(A1:A20))))) "Hans Knudsen" wrote: Hello I have customer numbers in column A and in column B I have numbers like for example "200408", "200409", "200410" meaning August 2004, September 2004 and October 2004. Now I want to count all items in column B for a certain month if the customer number in column A is unique. Number of rows in column A (and B) will be vary. Any help? I have searched Google and found a discussion primarily between Stephen Dunn and Harlan Grove back in the summer 2003, but quite honestly I think their discussion level in this case is far beyond my reach. http://groups.google.com/groups?hl=d...gbl%26rnum%3D2 Hans Knudsen |
#5
|
|||
|
|||
Hi Daniel
thanks for the hint. You're right, my original formula works only if there're no blank rows in between. -- Regards Frank Kabel Frankfurt, Germany "Daniel.M" schrieb im Newsbeitrag ... Hi Frank, You should always use ROWS(A1:A20) instead of COUNTA(A1:A20) because the result of MATCH() can produce a number higher than COUNTA(). Therefore the FREQUENCY() will distribute ALL those values in the SAME bucket (the last one). Ex with A1:A5 range: blank 34 blank 34 blank 34 1 200408 2 200408 Returns 1 but should return 2. Regards, Daniel M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Unique Values in Column | Excel Discussion (Misc queries) | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
How do I get unique values from 2 columns? | Excel Discussion (Misc queries) | |||
Need formula to check values of data in several cells as criteria | Excel Worksheet Functions | |||
Can I count values in column 1 if criteria in column 2 are met | Excel Worksheet Functions |