I have data of
employees in columns A, B, C, D and E with the headers Name,
Designation, Basic Salary, Perks, Total Package. I know I can count all
the managers in column B by using the countif function like '=countif(A2:A10,"manager")' but I wish to count all
the managers with a specific basic salary in one shot. I wish to count
all the employees who are managers and
get a basic salary of 9700, for example.
How can this be done?
You can solve this problem by using the countifs function or the
sumproduct function. Depending on how your data is arranged the
formulas could look like this as in our case:
1. =COUNTIFS(B2:B9,"Manager",C2:C9,"9700")
2. =SUMPRODUCT((B2:B9="Manager")*(C2:C9=9700)) - Remember to press
'ctrl+shift+enter' keys simultaneously to execute the array function
correctly after entering the function!
Watch the video below (about 12MB) to see the function in action: