COUNTIF & COUNTIFS in Google Sheets: Formula & Examples

You may need to count cells in Google Sheets for many reasons. However, the COUNT function is somewhat limited, as you are usually interested in counting cells that meet specific criteria. While it‘s possible to write nested functions with COUNT and IF, there’s no need for that. Google Sheets has built-in functions to save you the trouble: COUNTIF and COUNTIFS. The first allows you to specify a range of cells and the criterion that cells must meet to be counted. COUNTIFS, however, allows you to specify multiple ranges and multiple criteria.

In this guide, you will learn about the COUNTIF and COUNTIFS functions in Google Sheets. After reviewing the syntax for both functions, you will learn how to use them in different circumstances. You have a variety of examples with step-by-step instructions using different types of conditions, such as counting non-blank cells, cells containing partial or specific text, cells with values greater than or equal to a particular value, and cells with values within a range. Additionally, you will learn how to use the COUNTIFS function to count values based on multiple criteria.

COUNTIF & COUNTIFS Functions Syntax in Google Sheets

Let’s review the syntax for COUNTIF and COUNTIFS before using them.

COUNTIF Function Syntax

Google Sheets’ COUNTIF function has two required parameters.

=COUNTIF(range, criterion)

• range: the range of cells that you want to test using the criterion.
• criterion: the test or condition that you want to apply to the range.

COUNTIFS Function Syntax

Google Sheets’ COUNTIFS function has two required parameters, just like COUNTIF. However, COUNTIFS allows you to provide additional parameters to test multiple ranges using different criteria.

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

The first range and the first criterion are required arguments.

• criteria_range1: the first range of cells that you want to test using the first criterion.
• criterion1: the test or condition that you want to apply to the first range.

Additional ranges and criteria are optional, but you can add as many as you want. However, the dimensions of subsequent ranges must be equal to those of the first range - all ranges should have the same number of rows and columns.

• criteria_range2*: the second range of cells that you want to test using the second criterion.
• criterion2*: the test or condition you want to apply to the second range.

How to Build the COUNTIF Criteria or Conditions?

There are many useful operators you can use to define the criteria or conditions in the COUNTIF and COUNTIFS functions. For example, you can use the following criteria to search for non-blank and blank cells, respectively.

=COUNTIF(range, “<>”)
=COUNTIF(range, “”)

You can also use the Boolean operators TRUE and FALSE to count a cell based on the results of a formula or to count checked or unchecked checkboxes.

Numeric Values

When dealing with exact numeric values, you don’t need to use quotes. However, if you're using comparison operators, you’ll need the quotes to enclose the condition or criterion.

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

Text Values

You also have a lot of options in terms of text values. In addition to matching exact text by using quotation marks, you can also use any of the following wildcards for partial matches.

• * Matches 0 or more characters.
• ? Matches a single character
• ~ If you need to search for the symbols “*” or “?”, add the tilde before the symbol to escape it, so it's not used as a wildcard.

Examples of How to Use the COUNTIF Function in Google Sheets

Now that you have reviewed the syntax for the COUNTIF function, let's see some examples of how you can use it in different situations.

Imagine you have a table with sales data, which includes the name of the sales agent, their team, the product, and the sales amount. You need to get different counts for your sales report, but counting manually would be a nightmare, as the table has five hundred rows.

Google Sheets COUNTIF Cell Contains Text

Follow the steps below to count cells that contain specific text. In this case, let's count the number of sales of ‘productA’.

1. 1. In an empty cell, type in the COUNTIF formula.

1. 2. Select the column with the product information as the ‘range’.

1. 3. For the criterion, use “productA”.

1. 4. That’s it. The number of transactions related to this product is 112.

Second, let's find the number of sales made by ‘Southteam’. Instead of using an exact match, you can use wildcards to search for part of the text.

1. 1. In an empty cell, type in the function.

1. 2. For the range, select the column with the team names.

1. 3. For the criterion, the function should match cells containing ‘south’, followed by any number of characters.

1. 4. That’s it. Southteam made a total of 76 sales.

Google Sheets COUNTIF Greater Than

Imagine you want to count the number of sales with a value greater than $600.

1. 1. Type the COUNTIF formula in an empty cell.

1. 2. For the range, select the column with the sales amounts.

1. 3. The criterion should check that values are greater than $600.

1. 4. That’s it. There were 380 sales greater than $600.

Google Sheets COUNTIF Not Blank

Let’s say you’ve noticed some blank rows in your dataset, and you want to count the number of sales. To count all non-blank cells in a range, follow the steps below.

1. 1. In an empty cell, type in the COUNTIF formula.

1. 2. Select the range of cells you want to check.

1. 3. For the criterion, use “<>”, with the quotation marks.

1. 4. That's it. All non-blank cells have been counted.

Examples of How to Use the COUNTIFS Function with Multiple Conditions in Google Sheets

In Google Sheets, you can use the COUNTIFS function to count cells based on multiple criteria, and each criterion can be applied to a different range. For example, you can count the sales of a specific product with values greater than a particular amount and values within a specific range.

Google Sheets COUNTIFS with Different Criteria for Different Ranges

Let’s find the number of sales of productA for an amount greater than $500.

1. 1. In an empty cell, type in the COUNTIFS formula.

1. 2. Select the first range you want to evaluate. In this case, the column containing the products.

1. 3. Add the criterion you want to apply to the first range. In this case, values should match ”productA”.

1. 4. Next, select the second range you want to evaluate. For this example, it's the column with the sales amounts.

1. 5. The second criterion should check for values greater than $500.

1. 6. That’s it. There were 91 sales of ‘productA” greater than $500.

Google Sheets COUNTIFS Between Two Numbers

You can also use COUNTIFS to count the cells with values within a specified range by applying different criteria to the same range. For example, sales amounts above $600 but below $1000.

1. 1. Type the COUNTIF formula in an empty cell.

1. 2. Select the column with the sales amounts as the first range.

1. 3. The first criterion is that the value has to be greater than $600.

1. 4. For the second range, select the column with the sales amounts.

1. 5. The second criterion is that the values be under $1000.

1. 6. That’s it. There were 103 sales between $600 and $1000.

Conclusion

COUNTIF and COUNTIFS are very useful functions. You can count cells based on their contents by specifying a single criterion or multiple criteria. The criteria can be as simple or complex as you want and use logical operators, Boolean values, and wildcards.

You now know how to use COUNTIF and COUNTIFS to count cells containing numeric and Boolean values, as well as text. You also know how to write criteria to find numeric values greater than a specific number, numeric values within a range of values, exact matches for numeric and text values, partial matches for text using wildcards, and non-blank cells.

To learn more about the related function SUMIF, check out our guide on SUMIF & SUMIFS in Google Sheets: Formula & Examples.