Unique values with criteria

[Hans Knudsen]

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=...O0sD5OVDHA.2068%40TK2MSFTNGP11.phx.gbl&rnum=2

Hans Knudsen

 

[Guest]

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)))))

 

[Daniel.M]

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.

 

[Hans Knudsen]

Thank you very much!
Hans

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)))))

 

[Frank Kabel]

Hi Daniel
thanks for the hint. You're right, my original formula works only if
there're no blank rows in between.