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)),RO W(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.
"Tim C" wrote in message
...
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
|