The Google Sheets COUNTIFS function is used for counting rows that fulfill two or more criteria. Read this guide to learn how to use this to further power your Google Sheets dashboards!
Google Sheets COUNTIFS Syntax
The COUNTIFS function in Google Sheets is almost the same as its COUNTIF function. It is a combination of the COUNT and IFS functions in Google Sheets. The only difference is that you can add multiple ranges and criteria. Here’s the function syntax:
the reference to the set of cells where you want to apply criterion1, criterion2, etc. respectively
criterion1, criterion2 =
the criterion to apply across the criterion1_range, criterion2_range, etc. respectively. It can accommodate the following criteria:
An exact number
A string, enclosed in double quotes
Comparison expressions such as greater than or less than
The criterion can also be stored in another cell in Google sheets, and the reference placed instead. This allows the user to change the criterion anytime they need to without having to edit COUNTIFS function.
How to use COUNTIFS in Google Sheets
The COUNTIFS function in Google Sheets is best used if you want to count rows or instances that fulfill two or more criteria. Here are the steps:
Step 1: Click on the cell where you want to add it
Step 2: Type =COUNTIFS(
Step 3: Specify the range where to apply the first criterion
Step 4: Specify the first criterion
Step 5: Do Steps 3 to 4 again for the next criteria
Step 6: Close the function by adding the closing parenthesis
You can also apply multiple criteria to the same column.
The first criterion will be applied first, followed by the next criterion, and so on. You can imagine the COUNTIFS function working as follows:
=COUNTIFS(Where to apply first criterion, first criterion, where to apply second criterion, second criterion,…)
COUNTIFS Function Examples
This section contains four examples of the function in Google Sheets.
String criterion then number criterion
We can combine two different types of criterion in a single function. For this example, we count the number of times a product was ordered with a number of items in it. The product name is a string while the number of items is a number. Check the Google Sheets screenshot below:
Criteria stored in dropdown box and another cell
Storing the criteria in other cells also works just like in COUNTIF function. In this example, the product name is selected from a dropdown box while the number of items is stored in another cell. Check the Google Sheets screenshot below:
You can also combine comparison criteria in the COUNTIFS function. For this example, we find orders that contain more than 10 pieces and have a total profit of more than $30.00. Comparison criteria with their matching comparison symbols can be stored in external cells as is. Check the Google Sheets screenshot below:
Count number of values that fall within a range
You can also apply two or more criteria to the same range, provided that their results overlap. For example, you can count the number of orders that have the number of items between 5 and 15: Check the Google Sheets screenshot below:
In some other cases, this may not work. Check the next section for the solution.
Common COUNTIFS Formula Errors
There are four common errors when implementing this function in Google Sheets. Check how to solve them below.
A parsing error can occur in the formula. Check the Google Sheets screenshot below:
It all boils down to the form of double quotation marks used. To solve this error, you should switch off any automatic punctuation converters that may be present in the device you are using, such as iOS’s Smart Punctuation feature. Afterwards, edit the quotation marks to the format acceptable to Google Sheets.
Counts entries that do not have the exact case per letter
COUNTIFS is case-insensitive. Check the Google Sheets screenshot below:
The exact string “egg” only appears thrice but COUNTIFS counted 8 instances with an amount more than 30! It counted forms of the string “egg” that have different cases for each letter together with the original “egg” string.
How can we make COUNTIFS case-sensitive?
We can combine it with ARRAYFORMULA and EXACT or ARRAYFORMULA and REGEXMATCH.
If you want exact string match (with no allowance for partial matches), use EXACT function:
the reference to the set of cells where you want to count
criterion1, criterion2, etc =
the value to find and count across the data range
Check the Google Sheets screenshot below:
Missing first criterion
If your first criterion is missing, you will get zero as the result. Check the Google Sheets screenshot below:
This is because COUNTIFS works as a cascade: it progressively reduces the count as you apply the successive criterion. You have to carefully check the formula since it doesn’t throw an error code for the missing first criterion.
The solution is to simply add the first criterion to the formula.
Applying two different criteria to the same range gives zero result
When you try to apply two different criteria to the same range, you may get a zero result. Check the Google Sheets screenshot below:
This happens when the results do not have an overlap; that is, they do not share a common set of cells that fulfill both criteria. Clearly, “5-lb flour” is different from “6-egg set”. For these, you can use COUNTIF functions in the same formula, adding their results. Check the Google Sheets screenshot below:
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
SUMIFS: Sums data from cells that meet multiple criteria
COUNTIF: count data if it fulfils certain criteria