We help IT Professionals succeed at work.

Count using 2 variables

jag4
jag4 asked
on
I have 3 columns -
A: employee name
B: employee boss name
C: employee type (contractor, employee, part-time)

I would like to find the number of employee's under each boss who are contractor, for example. How?
Comment
Watch Question

The quick and dirty way would be to get a list of distinct boss names and do a CountIf for each one.

Leon
The easiest way is to do a pivot table if you know how to do it
Commented:
Since you said you only need to count the number of contractors under a boss, you can easily do this with a sumproduct formula. See attached.

Count-Using-Two-Variables.xlsx
If you  are using Excel 2007 or 2010 the easiest way is to use COUNTIFS function. It let's you count by more conditions at once. Works like a charm and is very easy to use. Try it...

Commented:
The formula you need is:

=SUMPRODUCT((B$1:B$13="Boss1")*(C$1:C$13="contractor"))
Changing the boss name and contractor to whatever you need. Change the cell number to the end too.

But i agree a pivot table in the long run would be better is changes are going to be made

Author

Commented:
Thanks for this answer - easiest to follow compared to the others and it allowed me to see what I needed to do.