Google Sheets COUNTIF and COUNTIFS Functions

Learn how to use the COUNTIF and COUNTIFS functions in Google Sheets. 1. The COUNTIF Function
2. How and when to use COUNTIF
3. The COUNTIFS Function
4. How and when to use COUNTIFS 6 Minutes

The COUNT function of Google Sheets allows you to count the number of cells with numerical values in a selected data range. The COUNTA function counts all the values, regardless of type except blank cells. However, these two functions will not work if your goal is to count the number of cells that match specific criteria. For this, we can use the  COUNTIF and COUNTIFS functions. In this tutorial, we will learn how and when to use these functions.

The COUNTIF Function

The COUNTIF function is a combination of the COUNT and IF functions. It is used to count the number of cells that meet a criterion.

The syntax is

=COUNTIF(range, criterion)

where

• Range is the set of cells where you want to count, and
• Criterion is a value to find and count across the data range.

Basically, this syntax says =COUNTIF(Where do you want to count?, What do you want to count?)

How and when to use COUNTIF

In this section, we will learn possible scenarios for using COUNTIF.

Counting numerical and textual values with exact match

You may want to count only the number of cells that contain a certain number or text.

For example, if we want to count the number of cells which have a value of 5 in cells C2:C11, we type =COUNTIF(C2:C11, 5) on a blank cell.

Suppose we want to count the number of cells which contain Cheeseburger in cells B2:B11, we simply type =COUNTIF(B2:B11, “Cheeseburger”) on another cell. Don’t forget that you need to enclose the text criterion in quotation marks to specify that you’re looking for textual values. Otherwise, you will have a wrong count value.

The COUNTIF function for textual values is also not case sensitive. Looking at the data above, you may notice that cell B9 contains a lowercase “c”, but it is still included when COUNTIF is performed.

In case your criterion is already written in one cell, you can simply call that cell when using COUNTIF. In our example below, the criterion Cheeseburger is already written on cell E4. Instead of writing “Cheeseburger” in our COUNTIF function, you may simply write E4, i.e.,  =COUNTIF(B2:B11, C3).

Counting textual values with partial match

You may wonder if it is possible to count cells that contain a specific word or characters. Don’t worry because COUNTIF wildcards can help. There are two wildcards that you can use: question mark (?) and asterisk (*).

A question mark (?) matches any single character, including space. For example, we have this syntax:

=COUNTIF(B2:B11,"?????????Pizza")

This criterion "?????????Pizza" allows the Google Sheets to count the cells that contain 14 characters ending in Pizza.

You can also place the question mark (?) at any position on your character criterion. Just make sure that you have the right count of the characters. However, counting the length of the characters is so tedious! What if we only want to find the count of the cells that start with, end with, or contain specific characters? In that case, the wildcard asterisk (*) is the best option!

An asterisk matches any sequence of characters. It allows you to disguise any number of characters that you want to ignore. For example,

• “*cheese” - means that you’re looking for cell textual values that end with “cheese”
• “cheese*” - means that you’re looking for cell textual values that start with “cheese”
• :*cheese*” - means that you’re looking for cell textual values that contain the word “cheese”, regardless of its position.

If you need to count the number of textual values that contain an asterisk (*) and/or a question mark (?), then use tilde sign (~) before those characters. For example, if we want to look for the values that contain "?", the formula will be =COUNTIF(A2:A11,"*~?*").In this case, COUNTIF will treat “?” as simple characters instead of searching characters.

Counting numerical values with a logical expression criterion

You can also include logical expressions in your criterion value. Here, we use some mathematical operators which are summarized below.

Note: Do not forget to include your criterion inside quotation marks.

The COUNTIFS Function

The COUNTIFS function is almost the same as the COUNTIF function. It is a combination of COUNT and IFS. The only difference is that you can add multiple ranges and criteria in COUNTIFS. Here’s the syntax:

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

where

• Criteria_range1 is the range to check against Criterion 1
• Criterion1 is the pattern or test to apply to Criteria_range1
• Criteria_range2 is the additional range to check
• Criterion2 is the additional pattern or test to apply.

Use the same pattern to add as many range and criterion pairs as you want.

How and when to use COUNTIFS

COUNTIFS is normally used to count values in two ranges that should meet some criteria or values that fall between a specific range of numbers. It also works like the AND function in Google Sheets

It is normally used when there are values in two ranges that should meet some criteria or whenever you need to get the number falling between a specific range of numbers.For example, we want to count the number of cells that is greater than 3 and less than 8, we write

