How to Use COUNTIF & COUNTIFS in Google Sheets

Key Takeaways: How to Use COUNTIF & COUNTIFS in Google Sheets

To Use COUNTIF & COUNTIFS in Google Sheets

Suppose you have a spreadsheet with sales data, and you want to count how many times the product “Lavender Oil” was sold.
You can use COUNTIF to achieve this: =COUNTIF(ProductRange, “Lavender Oil“)

Imagine you want to count transactions that meet two conditions: they were made by a specific seller (Seller ID 3782) and have a total amount greater than $100.
You can use COUNTIFS for this: =COUNTIFS(SellerRange, 3782, AmountRange, “>100“)

In this article we will learn about two most important and commonly used Google Sheets function. First one is COUNTIF and the second one is COUNTIFS. We will also learn how to use COUNTIF and COUNTIFS in Google Sheets with real-life examples.

Table of Contents

What is COUNTIF and COUNTIFS functions in Google Sheets?

Google Sheets allows the users to use various functions and formulae for calculation and management of data. Users may utilize them as and per requirements. Users need not to focus on writing long formulae rather just use Google Sheets functions to get their desired results.

COUNTIF and COUNTIFS functions are also provided by Google Sheets to facilitate the users. As the name suggests, COUNTIF function makes up the combination of COUNT function and IF function. It means that it checks certain IF, in other words, condition to evaluate a COUNT function. The same goes for COUNTIFS, just that COUNTIFS may evaluate multiple conditions. COUNTIF function is used for counting cells with a single condition in a single range. While COUNTIFS function is used to count cells meeting multiple conditions or criteria in one or more ranges.

In this article, we will discuss how to use COUNTIF and COUNTIFS functions in Google Sheets along with their uses and applications.

Syntax of COUNTIF function in Google Sheets

Syntax of COUNTIF function is pretty basic and easy to follow, it is as:

=COUNTIF (range, condition)

Let us understand the above syntax in detail:

“=” implies the beginning of the formula in a cell.
COUNTIF is the keyword designated for the COUNTIF function.
() Opening and Closing parenthesis indicate the start and end of function respectively.
Range defines the range of cells where the condition will be checked and upon satisfaction of condition, cells will be counted. Here, only a single range can be given.
Condition defines the criterion that will be evaluated and checked for each cell in the range. Here, only a single condition can be given. For multiple conditions, use COUNTIFS function provided by Google Sheets.

The same syntax of COUNTIF function can be used to solve many real-life problems with a little modifications in Google Sheets.

Syntax of COUNTIFS function in Google Sheets

Syntax of COUNTIFS function is similar to COUNTIF function such that range and conditions are evaluated. It is as:

=COUNTIFS (range_1, condition_1, [range_2], [condition_2], …, …)

Let us understand the above syntax in detail:

“=” implies the beginning of the formula in a cell.
COUNTIFS is the keyword used for the COUNTIFS function.
() Opening and Closing parenthesis indicate the start and end of function respectively.
Range_1 defines the first range of cells where the condition__1 will be checked and upon satisfaction of the condition, cells will be counted. It is mandatory.
Condition_1 defines the criterion that will be evaluated and checked for each cell in the range_1. It is mandatory.
[] Square brackets show that these parts are optional. But always make sure that range and criterion are given in pairs if any of the two is missing while one is present, it will end up in an error.
Range_2 defines the second range of cells that will be looked up for condition_2. It is optional.
Condition_2 defines the second condition that will be checked for range_2. It is mandatory if range_2 is specified. All the ranges and conditions need to come in pairs.
You may enter any number of pair of range and conditions. For each pair, the condition will be checked against the range.

Points to remember:

All ranges need to be identical, i.e. equal in length. Otherwise, the formula will end up as a Formula Parse Error.
In order to use the COUNTIFS better, you need to keep in mind that all ranges are checked against their relevant conditions and counting is based upon all conditions satisfaction upon their relevant ranges.

The same syntax of COUNTIFS function can be used to solve many real-life problems with a little modifications.

Use Case of COUNTIF and COUNTIFS functions in Google Sheets

