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.

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.