COUNTIFS
TheCOUNTIFS
function 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
COUNTIFS
formula?  Examples of using
COUNTIFS
formula COUNTIFS
formula not working? Similar formulas to
COUNTIFS
Usage
Use the 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 incriteria_range1
must meet in order to be counted.  criteria_range2, criterion2, ... (optional):
Additional ranges and criteria that can be used to further filter and count cells.
Examples
Here are a few example use cases that explain how to use theCOUNTIFS
formula 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 *
and ?
in the criteria, COUNTIFS
can be used to count cells that match a pattern or partial string.
Common Mistakes
COUNTIFS
not working? Here are some common mistakes people make when using the COUNTIFS
Google 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 nonadjacent criteria ranges
COUNTIFS only allows for adjacent criteria ranges. If you try to use nonadjacent 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.
Related Formulas
The following functions are similar to COUNTIFS
or are often used with it in a formula:

SUMIFS
The
SUMIFS
formula 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. 
AVERAGEIFS
The
AVERAGEIFS
formula 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). 
COUNT
The
COUNT
formula 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. 
IF
The
IF
formula 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.
Learn More
You can learn more about the COUNTIFS
Google Sheets function on Google Support.