We may require COUNTIF and COUNTIFS functions for scenarios which involves counting for the number of cells satisfying a certain condition or set of conditions. Let us consider the following worksheet and analyze what help can COUNTIF and COUNTIFS offer for the evaluation of the information inside the worksheet:

Considering the above worksheet, we can utilize COUNTIF function in the following ways.

We may want to count how many transactions a certain product have. E.g. how many times Lavender Oil was sold in the above record? In order to do this manually, we are required to look at the product name or product number of Lavender Oil and sum up the number of times they appear.
We may want to figure out how many transactions were made in a day, month or year.
Also, the number of transactions initiated by a particular seller can also be evaluated using COUNTIF function. And same goes for a particular customer.
Suppose you want to count the number of transactions which involves quantity greater than 3 or involving total amount greater than 10$. It can also be evaluated using COUNTIF function.
We can also count the number of transactions involving keyword “Oil” in them using COUNTIF function.

Now, let us move on to some complex operations for COUNTIFS function.

We can count the number of transactions which have a particular seller and a particular customer altogether using COUNTIFS function.
We can also count the number of transactions initiated by a certain seller on a particular day, month or year. Or we may count the number of transactions in a day, month or year preceding a particular amount or by a particular customer.
In the same way, we may find out the count of transactions based on multiple conditions being applied altogether on a set of records. So for each record (row), these conditions are checked and count is made based on the condition satisfaction.

Let us demonstrate how to use COUNTIF and COUNTIFS functions under various circumstances as described above and more.

How to use COUNTIF function in Google Sheets

It has become an easy to task to count the number of cells based upon a condition or criterion such that the number of cells satisfying the particular condition is added up in count otherwise not. Let us start the demonstration starting from the very basic applications.

COUNTIF function for text condition in Google Sheets

Suppose we have the same above worksheet and we need to find the number of transactions of “Coconut Oil”. We can visually see the number of transactions for the Product Name “Coconut Oil” as below:

It is easy to notice that the count of number of transactions for “Coconut Oil” is 2. But if we have a large dataset then it becomes a very hectic task to figure out the count and accuracy level is dropped for manual work. So, Google Sheets has provided us with functions like COUNTIF to do this task for us.

We can find the count of number of transactions for “Coconut Oil” using COUNTIF function in the following way:

Step 1: Select the cell where you want to write the formula for COUNTIF function.

Here, we have selected the cell where we would like to use COUNTIF function. Selected cell(s) would appear with a blue outline as shown above.

Step 2: Initiate the formula with = sign and put the keyword COUNTIF in it as:

You can see that Google Sheets is providing suggestions for the functions. Here, we will be using simple COUNTIF function to do a conditional count against a range.

Step 3: Write down range and condition inside parenthesis () to use the function.

First of all, we have to provide range in the formula as:

We can either type the range in the formula bar or we can just select those cells while writing the formula, the range mentioned appears selected as:

Now, mention the criterion after the comma as:

Remember that text match conditions are enclosed in double quotation marks to indicate that it is text that needs to be compared. The above condition will match the exact text “Coconut Oil”.

Step 4(Optional): End the formula with closing parenthesis as:

The result of COUNTIF appears as:

Which is the required output. We can put various conditions along with wildcards in the same way to get the desired results.

COUNTIF function for number condition in Google Sheets

Using the same worksheet, let us find the number of transactions of Seller Id “3782”. We can see the number of transactions initiated by Seller Id 3782 in Seller ID column of data table as:

We can find the count of number of transactions for 3782 Seller ID using COUNTIF function in the following way:

Step 1: Select the cell where you want to write the formula for COUNTIF function.

Here, we have selected the cell where we would like to use COUNTIF function. Selected cell(s) would appear with a blue outline as shown above.

Step 2: Initiate the formula with = sign and put the keyword COUNTIF in it as:

You can see that Google Sheets also provide examples for the user’s ease as above.

Step 3: Write down range and condition inside parenthesis () to use the function.

We have provided range and criteria in the formula as:

Remember that matching text conditions are enclosed in double quotation marks to indicate that it is text that needs to be compared. Providing simple arithmetic values to be matched do not require double quotes.

The result of formula appears as:

