Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique record count with multiple criteria
I've read many questions and answers similar to my problem but I can't find
one that works. I need to count the number of unique records based on 2 or more criteria (roughly 1000 rows), for example: Jones CompA Employee Project12 Jones CompA Employee Project44 Smith CompB Employee Project21 Smith CompB Employee Project09 Smith CompB Employee Project51 Smith CompB Employee Project04 Ying CompA Contractor Project6 Ying CompA Contractor Project12 Jones CompB Employee Project76 Jones CompB Employee Project04 Jones CompB Employee Project82 King CompA Contractor Project29 How do I count the number of: a) CompA Employees? Should be 1. b) CompA Contractors? Should be 2. c) Employees? Should be 4. d) CompA? Should be 3. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique record count with multiple criteria
To count the unique CompA Employee...
F2 = CompA G2 = Employee Assuming there are no empty cells within the range of data. Array entered** : =COUNT(1/FREQUENCY(IF((B2:B13=F2)*(C2:C13=G2),MATCH(A2:A13, A2:A13,0)),ROW(A2:A13)-MIN(ROW(A2:A13)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. You can use the same basic syntax for each of your conditions. -- Biff Microsoft Excel MVP "Rick" wrote in message ... I've read many questions and answers similar to my problem but I can't find one that works. I need to count the number of unique records based on 2 or more criteria (roughly 1000 rows), for example: Jones CompA Employee Project12 Jones CompA Employee Project44 Smith CompB Employee Project21 Smith CompB Employee Project09 Smith CompB Employee Project51 Smith CompB Employee Project04 Ying CompA Contractor Project6 Ying CompA Contractor Project12 Jones CompB Employee Project76 Jones CompB Employee Project04 Jones CompB Employee Project82 King CompA Contractor Project29 How do I count the number of: a) CompA Employees? Should be 1. b) CompA Contractors? Should be 2. c) Employees? Should be 4. d) CompA? Should be 3. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique with criteria condition | Excel Worksheet Functions | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Count unique values based on multiple criteria | Excel Discussion (Misc queries) | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
how to count unique values in excel based on multiple criteria | Excel Worksheet Functions |