How to Use the COUNTIFS Google Sheets Function

The COUNTIFS in Google Sheets helps you count cells based on multiple criteria. This differs from COUNT, COUNTA, and COUNTIF. Below, I’ll explain the COUNTIFS formula. I’ll also provide a step-by-step walkthrough on how to use it.

Understanding the COUNTIFS Formula

Whether you use Excel or Google Sheets, you’ll need to understand the syntax of the COUNTIFS formula. And as usual, I like to break down the structure of a formula to really understand how it works. 

Here’s the formula for COUNTIFS in most spreadsheet software.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3])

So let’s see what that means in Google Sheets and Excel.

The COUNTIFS Google Sheets Syntax

Here’s how it looks for the COUNTIFS function in Google Sheets.

Let’s discuss what it means. The syntax for the COUNTIFS Function in Google Sheets is as follows:

= COUNTIFS (criteria_range1, condition1,[ criteria_range2, condition2,criteria_range3, criteria3. . .]) 

So let’s talk about what each part means.

• criteria_range1 is the range of cells that you want to count based on condition1.
• condition1 is the condition that should be satisfied for a cell of criteria_range1 to be included in the count.
• criteria_range2, and condition2 are the additional ranges and criteria that you want to check. This set of parameters is repeatable.

Note: The number of rows and columns in your additional criteria_range parameters should be equal to those in their corresponding condition parameters.

The COUNTIFS function scans each cell in the given criteria_range1 and counts just the cells that match all the given conditions. In the end, it returns the total number of cells that it counted. I have a video that shows exactly how to implement this.

The COUNTIFS Syntax in Excel

The formula for COUNTIFS doesn’t change between Microsoft Excel and Google Sheets. It’s always the following:

= COUNTIFS (criteria_range1, condition1,[ criteria_range2, condition2,criteria_range3, criteria3. . .]) 

That means you’ll need to choose your criteria range, then your criteria for each argument you want to set as a condition. COUNTIFS remains one of the most commonly-used Excel functions. Note that my examples below use Google Sheets, but you can apply the same solutions to a worksheet in Excel.

How to Use the COUNTIFS Function

To demonstrate how to use the COUNTIFS function, let us take some formula examples in Google Sheets based on our sample dataset.

Want to follow along with my spreadsheet? You can make a copy of my example sheet here.

Using COUNTIFS in Google Sheets with Specific Types of Criteria

Below, I’ll walk you through how to use specific types of criteria. I’ll include numbers, text, logical operators, and dates. Remember, I’ve already covered some of these topics in much more detail. For example, I have a full guide on how to count non-blank cells in a spreadsheet. My goal below is to show how to include (or exclude) specific types of criteria in your search critera.

Adding Numeric Criterion and Text Criterion

When you have numeric values as your criteria, you can directly put them in the Google Sheets COUNTIFS formula or use a cell reference in their place. On the other hand, if we had a text or text string criterion, then we would need to enclose the text in quotation marks.

Let’s say in our example sheet, we wanted to count the Items worth 109.28 dollars that were sold Online. We would use the formula:

=COUNTIFS(E2:E22,109.28,C2:C22,"Online")

In this formula, the price condition, which is in numerical form, is keyed in directly, but the text is put in quotation marks (note that these are double quotes, not single quotes). The result is 2, which is the number of items sold online for the price of 109.28.

Using COUNTIFS with Logical Operators

We can also use the COUNTIF Google Sheets function with logical expressions. In this case, we need to use quotation marks even when dealing with numerical figures.

Logical operators are used to compare values. They include:

• > greater than
• < less than
• = equal to
• >= greater than or equal to
• <= less than or equal to
• <> not equal to

In our example sheet, we can count the number of items sold offline that are worth more than $200. To do this, we will use the formula:

=COUNTIFS(E2:E22,”>200″,C2:C22,”Offline”)

In this COUNTIFS example Google Sheets, the result is 5, which is the number of items sold Offline worth more than $200. When using logical operators, we can’t use cell references since they need to be in quotation marks, and Google Sheets will take them literally rather than as a cell reference. This will return an ERROR!

Using COUNTIFS with Date Criterion

Let’s say we want to find out how many baby food items were sold online before 1/1/2015. Before we go ahead and write the formula, it’s essential to understand what we require here.

This is more complex than just using a formula to count dates. In this example, we want to count the items where:

• Item type = “Baby Food”
• Sales Channel = “Online”, and
• Order date “<1/1/2015”

As we can see, we have three different conditions that need to be satisfied for an item to be considered when counting. So our COUNTIFS formula in Google Sheets would be:

=COUNTIFS(B2:B22,"Baby Food",C2:C22,"Online",D2:D22,"<1/1/2015")

Here’s the result we get:

Let us take another example. Say we want to find out how many office supply items worth more than $100 were sold offline.

Again, let’s break down our requirements. We want to count the items where:

• Item Type = “Office Supplies”
• Unit Price “>100”, and
• Sales Channel = “Offline”

So our COUNTIFS formula would be:

