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 