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.

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.

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.

unique values with multiple criteria
 

Excel 2007
Four more ways.
With and without formulas:
http://www.mediafire.com/file/zmnqjekyzu1/02_12_09.xlsm

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.