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

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

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.

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

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.