Easy Guide to the COUNTIF and COUNTIFS Google Sheets Functions

There are many functions in Google Sheets that 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 one or more conditions, you have two options in Google Sheets – the COUNTIF and COUNTIFS functions. In this tutorial, we will provide you with an easy guideline on using both the COUNTIF and COUNTIFS Google Sheets functions with the help of some simple examples.

What Does the COUNTIF Function Do in Google Sheets?

The COUNTIF function in Google Sheets lets you count the number of cells in a given range that match a given criterion. Note that you can only test one criterion using this function.

Syntax for the COUNTIF Function is as follows:

= COUNTIF (range, condition) 

Here,

  • range is the range of cells containing the data you want to work with (or count cells from).
  • condition is the condition that should be satisfied for a cell to be included in the count.

The COUNTIF function scans each cell in the given range and counts just the cells that match the given condition. In the end, it returns the total number of cells that it counted.

For example, consider the following list of words:

 list of words

Say you want to count how many times the word “collar” appears in the above list. In other words, you want to count all cells in the list which contain the word “collar.”

Since we want to count based on a condition, we can use the COUNTIF function as follows:

=COUNTIF(A2:A13,"collar")

Here, we specified that we want a cell from the range A2:A13 to be counted only if it contains the value “collar.”

Here’s the result we get:

 =COUNTIF(A2:A13,"collar")

How to Use the COUNTIF Function in Google Sheets

Now that you understand the syntax for the COUNTIF function, let’s see some simple examples to understand the different ways in which it can be used. We will apply all our examples to the following data set:

country-wise sales data

The above dataset contains country-wise sales data, which has been randomly generated. The data includes names of countries, types of items sold, whether the items were sold online or offline, the date of order, and the unit price of each item.

Let us see different use cases for the COUNTIF function with this dataset.

Using Google Sheets COUNTIF to Count Cells that Contain a Given Text

As mentioned before, you can use the COUNTIF function to count the number of cells in a data range that contain a specific numeric or text value.

For example, from our given sample data set, let’s say we want to find the total number of items sold online. This means we want to count the number of cells in column C that contain “Sales Channel = Online.”  For this, we can use the COUNTIF function as follows:

=COUNTIF(C2:C22,"Online")
=COUNTIF(C2:C22,"Online")

Using Google Sheets COUNTIF to Count Cells that are Not Blank

So far, we have seen how to count a cell if it contains text.

The COUNTIF function can also be used to count the number of blank and non-blank cells in your data range.

To count the number of blank cells in, say column A, you can use the following formula:

=COUNTIF(A2:A22,"")
=COUNTIF(A2:A22,"")

To count the number of non-blank cells in column A, you can use the following formula:

=COUNTIF(A2:A22,"<>")
=COUNTIF(A2:A22,"<>")

This returns the number of non-blanks cells that can have any value. However, if you want to count the number of non-blank cells that contain only text values, you can use the following formula:

=COUNTIF(A2:A22,"*")

Using COUNTIF to Count Cells based on a Date in Google Sheets

The COUNTIF function doesn’t just let you specify conditions with text values. It also lets you specify conditions involving dates, as well as numeric values.

For example, if you want to count how many orders were made on the date 8/22/2012, your formula would be:

=COUNTIF(D2:D22,"8/22/2012")

Notice we specified the date condition by enclosing it in double-quotes.

=COUNTIF(D2:D22,"8/22/2012")

Using COUNTIF to Count Cells based on a Logical Expression in Google Sheets

Now let us look at some cases where you don’t want to look for exact matches but values that are less than, greater than, equal to or not equal to a given number. Criteria like these are known as logical criteria.

Say you want to count all the orders made before the date 8/22/2012. You could then use the COUNTIF formula with a logical criterion as follows:

=COUNTIF(D2:D22,"<8/22/2012")
=COUNTIF(D2:D22,"<8/22/2012")

Similarly, you can use a logical criterion with a numerical value. For example, if you want to count all the orders that are valued at less than $100 in unit price, you can use the following formula:

=COUNTIF(E2:E22,"<100")
=COUNTIF(E2:E22,"<100")

Note: You can also combine logical criteria using AND and OR functions.             

Using COUNTIF to Count Cells Across Multiple Google Sheets

It’s quite natural to have data across multiple sheets. For example, you could have 15 transactions in Sheet1 and another 15 transactions in Sheet2. When you want to count based on a criterion, you might need to match data from a field in both sheets.

For example, say you have the following 15 transactions in Sheet1:

 15 transactions in Sheet1

And you have the rest of the transactions in Sheet2:

 the rest of the transactions in Sheet2

Now let us try to count the total number of Baby Food orders from both sheets. One way to do this would be to use a COUNTIF function to count the items from each sheet separately and then add them up together, like this:

=COUNTIF(Sheet1!B2:B16,"Baby Food")+COUNTIF(Sheet2!B2:B16,"Baby Food")
use a COUNTIF function to count the items from each sheet separately and then add them up together

However, imagine how your formula would look if you had your transactions in 10 different sheets!

So a more elegant way to write your formula would make use of an array that combines the ‘Item Type’ lists from all your sheets into one, as follows:

=COUNTIF({Sheet1!B:B;Sheet2!B:B},"Baby Food")

Notice we enclosed the lists from all sheets between curly braces (signifying an array) and separated each list with a semicolon. Using the above formula, we get the same result, as can be seen in the screenshot below:

=COUNTIF({Sheet1!B:B;Sheet2!B:B},"Baby Food")

Counting Cells from Multiple Ranges in Google Sheets

If you want to count cells based on multiple conditions from multiple columns, the COUNTIF function will not be much help. This is because the COUNTIF function allows you to use only one data range and one criterion. So if you have more than one criteria, you will have to use the COUNTIFS function instead.

In the next few sections, we will learn more about the COUNTIFS function and how it can be used.

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.

Syntax for the COUNTIFS Function 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, 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 an example based on our sample dataset.

sample dataset

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 would be:

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

Here’s the result we get:

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

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:

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

Using COUNTIFS to Count Cells from the Same Column in Google Sheets

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

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

This is because the above formula tries to look for cells where both the 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:

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

Conclusion

In this tutorial, we showed you using several examples of how to use the COUNTIF and COUNTIFS functions in Google Sheets. While the COUNTIF function allows you to count cells in a single range and based on a single condition, the COUNTIFS 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.

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.