COUNTIFS Google Sheets: The Ultimate Guide

Are you tired of manually counting and filtering data in Google Sheets? Look no further! In this guide, we will dive deep into the COUNTIFS function in Google Sheets, which allows you to count rows or instances that meet multiple criteria. Whether you’re a beginner or an expert, this guide will equip you with the knowledge you need to level up your data analysis skills.

Syntax of COUNTIFS in Google Sheets

The COUNTIFS function in Google Sheets is similar to the COUNTIF function, but with one key difference – it allows you to add multiple ranges and criteria. Here’s the syntax of the function:

=COUNTIFS(range_criteria1, criteria1, range_criteria2, criteria2, ...)

• range_criteria1, range_criteria2 refers to the range of cells where you want to apply the criteria1, criteria2, etc. respectively.
• criteria1, criteria2 is the criteria you want to apply to the range_criteria1, range_criteria2, etc. It can include the following types of criteria:
  • Exact numbers
  • Strings (enclosed in quotation marks)
  • Comparison expressions such as greater than or less than

You can also store the criteria in other cells of the Google Sheets and use the reference in place. This allows you to easily modify the criteria without having to edit the COUNTIFS function every time.

How to use COUNTIFS in Google Sheets

Using the COUNTIFS function in Google Sheets is simple and straightforward. Follow these steps to get started:

1. Click on the cell where you want to add the function.
2. Type =COUNTIFS( to start the function.
3. Specify the range where you want to apply the first criterion.
4. Specify the first criterion.
5. Repeat steps 3 and 4 for additional criteria.
6. Close the function by adding the closing parenthesis.

You can also apply multiple criteria to the same column. The function will apply the first criterion first, followed by the next criterion, and so on. You can imagine the COUNTIFS function working like this:

=COUNTIFS(Where to apply the first criterion, first criterion, Where to apply the second criterion, second criterion, ...)

Examples of the COUNTIFS function

Let’s explore some practical examples of how the COUNTIFS function can be used in Google Sheets.

Example: String criterion followed by a number criterion

In this example, we want to count the number of times a product has been ordered with a certain quantity. The product name is a string, while the quantity is a number. Take a look at the screenshot below:

Example: Criteria stored in a dropdown list and another cell

Just like the COUNTIF function, you can store the criteria in other cells. In this example, the product name is selected from a dropdown list, while the quantity is stored in another cell. Check out the Google Sheets screenshot below:

Example: Comparison criteria

You can also combine comparison criteria in the COUNTIFS function. In this example, we are looking for orders that have more than 10 items and a total profit greater than $30.00. The comparison criteria with their corresponding comparison symbols can be stored in external cells as is. Take a look at the Google Sheets screenshot below:

Example: Counting the number of values within a range

You can also apply two or more criteria to the same range, as long as their results overlap. For example, you can count the number of orders that have a quantity between 5 and 15. Check out the Google Sheets screenshot below:

In some cases, this may not work. Read on for the solution.

Common errors with the COUNTIFS formula

Like any formula, there are some common errors that can occur when using the COUNTIFS function in Google Sheets. Let’s explore them and how to resolve them.

Error: Parsing error

A parsing error can occur in the formula. It all boils down to the format of the quotation marks used. To resolve this error, you need to disable any automatic punctuation converter that may be present on your device, such as the smart punctuation feature on iOS. Then, modify the quotation marks to be acceptable for Google Sheets.

Error: Counts entries that don’t have exact case match

COUNTIFS is case-insensitive. The exact string “egg” appears only three times, but COUNTIFS counted 8 instances with an amount greater than 30! It counted variations of the string “egg” that have different cases for each letter with the original “egg” string.

To make COUNTIFS case-sensitive, you can combine it with ARRAYFORMULA and EXACT or ARRAYFORMULA and REGEXMATCH.

• If you want an exact string match (without allowing partial matches), use the EXACT function:

=countifs(ArrayFormula(EXACT(range_criteria1,criteria1)),TRUE,range_criteria2,criteria2,...)

• If you want partial matches like in our example with wildcard characters, use the REGEXMATCH function:

=countif(ArrayFormula(REGEXMATCH(range_criteria1,criteria1),TRUE,range_criteria2,criteria2,...)

Error: Missing first criterion

If your first criterion is missing, you will get a zero as the result. This is due to the cascading nature of COUNTIFS – it progressively reduces the count as you apply subsequent criteria. Double-check the formula as it doesn’t generate an error code for the missing first criterion. The solution is simple – just add the first criterion to the formula.

Error: Applying two different criteria to the same range results in zero

When trying to apply two different criteria to the same range, you may end up with a zero result. This happens when the results don’t have an overlap – that is, they don’t share a common set of cells that meet both criteria. Clearly, “5-pound flour” is different from “6-pack of eggs”. For this, you can use COUNTIF functions in the same formula, adding their results.

Related functions

To further enhance your data analysis skills in Google Sheets, here are some related functions you may find useful:

• IF: Used to check specific conditions in a dataset
• SUMIF: Adds numbers if they meet a certain condition
• SUMIFS: Adds data from cells that meet multiple criteria
• COUNTIF: Counts data if it meets certain criteria
• COUNT IF Not Blank: Counts non-empty cells
• IFS: Combines multiple IF functions into a single statement
• IFERROR: Replaces formula error messages with specified text or an empty cell
• IF THEN: Evaluates data against a condition and takes a corresponding action when the result is TRUE
• IF AND: By combining the IF and AND functions, you can 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 another when it 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: Calculates an average for numbers in a data range if they meet specified criteria

Use our invoice due date tracker to easily track due dates from your spreadsheet in just a few clicks.

If you’re interested in learning how to send an email from Google Sheets based on the value of a cell, we also recommend checking out our comprehensive guide.

Now that you’re armed with the knowledge of the COUNTIFS function, you’re ready to conquer your data analysis tasks in Google Sheets. Happy counting!