In Google Sheets, there are various functions that help you count data. For instance, the COUNT function allows you to count specific types of data in a range, and the COUNTA function counts all types of data in a given range.
But what if you need to count data based on more than one condition? This is where the Google Sheets COUNTIFS function comes to the rescue.
In this article, we will walk you through the easy steps of using the COUNTIFS function in Google Sheets, using straightforward examples.
So, whether you’re a newbie or just looking for a quick refresher, we’ve got you covered on how to make the most of the COUNTIFS function in Google Sheets.
Understanding What The COUNTIFS Function in Google Sheets Does
The COUNTIFS function in Google Sheets is a powerful tool that allows you to count cells in a specified range based on multiple criteria across different columns. This function is incredibly useful when you need to filter and count data that meets specific conditions.
It’s important to note that the COUNTIFS function works by counting cells that meet all the criteria you specify. This distinguishes it from simply counting characters or single conditions, making it a valuable asset for more complex data analysis tasks.
In other words, the COUNTIFS function acts as a filter, identifying and tallying only those cells that simultaneously satisfy all the conditions you define. This level of precision allows you to gain insights into your data that would be challenging to uncover through simple counting methods.
But before we dive deeper into how the COUNTIFS function in Google Sheets works, let’s help you understand the COUNTIFS syntax.
Knowing this will give you a solid understanding of how this function works, allowing you to maximize its potential fully.
COUNTIFs Google Sheets Syntax
The COUNTIFS syntax is very crucial to understanding how to fully maximize this function. So we want to use this opportunity to break the COUNTIFS function’s syntax in Google Sheets:
The syntax for COUNTIFS in Google Sheets looks like this:
=COUNTIFS(criteria_range1, condition1, [criteria_range2, condition2, ...])Now, let’s understand each part:
- criteria_range1: This is the first range of cells you want to count based on a specific condition. It’s the area of your data where you want to apply your first rule.
- condition1: This represents the condition that a cell within criteria_range1 should meet in order to be counted. It’s like setting the criteria for what you want to count within the first range.
- [criteria_range2, condition2, …]: If you need to count data based on more conditions and across different ranges, you can add additional pairs of criteria_range and condition. This allows you to filter and count data that meets multiple criteria across various parts of your spreadsheet.
Important Note: When you use additional criteria, the number of rows and columns in your criteria_range should match the same dimensions in your condition. This ensures that Google Sheets correctly matches the criteria to the corresponding range for accurate counting.
For a better perspective, the COUNTIFS function carefully looks at each cell in the selected range (criteria_range1) and counts only the cells that meet all the conditions you specify. In the end, it tells you how many cells it counted that met all the criteria.
By understanding this simple syntax, you’ll be well on your way to using COUNTIFS effectively in your Google Sheets documents. Let’s move on to practical examples to see how it all comes together.
COUNTIFS Function in Google Sheets: The Step-By-Step Guide
Now that we’ve covered the fundamentals, especially the COUNTIFS syntax, it’s time to roll up our sleeves and dive into some practical examples.
In this section, we’ll walk through a few examples that will make the concept of the COUNTIFS function in Google Sheets crystal clear.
To make it all clear, we’ll use some sample data. This data will serve as our playground to explore the various ways you can make use of the COUNTIFS function in Google Sheets.
If you want to follow along with today’s tutorial, you can copy the sample sheet by clicking the below link.
Utilizing COUNTIFS in Google Sheets with Numeric and Text Criteria
When you’re using numbers as criteria in Google Sheets’ COUNTIFS formula, you can enter them directly in the formula, or you can refer to a cell that contains the number. However, if you’re working with text or text strings as criteria, you need to enclose the text within quotation marks.
Let’s use our sample data as an example. If we wanted to count the items that cost $350 and were sold online, here is how we would go about it.
Step 1: Select a Blank Cell
The first thing we need to do is choose a blank cell. This is where we want our count result to be generated. For this example, we will choose cell F5.
Step 2: Enter COUNTIFs Formula
After selecting the cell where you want the count result to be generated, the next thing we need to do is enter the COUNTIFs formula. To do that, navigate to the formula bar and type in the following formula:
=COUNTIFS(D5:D19,350,C5:C19,"Online")
Let’s break the formula down:
=COUNTIFS(D5:D19,350,C5:C19,"Online")- =COUNTIFS: This is the function itself, which you start with to tell Google Sheets that you want to count based on specific conditions.
- D5:D19: This is the first range of cells you want to evaluate. In this case, it’s cells from D5 to D19, where you’re likely keeping the item prices.
- 350: This is the first condition, and it’s a numeric value. The formula will count how many cells in the range D5:D19 contain the value 350.
- C5:C19: This is the second range of cells you want to evaluate. In this case, it’s the cells from C5 to C19, where you’re probably storing the sales channel information.
- “Online”: This is the second condition, and it’s a text value enclosed in quotation marks. The formula will count how many cells in the range C5:C19 contain the text “Online.”
In summary, this COUNTIFS formula counts the number of items that meet two conditions: they have an item price of 350 (a numeric condition) and were sold through the “Online” sales channel (a text condition) in the specified ranges of your spreadsheet. The formula calculates and returns the count of items that satisfy both of these conditions.
Step 3: Hit Enter
Once you’re done typing the formula like we showed you in the previous step, all you need to do is press the Enter button on your keyboard and Google Sheets will automatically generate the count result in the selected cell.
Here is what our spreadsheet looks like after executing this step:
By following these steps, you’ll be able to effectively use the COUNTIFS function in Google Sheets to count data based on both numeric and text criteria.
Using COUNTIFS with Logical Operators
In the previous section, we learned how to use COUNTIFS with numeric and text criteria. Now, we’ll take it a step further by exploring how to apply COUNTIFS with logical operators.
For starters, logical operators are like tools that help us compare values and conditions. They include:
- > (greater than)
- < (less than)
- = (equal to)
- >= (greater than or equal to)
- <= (less than or equal to)
- <> (not equal to)
In this example sheet, we want to count the number of items that were sold via the retail channel and have a value greater than $350. To achieve that, here is how we will go about it.
Step 1: Choose A Blank Cell
As we did in the first example, we will need to choose a blank cell in our sample spreadsheet. This is where we want the count result to be generated. For this example, we will select cell F5.
Step 2: Enter the COUNTIFS Formula
With the cell where you want the count result generated selected, navigate to the formula bar and type in the following formula
=COUNTIFS(D5:D19, ">350", C5:C19, "Retail")
Let’s break down the COUNTIFS formula:
=COUNTIFS(D5:D19, ">350", C5:C19, "Retail")- =COUNTIFS: This is the COUNTIFS function you begin with to tell Google Sheets that you want to count based on specific conditions.
- D5:D19: This is the first range of cells you want to evaluate. In this case, it’s cells from D5 to D19, where you’re probably storing the item prices.
- “>350”: This is the first condition, and it uses a logical operator (>) to check if the values in the range D5:D19 are greater than 350. In this case, it will count how many cells contain numbers greater than 350.
- C5:C19: This is the second range of cells you want to evaluate. It’s cells from C5 to C19, where you likely have information about the sales channels.
- “Retail”: This is the second condition and it’s a text value enclosed in quotation marks. The formula will count how many cells in the range C5:C19 contain the text “Retail.”
In a nutshell, this COUNTIFS formula counts the number of items where both conditions are met: the item’s price is greater than 350 (a numeric condition), and it was sold through the “Retail” sales channel.
Step 3: Press Enter
Done entering the formula like we showed you in the previous step? Great. Now, press the Enter button on your keyboard. This action authorizes Google Sheets to generate the count result in the selected cell.
Here, check out what our spreadsheet looks like:
If you followed the steps we detailed in this section, you should be able to use COUNTIFS with logical operators to count data based on more complex conditions in your Google Sheets document.
Note: In this example, using COUNTIFS in Google Sheets, we get a result of 6. This means we’re counting items that meet two conditions: they were sold via the retail channel, and their value exceeds $350.
However, it’s important to note that when we use logical operators like >, <, =, etc., we cannot use cell references. This is because the logical operators need criteria to be in quotation marks.
If we don’t put cell references in quotes, Google Sheets will treat them as text and not as cell references. As a result, it may return an error in your formula.
So, to avoid errors when using logical operators with COUNTIFS, be sure to enclose your criteria in quotation marks to make them work correctly.
Counting Items within the Same Category with COUNTIFS
Now, let’s delve into a unique scenario where you want to count items that meet different conditions, all within a single category.
For example, imagine you’re interested in counting how many items fall under both the “Laptop” and “Smartphone” categories. These conditions are part of the ‘Item Type’ category, which means we need to find a way to specify both conditions within the same column.
If we simply use the below formula for it, we might not get the desired result.
=COUNTIFS(B5:B19, "Laptop", B5:B19, "Smartphone")Let’s show you what we mean:
Step 1: Select A Blank Cell
First things first, choose a blank cell in your spreadsheet. This is where you want the count result to be generated. For this example, we will go with cell F5.
Step 2: Enter the COUNTIFS Formula
After selecting the cell where you want the count result to be generated, head over to the formula bar and type the following formula:
=COUNTIFS(B5:B19, "Laptop", B5:B19, "Smartphone")
Let’s break down the COUNTIFS formula:
=COUNTIFS(B5:B19, "Laptop", B5:B19, "Smartphone") - =COUNTIFS: This is the COUNTIFS function, indicating that you want to count cells based on specific conditions.
- B5:B19: This is the range of cells you want to evaluate. It covers cells from B5 to B19, likely where you have data about the products.
- “Laptop”: This is the first condition. It is text enclosed in quotation marks, meaning you want to count how many cells in the range B5:B19 contain the text “Laptop.”
- B5:B19 (again): This is specifying the same range as before, and this time, you’re adding a second condition.
- “Smartphone”: This is the second condition, also enclosed in quotation marks. It means you want to count how many cells in the same range B5:B19 contain the text “Smartphone.”
In summary, this COUNTIFS formula counts the number of items in the range B5:B19 that satisfy either of the two conditions: they are either “Laptop” or “Smartphone.” It’s important to note that COUNTIFS allows you to count based on multiple conditions, but in this case, it might not give the expected result as it will count both “Laptop” and “Smartphone” separately.
Step 3: Hit Enter
After entering the formula like we showed you, all you need to do is hit the Enter button on your keyboard. The count result should be generated in the blank cell we selected earlier.
If you look at the screenshot above, you’ll notice that applying the formula we showed you doesn’t provide the correct result.
This happened because the formula looks for cells where both conditions are met, acting like an “AND” function. Since there are no cells in the range B2:B22 that contain both “Laptop” and “Smartphone” at the same time, the formula returns 0.
To solve this issue and count items separately, you can use a combination of functions: ARRAYFORMULA, SUM, and COUNTIFS. Here’s the formula:
=ARRAYFORMULA(SUM(COUNTIFS(B5:B19,{"Laptop","Smartphone"}))Make sure to include all your criteria within curly braces, like this: {“Laptop”, “Smartphone”}.
With this new formula, it will count cells containing “Laptop” and “Smartphone” individually and then add them together. This way, you get an accurate count of both items within your data.
Let’s apply the above formula to see if we get the right result this time around.
Step 1: Replace Formula
Before, we tried using the formula =COUNTIFS(B5:B19, “Laptop”, B5:B19, “Smartphone”) to count items within the same category, but it didn’t give us the result we were looking for.
To make it work correctly, we will change the formula to this:
=ARRAYFORMULA(SUM(COUNTIFS(B5:B19,{"Laptop","Smartphone"}))Now, let’s go to the formula bar and type in this new formula. This updated formula will help us count items within the “Laptop” and “Smartphone” categories accurately.
Step 2: Press Enter
Now that we have typed in the new formula, it’s time to execute the formula. To that, simply press Enter on your keyboard. The result should be generated in the selected cell.
Here is what we got after executing this new formula.
The formula we’ve just introduced will now individually count the cells that contain the values “Laptop” and “Smartphone.”
After counting each category separately, it will then add them together to provide you with the final result.
Final Thoughts
In this guide, we’ve explored the powerful COUNTIFS function in Google Sheets, which is a valuable tool for counting and analyzing data that meets specific conditions.
Whether you’re dealing with numeric or text criteria, logical operators, or even counting items within the same category, COUNTIFS offers a versatile solution for your data analysis needs.
Here are some key takeaways to remember:
- Versatility: COUNTIFS allows you to count cells based on multiple criteria, making it adaptable to various scenarios.
- Numeric and Text Criteria: You can use COUNTIFS with both numeric and text criteria; just remember to enclose text criteria in quotation marks.
- Logical Operators: When using logical operators like >, <, and =, ensure your criteria are enclosed in quotes and follow the correct syntax.
- To count items within the same category using COUNTIFS, you can use ARRAYFORMULA and SUM in combination with COUNTIFS.
By mastering the COUNTIFS function in Google Sheets, you can efficiently analyze your data, gain insights, and make informed decisions based on specific conditions.