In addition to the basic IF function, Excel can also count the number of cells containing certain parameters with the COUNTIF function.
Building off the example used in the IF function linked to above, you can count how many people got certain scores on their performance evaluation, or how many got raises. You can use parameters of numbers, text or dates.
Click on the fx button to get the function wizard, change the category to All, and select COUNTIF.
For our first example, let’s count how many people got a performance score of 8. The range is C2:C11, and the criteria is 8. This will give a result of 2. If you want to count people who got a score of 8 or higher, you can change the criteria to >=8, which will give a result of 4.
You can also use text as a criteria. In this case, we can count how many people got a raise. Change the range to D2:D11 and the criteria to “Raise” (the text needs to have quotation marks around it). Whatever text you use is not case sensitive, so “raise”, “Raise”, and “RAISE” would give the same result.
You can also use a wildcard (*) at the beginning or end of the text, if you want to count things that start or end with specific words or letters. Using the criteria “Raise*” will count employees who got a raise, plus those who got a raise and a bonus.
There are a couple options to count cells that are not blank. The criteria “*” will count anything with text. Cells with numbers or text will be ignored and treated as blank. The criteria “<>”&” “ will count everything – text, numbers, and dates.
To count cells that are blank, the criteria “<>”&”*” will count cells not containing any text, and “ “ will count cells not containing any data – text, numbers, or dates.
If you use a cell reference, e.g. F7, as a criteria, COUNTIF will look for whatever value is in that cell, and you can change it to get different results, e.g. if you wanted to count employees who got a score of 7, then count those with a score of 8, then those with a score of 9, etc.
When using a number or date as a criteria, you can use the specific number/date, as well as:
- Less than: <
- Greater than: >
- Not equal to: <>
- Less than or equal to: <=
- Greater than or equal to: >=
- Any of these, in conjunction with arithmetic, e.g. greater than or equal to a date plus 5 days.