COUNTIFSfunction counts the number of cells in a range that meet multiple criteria. It is commonly used to analyze data sets and filter out specific information based on selected criteria.
- How to use
- Examples of using
COUNTIFSformula not working?
- Similar formulas to
COUNTIFS formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- criteria_range1 (required):
The range of cells that will be evaluated against the first criterion.
- criterion1 (required):
The condition or criteria that each cell in
criteria_range1must meet in order to be counted.
- criteria_range2, criterion2, ... (optional):
Additional ranges and criteria that can be used to further filter and count cells.
ExamplesHere are a few example use cases that explain how to use the
COUNTIFSformula in Google Sheets.
Counting cells based on a single criterion
By specifying a single range and criterion,
COUNTIFS can be used to count the number of cells that meet a specific condition.
Counting cells based on multiple criteria
By specifying multiple ranges and criteria,
COUNTIFS can be used to count the number of cells that meet several conditions at once.
Counting cells based on wildcard characters
By using wildcard characters like
? in the criteria,
COUNTIFS can be used to count cells that match a pattern or partial string.
COUNTIFSnot working? Here are some common mistakes people make when using the
COUNTIFSGoogle Sheets Formula:
Incorrect number of arguments
One of the most common mistakes with COUNTIFS is providing an incorrect number of arguments. This can happen if you forget to include a criteria range or its corresponding criterion. Make sure that each criteria range is followed by its corresponding criterion, and that you have included all necessary criteria ranges and criteria.
Using incorrect operators in criteria
Another common mistake with COUNTIFS is using incorrect operators in the criteria. For example, using '=' instead of '<' or '>' will not produce the desired results. Make sure you are using the correct operators for your criteria.
Not using quotation marks for text criteria
If you are using text criteria in COUNTIFS, make sure to enclose the criteria in quotation marks. If you forget to do this, Sheets will treat the criteria as a cell reference, which will not produce the desired results.
Using non-adjacent criteria ranges
COUNTIFS only allows for adjacent criteria ranges. If you try to use non-adjacent criteria ranges, Sheets will return an error. To avoid this mistake, make sure that all criteria ranges are adjacent to each other.
Not using absolute references for criteria ranges
If you copy a COUNTIFS formula to other cells, make sure to use absolute references for the criteria ranges. If you do not use absolute references, Sheets will adjust the ranges for each cell, which will not produce the desired results.
The following functions are similar to
COUNTIFS or are often used with it in a formula:
SUMIFSformula is a function in Google Sheets that adds the values in a specified range based on multiple criteria. It is most commonly used to sum data that meets specific criteria, such as summing sales for a particular month by region or summing expenses for a specific category and date range.
AVERAGEIFSformula calculates the average of a range of cells that meet multiple criteria. It is most commonly used to find the average of a range of values that meet specific conditions. The formula takes one required argument (average_range) and any number of optional argument pairs (criteria_range, criterion).
COUNTformula returns the number of numeric values within a specified range of cells. It is commonly used to count the number of entries in a given range that meet specific criteria, such as counting the number of sales above a certain threshold.
IFformula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.
You can learn more about the
COUNTIFS Google Sheets function on Google Support.