The COUNTIF spreadsheet function is very useful for counting the values within a given range that match a given criterion.
Take a look at the spreadsheet in the first figure. It’s a list of scores from an online quiz, each result marked out of 100.
We want to know how many people took the online quiz, and how many passed a specific threshold score.
First we’ll count the responses using COUNTA, as described in this post:
This will count the number of cells populated in column A (starting from A2) and therefore the number of respondents. Now we want to count the number of those who exceeded our chosen ‘pass’ score, which for this example we’ll make 45.
The COUNTIF function has the syntax
Here the range parameter is the sheet range containing the responses, and is A2:A (as for COUNTA in the adjacent cell). The criterion is that the score be greater than or equal to 45 (note the quote marks):
In this case, seven responders have made the grade:
If the cells you’re examining contain text, you can use a string as a criterion, and the match will be positive if the text in the cell contains the string passed to COUNTIF. You can also use the wildcards ?, *, and ~ as explained in this post.
NOTE: COUNTIF can only count relative to a single criterion. If you need to use more than one, there are other options, which I’ll cover in later posts.