=COUNTIFS(B2:B22,"Office Supplies",E2:E22,">100",C2:C22,"Offline")

Here’s the result we get:

Using COUNTIFS to Count Cells in the Same Column

Now let us look at a special case where we want to count cells with different conditions but from the same column. For example, say you want to count the number of Baby food and Personal care items sold. Notice that both conditions fall under the ‘Item Type’ category. This means we have to specify both conditions from the same column.

Intuitively it might seem like the following formula should work:

=COUNTIFS(B2:B22,"Baby Food",B2:B22,"Personal Care")

But you will notice from the screenshot below that this does not return the correct result:

This is because the above formula tries to look for cells where both conditions are met. In other words, it acts like an AND function. Since there are no cells in the range B2:B22 that have both values “Baby Food” and “Personal Care” simultaneously, the above formula returns a 0.

The solution to this problem is to use a combination of the ARRAYFORMULA, SUM, and COUNTIFS functions, as follows:

=ARRAYFORMULA(SUM(COUNTIFS(B2:B22,{"Baby Food","Personal Care"})))

Remember to include all your criteria within curly braces.

The above formula will now separately count cells containing values “Baby Food” and “Personal Care” and then sum them up to give you the following result:

Using COUNTIFS with Wildcard Characters

Wildcard characters are very useful In creating more complex or specific criteria in your Google Sheet COUNTIFS formula. You can use them to count cells with specific texts or values in them.  The wildcards in Google Sheets include:

1. Asterisk (*) – Stands for any number of symbols or characters
2. Question mark(?)- Stands for one syllable or character
3. Tide (~)- Used to show that the asterisk or question mark is not a wildcard.

For example, if we wanted to count the number of items with the word food in them that were sold online, we would use the formula

=COUNTIFS(B2:B22,"*Food", C2:C22,"Online")

The Asterik is in front of the word food because, in the data, the word food comes last.

So what if you wanted to find a character like an actual question mark? Normally, that would find any single character as a wildcard. The workaround is simple. Just drop a tilde in front of the character, and your criteria will look for that specific character.

Using COUNTIFS Across Multiple Sheets in Google Sheets

It is possible to use the Google spreadsheet COUNTIFS function across multiple sheets in Google Sheets. All you need is the sheet’s tab name.

For example, we can add another sheet to our example spreadsheet.

If we want to find the number of items worth less than $600 sold Online, we can use the following formula:

=COUNTIFS(Sheet1!E2:E22,"<600",Sheet1!C2:C22,"Online")

We don’t really need to put in the sheet name and range manually, though. You can just select the needed columns directly from the other sheet.

In this case, we want to count the criteria in columns C and E.

1. Type in the equal sign and the COUNTIFS formula

2. Go to the previous sheet and select the first column. Add a coma

3. Type in the first criteria, then add a comma. In our case, it’s “<600”

4. Go back to the sheet and select the second column.

5. Type in the second criterion and close the brackets. In our case, the criteria is “Online” Once you are done, you can click enter or any other cell, and the results will appear.

You will get the correct results for the items sold Online worth less than 600 in your second sheet.

What Does the COUNTIFS Function Do in Google Sheets?

The COUNTIFS function in Google Sheets lets you count the number of cells in a given range based on multiple criteria in multiple columns. The function counts a cell in the given range if it meets all the criteria specified. Note that this differs from counting characters.

Many functions in Google Sheets serve the purpose of counting. For example, the COUNT function helps you count cells in a range containing a specific type of data, while the COUNTA function helps you count the number of cells in a range containing all types of data.

However, when you want to count based on multiple criteria, you have one option, the COUNTIFS function. In this tutorial, we will provide you with an easy guideline on using COUNTIFS Google Sheets function with the help of some simple examples.

COUNTIF vs COUNTIFS

Here’s a video that explains how to use COUNTIFS and how it differs from the COUNTIF formula.

Important Things to Note

There are some things you need to keep in mind when using the COUNTIFS function in Google Sheets.

• The ranges for each criterion must be the same size.
• To count empty cells, use a blank space inside quotation marks.
• When using logic operators, always use quotation marks.
• You can use cell references as your criteria.
• When using wildcards, spaces also count as characters
• The Excel countif formula is the same as the one in Google Sheets

Note that, while it’s super common to use contiguous ranges, you can also use non-contiguous ranges. Just remember that you need to use the same size range for each criterion.

Conclusion

In this guide, we showed you how to use COUNTIFS in Google Sheets using several examples. While the COUNTIF function allows you to count cells in a single range and based on a single condition, the COUNTIFS Google Sheets  function lets you count cells in a single range based on a combination of conditions. We hope our examples and step-by-step guideline has helped you understand these two functions in-depth.

References

1. Microsoft Support. COUNTIFS function [Internet]. Microsoft; [cited 2024 Jan 26]. Available from: https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
2. Google Support. Use Google Sheets: A beginner’s guide [Internet]. Google; [cited 2024 Jan 26]. Available from: https://support.google.com/docs/answer/3256550