Which is the required output. We can also use various conditions such as >, <, >=, <=, etc. Using conditional operators, we need to write the condition enclosed in double quotation marks.

Let us take another simple example for counting the number of transactions having item count greater than or equal to 3 using the formula:

Result would appear as:

COUNTIF function for counting the number of blank cells in Google Sheets

Suppose we have a worksheet which has some filled cells and some blank entries. Let us use the worksheet below which has some blank entries in Time Column:

We may want to know the number of transaction that are recorded without recording the time of entry with it. So, we can find out the number of blank cells in the range using the following steps.

Step 1: Select the cell where you want to write the formula for COUNTIF function.

Step 2: Initiate the formula with = sign and put the keyword COUNTIF in it as:

Step 3: Write down range and condition inside parenthesis () to use the function.

Since the condition is “blank cells”, then the condition is written as “” which implies blank cells in Google Sheets. So the formula appears as:

Also, M6 cell shows the number of blank cells as:

COUNTIF function for counting number of non-blank cells in Google Sheets

Let us use the same dataset to find out the number of transactions which had the Time mentioned in the records. We can do so by changing the condition such that it counts the number of non-blank cells in Google Sheets.

Step 1: Select the cell where you want to write the formula for COUNTIF function.

Step 2: Initiate the formula with = sign and put the keyword COUNTIF in it.

Step 3: Write down range and condition inside parenthesis () to use the function.

Now, the condition is to find number of non-blank or not blank cells so it is written as “<>” in Google Sheets. <> generally means not equal to, so here the cells which are not equal to an empty string are counted in COUNTIF function. Alternatively, we can write the condition as “<>”&””. Both works fine in Google Sheets. The formula becomes:

The result of formula counting the number of blank cells appear as:

COUNTIF function for specific word in Google Sheets

Let us consider the same worksheet to find the number of transactions that had the keyword “oil” in the Product Name column. We can do so using the following steps.

Step 1: Select the cell where you want to write the formula for COUNTIF function.

Step 2: Initiate the formula with = sign and put the keyword COUNTIF in it.

Step 3: Write down range and condition inside parenthesis () to use the function.

Here, the condition uses a wildcard “*” that appears before the actual keyword as “*Oil”. Formula and the result of formula are as:

Showing the count of transactions for Oils.

How to use COUNTIFS function in Google Sheets

COUNTIFS function for multiple conditions (number duration or range) in Google Sheets

Sometimes, we may need to find the number of transactions that come within a particular range or duration of numbers. For finding the range, we need to check two conditions; number is greater than or equal to minimum number and also the number is less than or equal to maximum number. As we cannot deal multiple conditions in COUNTIF function, we need to use COUNTIFS function which allows multiple ranges and multiple conditions. Consider the amount column of worksheet. Let us find out the number of transactions that come within the range (number duration) of 10$-20$ by following the following steps.

Step 1: Select the cell where you want to write the formula for COUNTIFS function.

Step 2: Initiate the formula with = sign and put the keyword COUNTIFS in it.

Step 3: Write down ranges and conditions in a pair one by one within parenthesis (); all separated by comma’s to use the COUNTIFS function.

The conditions are “>=10” and ”<=20” that are both checked on the same range of cells such that Google Sheets checks if the number is greater than or equal to 10 and also the number is less than or equal to 20 to find the count within the range of 10-20 as:

We can see that there are 6 cells such that they come within the range of 10-20$.

Multiple COUNTIF functions to find the count of cells outside a number duration or range in Google Sheets

Suppose we need to find out the number of cells outside a specific range of numbers. We need to use two COUNTIFS functions such that a function will find the number of transactions below the range and another COUNTIFS will be used to find the numbers above the range and then both COUNTIFS are added up together to find the total number of cells outside the range. Let us find the number of cells outside the number range using the following formula.

COUNTIFS function for text and date condition in Google Sheets

Let us find the number of transactions for all kinds of “Oils” on the date June “27, 2022”. It implies here we have two conditions and two respective ranges for them. We will check Oils in the Product Names column and check for the specific date in Time Column.

Step 1: Select the cell where you want to write the formula for COUNTIFS function.

Step 2: Initiate the formula with = sign and put the keyword COUNTIFS in it.

