Count of unique items meeting condition

[Tim C]

Excel 2003 on Windows XP.

Sheet1
A1:A10, named Category, contains "A", "B" or "C".
B1:B10, named Code, contains a code number as text. Items are not unique.

Sample data:

A 1-1
A 1-1
A 1-2
A 1-2
B 1-1
B 1-2
B 1-3
C 1-1
C 1-1
C 1-1
C 1-1

Sheet2
Column A contains the possible categories.
In column B, I need a formula that will give me a count of unique codes for
a given category.

Sample desired result:

A 2
B 3
C 1

Unique I can do. Unique with conditions, I can't figure out. Any help is
greatly appreciated.

Thanks,
Tim C

 

[Daniel.M]

Hi Tim,

By using a match(). you always generating the same 'number' (the first match)
for any condition satisfying the criteria you supplied. By using frequency, you
distribute the matches into buckets (from 1 to .Rows.Count).
You then have as many 'unique' occurences as there are different match results
different than 0.

Assuming your letter in E1 ("A", "B" or "C"), this array (Ctrl-Shift-Enter)
formula:

=SUM(--(FREQUENCY(IF(Category=E1,MATCH(Codes,Codes,0)),ROW(INDIRECT("1:"&ROWS(Co
des))))>0))

or this one (same idea, but here the 0 will produce #DIV0! errors that won't be
COUNTed. It's slightly shorter but not faster)

=COUNT(1/FREQUENCY(IF(Category=E1,MATCH(Codes,Codes,0)),ROW(INDIRECT("1:"&ROWS(C
odes)))))

Regards,

Daniel M.