=COUNTIFS(A2:A12, ">3", A2:A12, "<8").

Oops! Something went wrong while submitting the form.

November 29, 2021

Google Sheets COUNTIF and COUNTIFS Functions The COUNT function of Google Sheets allows you to count the number of cells with numerical values in a selected data range. The COUNTA function counts all the values, regardless of type except blank cells. However, these two functions will not work if your goal is to count the number of cells that match specific criteria. For this, we can use the  COUNTIF and COUNTIFS functions. In this tutorial, we will learn how and when to use these functions.

The COUNTIF Function

The COUNTIF function is a combination of the COUNT and IF functions. It is used to count the number of cells that meet a criterion.

The syntax is

=COUNTIF(range, criterion)

where

• Range is the set of cells where you want to count, and
• Criterion is a value to find and count across the data range.

Basically, this syntax says =COUNTIF(Where do you want to count?, What do you want to count?)

How and when to use COUNTIF

In this section, we will learn possible scenarios for using COUNTIF.

Counting numerical and textual values with exact match

You may want to count only the number of cells that contain a certain number or text.

For example, if we want to count the number of cells which have a value of 5 in cells C2:C11, we type =COUNTIF(C2:C11, 5) on a blank cell.

Suppose we want to count the number of cells which contain Cheeseburger in cells B2:B11, we simply type =COUNTIF(B2:B11, “Cheeseburger”) on another cell. Don’t forget that you need to enclose the text criterion in quotation marks to specify that you’re looking for textual values. Otherwise, you will have a wrong count value.

The COUNTIF function for textual values is also not case sensitive. Looking at the data above, you may notice that cell B9 contains a lowercase “c”, but it is still included when COUNTIF is performed.

In case your criterion is already written in one cell, you can simply call that cell when using COUNTIF. In our example below, the criterion Cheeseburger is already written on cell E4. Instead of writing “Cheeseburger” in our COUNTIF function, you may simply write E4, i.e.,  =COUNTIF(B2:B11, C3).

Counting textual values with partial match

You may wonder if it is possible to count cells that contain a specific word or characters. Don’t worry because COUNTIF wildcards can help. There are two wildcards that you can use: question mark (?) and asterisk (*).

A question mark (?) matches any single character, including space. For example, we have this syntax:

=COUNTIF(B2:B11,"?????????Pizza")

This criterion "?????????Pizza" allows the Google Sheets to count the cells that contain 14 characters ending in Pizza.

You can also place the question mark (?) at any position on your character criterion. Just make sure that you have the right count of the characters. However, counting the length of the characters is so tedious! What if we only want to find the count of the cells that start with, end with, or contain specific characters? In that case, the wildcard asterisk (*) is the best option!

An asterisk matches any sequence of characters. It allows you to disguise any number of characters that you want to ignore. For example,

• “*cheese” - means that you’re looking for cell textual values that end with “cheese”
• “cheese*” - means that you’re looking for cell textual values that start with “cheese”
• :*cheese*” - means that you’re looking for cell textual values that contain the word “cheese”, regardless of its position.

If you need to count the number of textual values that contain an asterisk (*) and/or a question mark (?), then use tilde sign (~) before those characters. For example, if we want to look for the values that contain "?", the formula will be =COUNTIF(A2:A11,"*~?*").In this case, COUNTIF will treat “?” as simple characters instead of searching characters.

Counting numerical values with a logical expression criterion

You can also include logical expressions in your criterion value. Here, we use some mathematical operators which are summarized below.

Note: Do not forget to include your criterion inside quotation marks.

The COUNTIFS Function

The COUNTIFS function is almost the same as the COUNTIF function. It is a combination of COUNT and IFS. The only difference is that you can add multiple ranges and criteria in COUNTIFS. Here’s the syntax:

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

where

• Criteria_range1 is the range to check against Criterion 1
• Criterion1 is the pattern or test to apply to Criteria_range1
• Criteria_range2 is the additional range to check
• Criterion2 is the additional pattern or test to apply.

Use the same pattern to add as many range and criterion pairs as you want.

How and when to use COUNTIFS

COUNTIFS is normally used to count values in two ranges that should meet some criteria or values that fall between a specific range of numbers. It also works like the AND function in Google Sheets

It is normally used when there are values in two ranges that should meet some criteria or whenever you need to get the number falling between a specific range of numbers.For example, we want to count the number of cells that is greater than 3 and less than 8, we write

=COUNTIFS(A2:A12, ">3", A2:A12, "<8").