Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count of unique items meeting condition
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Count of Items with certain criteria | Excel Discussion (Misc queries) |