Google Sheets is a great asset that makes your calculations easier by utilizing functions. COUNTIF and COUNTIFS are two related functions that count cells based on criteria. These functions receive a set of data and then count the ones that meet the condition you’ve set.
COUNTIFS is essentially the successor to COUNTIF, as it can do what COUNTIF does and more. Where COUNTIF can only test a single set of data against a single condition, COUNTIFS can test multiple sets of data against multiple conditions.
Read on to find out what these functions do, and how to use them in Google Sheets.
What Are COUNTIF and COUNTIFS Functions in Google Sheets?
COUNTIF is a Google Sheets function that counts cells based on a single condition. COUNTIF returns the number of cells in a given cell range that meet a specific condition and cannot take in more than one series of data and one condition. The COUNTIF function’s syntax is as below:
=COUNTIF(range, condition)
You enter the range of cells you want the function to look through, and then enter the condition against which these cells are tested. Then, the function returns the number of cells that met the condition and passed the test.
COUNTIF is a handy function, and you can make the scope of your condition wider or tighter by using wildcards, albeit it only supports a single range and a single condition.
COUNTIFS, on the other hand, a close relative of COUNTIF, can support numerous ranges and conditions. The syntax for COUNTIFS is as below:
=COUNTIFS(range1, condition1, range2, conditon2, ...)
Range1 is tested against condition1, and range2 is tested against condition2, and so forth. One important thing about COUNTIFS is that it doesn’t simply return the sum of cells that meet each condition.
There is often one initial disambiguation regarding COUNTIFS. You might think that COUNTIFS count the cells that meet the first condition, and then the cells that meet the second condition independently and finally sums them, but that’s not what COUNTIFS does. Instead, COUNTIFS filters the values that met the first condition by testing them against the second condition, and then only if the value meets both conditions in the given ranges does COUNTIF count it.
Similar to COUNTIF, COUNTIFS supports wildcards which enable you to widen or tighten your condition’s scope. These wildcards are:
- Question mark (?): Takes the place of any single character. R?d can be Rod or Red.
- Asterisk (*): Takes the place of any number of any character. R*d can be Rod, Road, or Roasted,
- Tilde (~): Tells Google Sheets that the character immediately after is not a wildcard, and should not be treated as such. R~?d is R?d.
You can use mathematical logical operators to set conditions with COUNTIF and COUNTIFS. These operators are:
- Greater than >
- Less than <
- Equal to =
- Greater than or equal to >=
- Less than or equal to <=
With all this in mind, let’s move to the Google Sheets environment and put these functions to use.
How to Use the COUNTIF Function in Google Sheets
COUNTIF needs a set of data and a condition to work. In this example, we’ve got the age and height of some community volunteers in a spreadsheet. The goal is to count how many of them are aged 20 or more and output it in a cell.
Of course, you could count the cells yourself, but that would be a shame. Why count when you can write a formula that counts for you? Right?
- Select the cell where you want to show the results. We’re going to select cell F4 for this example.
- Go to the formula bar and enter the formula below:
=COUNTIF(B2:B7, ">=20")
This formula summons the COUNTIF function and then tells it to take in the cells from B2 to B7 (cells with the age values) and then test them against the condition “>=20”. By doing this, the function checks the values to see if they’re 20 or greater and then returns the number of cells that pass this test.
- Press Enter. COUNTIF will now return the number of volunteers who are aged 20 or more.
You can also count the cells containing a specific string. For instance, the formula below counts the cells that contain the word Red in cells A1 to A10:
=COUNTIF(A1:10, "Red")
As demonstrated above, the COUTNIF function takes on the burden of counting off your shoulder. Yet, COUNTIF can’t do the job in some scenarios. For instance, if you wanted to count the number of volunteers aged 20 and more and are also taller than 175cm, you’d have trouble counting it with COUNTIF as it only supports one set of data and one condition.
That’s where its close relative, COUNTIFS, comes in.
How to Use the COUNTIFS Function in Google Sheets
COUNTIFS is a function very similar to COUNTIF, though with one major advantage. COUNTIFS can take multiple ranges and test them against multiple conditions, returning the count of values that have both conditions in their ranges.
Conversely, COUNTIFS can also take a single range and test it against a single condition, making it practically the same as COUNTIF.
In the same spreadsheet as the previous example, let’s use COUNTIFS to count the number of volunteers who are aged 20 and above and are also taller than 175 centimeters.
- Select the cell where you want to display the results. That will be cell F5 for this example.
- In the formula bar, enter the formula below:
=COUNTIFS(B2:B7, ">=20", C2:C7, ">175")
This formula will first run the age of the volunteers (cells B2 to B7) against the “>=20” condition to see if they’re aged 20 or older. Then the volunteers, who meet the first condition, are tested against the second condition. Their height (cells C2 to C7) is tested to check if it’s above 175. Finally, the formula returns the count of volunteers who have both conditions.
- Press Enter. Google Sheets will now display the number of volunteers who are 20 and older and are also taller than 175 centimeters.
COUNTIFS grows to prove itself even more useful when you’re dealing with lots of cells. Like most other core functions in Google Sheets, COUNTIFS can achieve wonders when combined with other functions and wildcards.
Let Google Sheets Do the Counting
Counting cells is a chore you can do on your own when there aren’t that many cells, but it grows harder and harder as more cells get involved, until it becomes impossible for you to count on your own. The COUNTIF and COUNTIFS functions exist to serve in these situations, and now you know how to utilize them properly.
Given a range and a condition, or multiple ranges and conditions in COUNTIF’s case, these functions can count the cells that you want. You can combine COUNTIF and other functions with wildcards to make them even more efficient.
Read Next
About The Author
