COUNTIFS

• 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:

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

1. criteria_range1 (required):
   The range of cells that will be evaluated against the first criterion.
2. criterion1 (required):
   The condition or criteria that each cell in criteria_range1 must meet in order to be counted.
3. criteria_range2, criterion2, ... (optional):
   Additional ranges and criteria that can be used to further filter and count cells.

Examples

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

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.

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.