COUNTIF:

The COUNTIF function in Microsoft Excel counts the number of cells within a range that meet a single criterion that you specify. For example, let’s create a table of Employees and their work hours. We are interested in finding the number of employees who work more than eight hours, this can be easily done by COUNTIF function in Excel.

In the above example we have used COUNTIF formula in Microsoft Excel (=COUNTIF($B$2:$B$10,”> 8″)) to find how many employees worked more than 8 hours. Column B2:B10 is the range and the criteria is “> 8” hours. Notice that the criteria is in double quotes. Similarly, we found the number of employees who worked less than 8 hours by using the formula =COUNTIF(B2:B10,”<8″)  (The criteria is < 8 hours).

COUNTIFS:

The COUNTIFS function in Microsoft Excel counts the number of cells in multiple ranges and with multiple criterions that you specify. For example, let’s create a table of Employees with their Department and their work hours. We are interested in finding the number of employees who work more than 8 hours in Human Resource Department. This can be easily done by COUNTIFS function.

In the above example we have used COUNTIFS formula in Microsoft Excel (=COUNTIFS(B2:B10, “Human Resource”,C2:C10,”>8″) ) to find how many employees in Human Resource Department worked more than 8 hours. Column B2:B10 is the range and the criteria is “Human Resource”. Second Range is C2:C10 and the criteria is “>8”. Notice that the criteria is in double quotes.  We see that there is only one employee from Human Resource Department who worked more than 8 hours, which is correct (as per the table). Here the criteria is case insensitive.