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:
=COUNTIFS(criterion1_range, criterion1, criterion2_range, criterion2,...)
where
criterion1_range, criterion2_range =
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:
Comparison criteria
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.
Parsing error
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:
=countifs(ArrayFormula(EXACT(criterion1_range,criterion1)),TRUE,criterion2_range,criterion2,...)
where
criterion1_range, criterion2_range, etc =
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:
If you want partial matches such as our example using wildcards, use REGEXMATCH function:
=countif(ArrayFormula(REGEXMATCH(criterion1_range,criterion1),TRUE,criterion2_range,criterion2,...)
where
criterion1_range, criterion2_range, etc =
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:
Related Functions:
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
COUNT IF Not Blank: Count cells that are not empty.
IFS: Allows you to combine many if functions in one statement
IFERROR: Replaces formula error messages with specified text or a blank cell.
IF THEN: Evaluates data against a condition and take a corresponding action when the result is TRUE.
IF AND: Combining the IF and AND functions allows you to include multiple conditions that must be met to return a TRUE result.
IF ELSE: Evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF: Learn how to use multiple if statements in a single formula
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
Recommended articles
More resources