Step 3: Write down range and condition one by one separated by comma’s inside parenthesis () to use the function.

The first range and condition goes for finding the keyword “Oil” in Product Name column as:

Google Sheets is showing us the upcoming result as 7 if we submit the formula now. We will add the second range and condition after comma for finding June “27, 2022” in Time as:

It shows the required output.

COUNTIFS for simple text and number condition

COUNTIFS function for text and date condition in Google Sheets

Let us find the number of transactions for the customer “Tony” which are greater than 5$. We can do so using COUNTIFS functions. Here, 2 conditions are concerned; Customer should be “Tony” and Amount should be greater than 5$. Let us proceed in the following way.

Step 1: Select the cell where you want to write the formula for COUNTIFS function.

Step 2: Initiate the formula with = sign and put the keyword COUNTIFS in it.

Step 3: Now, it is time to write ranges and conditions.

The first range and condition goes for finding that the customer is Tony as:

We can see that there are total 3 transactions by Tony. Now, we will write the second condition for Amount to be greater than 5$ as:

It shows that there is only 1 transaction where Tony purchases more than 5$.

Important Notes

“=” sign indicates the start of formula in any cell. Functions can only be used if the cells start from =.
COUNTIF and COUNTIFS functions are case-insensitive so “Coconut Oil” or “coconut oil” in criterion is going to produce the same results.
Google Sheets is not case-sensitive in terms of its keywords, so you can use Countif, COUNTIF, countif, etc. As long as the spellings are same, Google Sheets will recognize the keyword and proceed the function automatically.
Range and conditions must be given in pairs.
All functions must be enclosed using brackets “()” after their keywords. “(“ indicates the start while “)” indicates the end of functions.
Parameters are always provided within “( )“ for any function.
[] represents that this element is optional and may be skipped.
Mathematical conditions need to be written inside double quotation marks as well as text conditions.

Frequently Asked Questions

Can I Use COUNTIF & COUNTIFS Functions to Find Column Letters in Google Sheets?

Yes, you can use the COUNTIF and COUNTIFS functions for finding column letters in google sheets. These functions allow you to search for specific criteria within a range and return the number of occurrences. By utilizing these functions in combination with other formulas and functions, you can identify and retrieve the column letters you need in your spreadsheet.

Are COUNTIF and COUNTIFS keyword case-sensitive in Google Sheets?

No, COUNTIF and COUNTIFS are not case-sensitive in Google Sheets. You may use upper or lower case letters. As long as the spells are same, there would not be an issue.

Is text condition case-sensitive in Google Sheets?

No, conditions are not case-sensitive in Google Sheets. Any combination of upper and lower case are considered the same for a same word. For example, “Oil” is the same as “oil” for text conditions in Google Sheets.

Can COUNTIF and COUNTIFS work without using equal sign “=” in the start of the cell?

No, no function in Google Sheets can be written without initiating a formula, i.e. “=” sign. “=” indicates the start of any formula. Google Sheets provide various functions with the help of keywords. Those keywords are only recognized once the cell has started with = sign to indicate the start of formula. Otherwise they will simply appear as text in the cell. In simple words, functions can only be used inside formula.

Can we use multiple conditions in COUNTIF function in Google Sheets?

No, COUNTIF function can only accept one range and parameter. In case of multiple conditions and parameters, users are advised to use COUNTIFS function provided by Google Sheets. Working for both is the same, the difference is that COUNTIF takes only one condition and range while COUNTIFS may take multiple sets of range and condition.

Can we use multiple COUNTIF or COUNTIFS in a single formula in Google Sheets?

Yes, we may use any number of COUNTIF and COUNTIFS functions inside one formula in Google Sheets. One such demonstration is also shown in above article.

Google Sheets also permits us to use COUNTIF and COUNTIFS together in a formula.

Conclusion

There are many functions provided by Google Sheets to help the users in their calculations and analysis and help to save precious time of users. COUNTIF and COUNTIFS functions are also provided by Google Sheets to ease the calculations for users. These are used to count the number of cells satisfying a certain condition or set of conditions over ranges.

Feel free to share your thoughts in the comment section below.

Thanks for Reading!