Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values with multiple criteria
I've read other questions and answers on this subject but haven't found
anything that works for my situation. I need to count unique records based on mulitple criteria, for example: Jones CompA Employee Project1 Jones CompA Employee Project2 Jones CompA Employee Project3 Smith CompA Contractor Project4 Smith CompA Contractor Project2 Ying CompB Employee Project6 Ying CompB Employee Project7 Ying CompB Employee Project8 Ying CompB Employee Project9 Baum CompA Employee Project2 Baum CompA Employee Project10 Elvis CompA Contractor Project4 How do I find the number of... a) CompA Employees? Should be 2. b) CompA Contractors? Should be 2. c) CompB Employees? Should be 1. d) Employees? Should be 3. e) Contractors? Should be 2. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values 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 other questions and answers on this subject but haven't found anything that works for my situation. I need to count unique records based on mulitple criteria, for example: Jones CompA Employee Project1 Jones CompA Employee Project2 Jones CompA Employee Project3 Smith CompA Contractor Project4 Smith CompA Contractor Project2 Ying CompB Employee Project6 Ying CompB Employee Project7 Ying CompB Employee Project8 Ying CompB Employee Project9 Baum CompA Employee Project2 Baum CompA Employee Project10 Elvis CompA Contractor Project4 How do I find the number of... a) CompA Employees? Should be 2. b) CompA Contractors? Should be 2. c) CompB Employees? Should be 1. d) Employees? Should be 3. e) Contractors? Should be 2. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values with multiple criteria
I don't come up with the numbers you are indicating for your data, for
example Employees CompA. There are 5 unique records, the are no repeats, just look at the Project # column?? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rick" wrote: I've read other questions and answers on this subject but haven't found anything that works for my situation. I need to count unique records based on mulitple criteria, for example: Jones CompA Employee Project1 Jones CompA Employee Project2 Jones CompA Employee Project3 Smith CompA Contractor Project4 Smith CompA Contractor Project2 Ying CompB Employee Project6 Ying CompB Employee Project7 Ying CompB Employee Project8 Ying CompB Employee Project9 Baum CompA Employee Project2 Baum CompA Employee Project10 Elvis CompA Contractor Project4 How do I find the number of... a) CompA Employees? Should be 2. b) CompA Contractors? Should be 2. c) CompB Employees? Should be 1. d) Employees? Should be 3. e) Contractors? Should be 2. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values with multiple criteria
Excel 2007
Four more ways. With and without formulas: http://www.mediafire.com/file/zmnqjekyzu1/02_12_09.xlsm |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values with multiple criteria
Hi,
You can simply pivot the data as well. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Rick" wrote in message ... I've read other questions and answers on this subject but haven't found anything that works for my situation. I need to count unique records based on mulitple criteria, for example: Jones CompA Employee Project1 Jones CompA Employee Project2 Jones CompA Employee Project3 Smith CompA Contractor Project4 Smith CompA Contractor Project2 Ying CompB Employee Project6 Ying CompB Employee Project7 Ying CompB Employee Project8 Ying CompB Employee Project9 Baum CompA Employee Project2 Baum CompA Employee Project10 Elvis CompA Contractor Project4 How do I find the number of... a) CompA Employees? Should be 2. b) CompA Contractors? Should be 2. c) CompB Employees? Should be 1. d) Employees? Should be 3. e) Contractors? Should be 2. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
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 | |||
Unique values with criteria | Excel Worksheet Functions |