How to Use SUMIF and COUNTIF in Google Sheets
Google Sheets offers two powerful functions - SUMIF and COUNTIF - that can automate this process for you. In this article, we'll explore the ins and outs of these functions, including how to use them, their syntax, and some useful examples.
Need additional help? Want to try something more advanced than Google Sheets?
We are offering an Open Source Data Analysis Tool that is ready Out-of-the-Box.
Explore Data with RATH (opens in a new tab) Now.
Introduction to SUMIF and COUNTIF
Before we dive into the specifics of SUMIF and COUNTIF, let's define what they are and how they work.
What is SUMIF?
SUMIF is a function in Google Sheets that adds up the values in a range that meets certain criteria. For example, you could use SUMIF to add up the sales for a specific product or the expenses for a particular month.
What is COUNTIF?
COUNTIF is a function in Google Sheets that counts the number of cells in a range that meet certain criteria. For example, you could use COUNTIF to count the number of orders placed by a specific customer or the number of days that an employee was absent.
Using SUMIF and COUNTIF
Now that we understand the basics of these functions, let's explore how to use them in more detail.
Syntax of SUMIF
The syntax of SUMIF is as follows:
SUMIF(range, criteria, [sum_range])range: The range of cells that you want to evaluate.criteria: The criteria that you want to use to evaluate the range. This can be a number, a text string, a cell reference, or an expression that evaluates to a number or text string.sum_range(optional): The range of cells that you want to add up. If this argument is omitted, Google Sheets will add up the cells in the range argument.
Syntax of COUNTIF
The syntax of COUNTIF is as follows:
COUNTIF(range, criteria)range: The range of cells that you want to evaluate.criteria: The criteria that you want to use to evaluate the range. This can be a number, a text string, a cell reference, or an expression that evaluates to a number or text string.
Examples of SUMIF and COUNTIF
Let's look at some examples of how to use SUMIF and COUNTIF in Google Sheets.
Example 1: SUMIF Suppose you have a spreadsheet that contains the following data:
| Product | Sales |
|---|---|
| A | 100 |
| B | 200 |
| A | 150 |
| C | 75 |
| B | 125 |
If you want to calculate the total sales for Product A, you could use the following formula:
=SUMIF(A2:A6, "A", B2:B6)This formula tells Google Sheets to evaluate the range A2:A6 for cells that contain the value "A". It then adds up the corresponding values in the range B2:B6 to get a total sales figure of 250.
Example 2: COUNTIF Suppose you have a spreadsheet that contains the following data:
| Employee | Department |
|---|---|
| Alice | HR |
| Bob | IT |
| Charlie | HR |
| Dave | Sales |
| Emma | IT |
| Frank | Marketing |
If you want to count the number of employees in the HR department, you could use the following formula:
=COUNTIF(B2:B6, "HR")This formula tells Google Sheets to evaluate the range B2:B6 for cells that contain the value "HR". It then counts the number of cells that meet this criteria, giving you a total of 2.
Using Wildcards with SUMIF and COUNTIF
Sometimes you may want to evaluate a range based on a partial match. In these cases, you can use wildcards with SUMIF and COUNTIF.
Example 3: SUMIF with Wildcards Suppose you have a spreadsheet that contains the following data:
| Product | Sales |
|---|---|
| Apples | 100 |
| Bananas | 200 |
| Apples | 150 |
| Oranges | 75 |
| Bananas | 125 |
If you want to calculate the total sales for all products that contain the word "apple", you could use the following formula:
=SUMIF(A2:A6, "*apple*", B2:B6)This formula tells Google Sheets to evaluate the range A2:A6 for cells that contain the text string "apple". It then adds up the corresponding values in the range B2:B6 to get a total sales figure of 250.
Example 4: COUNTIF with Wildcards Suppose you have a spreadsheet that contains the following data:
| Employee | Department |
|---|---|
| Alice | HR |
| Bob | IT |
| Charlie | HR |
| Dave | Sales |
| Emma | IT |
| Frank | Marketing |
If you want to count the number of employees whose names contain the word "smith", you could use the following formula:
=COUNTIF(A2:A7, "*smith*")This formula tells Google Sheets to evaluate the range A2:A7 for cells that contain the text string "smith". It then counts the number of cells that meet this criteria, giving you a total of 1.
Conclusion
In conclusion, SUMIF and COUNTIF are powerful functions in Google Sheets that can save you time and effort when it comes to calculating sums and counts. By understanding their syntax and how to use wildcards, you can unlock even more possibilities for data analysis in your spreadsheets.