Using COUNTIF in Google Sheets

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.

Click image to enlarge

Click image to enlarge

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:

Click image to enlarge

Click image to enlarge

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

=countif(range, criterion)

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):

=countif(A2:A, ">=45")

In this case, seven responders have made the grade:

Click image to enlarge

Click image to enlarge

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.