Easy Guide to the COUNTIFS Google Sheets Function

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.

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.

COUNTIFS Google Sheets Syntax

Syntax for the COUNTIFS Function in Google Sheets is as follows:

= COUNTIFS (criteria_range1, condition1,[ criteria_range2, condition2,…]) 

Here,

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

How to Use the COUNTIFS Function in Google Sheets

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

You can make a copy of our example sheet here.

Using COUNTIFS in Google Sheets with Number 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. 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.

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.

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.

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

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.