# COUNTIFS function in Excel

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010

Home

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 to see the countifs function in action:

Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010