SUMIF & SUMIFS in Google Sheets: Formula & Examples

There are many reasons why you may need to sum the values of cells in Google Sheets. The SUM function and the ‘Autosum’ button are helpful but not very flexible. If you want to sum cells that meet specific criteria, you should consider using Google Sheets’ built-in functions: SUMIF and SUMIFS. The first allows you to specify a range of cells and the criterion cells must meet to be summed, while SUMIFS allows you to specify multiple ranges and criteria.

In this guide, you will learn about the SUMIF and SUMIFS 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 criteria, such as adding values based on the text contained in cells and values that fall within a specific date range. Additionally, you will learn how to use the SUMIFS function to sum values based on multiple criteria.

SUMIF & SUMIFS Function Syntax in Google Sheets

Let’s review the syntax for SUMIF and SUMIFS before using them.

SUMIF Function Syntax

Google Sheets’ SUMIF function has two required parameters and an optional parameter.

=SUMIF(range, criterion,[sum_range])

• 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.
• sum_range: the range you want to sum if different from the range used as the first parameter.

SUMIFS Function Syntax

Google Sheets’ SUMIFS function has three required parameters. However, SUMIFS allows you to provide additional parameters to test multiple ranges using different criteria.

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

The sum_range, the first range, and the first criterion are required arguments.

• sum_range: the range you want to sum.
• criteria_range1: the 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 Criteria or Conditions?

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

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

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 SUMIF Function in Google Sheets

Now that you have reviewed the function’s syntax, it's time to see some examples of how to use SUMIF in different situations.

Imagine you have a table with sales data, which includes the date, 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 Sum If Cell Contains Text

For this example, I want to sum the sales amounts corresponding to ‘productA’.

1. 1. In an empty cell, type the SUMIF formula.

1. 2. Select the range to which you want to apply the criterion. In this case, the column with the products.

1. 3. Add the criterion you want to apply to the selected range. For this example, I want an exact match for “productA”.

1. 4. Next, select the range with the values you want to sum if it's different from the first range selected. In this case, it is different, as I want to add the values in the sales amount column.
   

1. 5. That’s it. We have the total for sales of productA.

SUMIF Date Range Google Sheets

Imagine that you need to sum values based on the date when sales date rather than the product or any other text value. For this example, I will find the total amount for sales made on or after February 1st, 2022.

1. 1. Type the SUMIF function in an empty cell.

1. 2. Select the range to which you want to apply the criterion. In this example, I’ll select the column with the dates.

1. 3. The criterion needs to check whether the date is greater than or equal to February 1st, 2022.

1. 4. Next, you need to select the range with the values to be summed. Below, I have chosen the column with the sales amounts.

1. 5. That’s it. The total amount for sales made on or after February 1, 2022, is $332,194.88.

Examples of How to Use the SUMIFS Function with Multiple Criteria in Google Sheets

The COUNTIFS function allows you to sum values based on multiple criteria, which can be applied to multiple ranges. For the sake of simplicity, the examples below are based on the same dataset as the one used for SUMIF.

SUMIFS with Multiple Criteria Applied to Multiple Ranges

For this example, I want to sum the amounts for all sales of productA made on or after February 1, 2022.

1. 1. In an empty cell, type the SUMIFS formula.

1. 2. Select the range containing the values you want to sum.

1. 3. Select the first range to which you want to apply the first criterion. In this case, it’s different from the sum range, as I want to check the product column.

1. 4. Add the criterion you want to apply to the first range. In this example, I want an exact match for “productA”.

1. 5. Next, select the second range. This is the range to which you want to apply the second criterion. Below, I have selected the date column.

1. 6. Add the second criterion, which will be applied to the second range. For this example, I have specified that the date should be on or after February 1, 2022.

1. 7. That’s it. Sales of productA made on or after the selected date total $70,811.22.

SUMIFS with Multiple Criteria Applied to One Range

SUMIFS can also be used by applying multiple criteria to a single range. Let’s see how you can find the total sales amount for sales with a value between $500 and $1000.

1. 1. Type the SUMIFS formula into an empty cell.

1. 2. Select the range of cells containing the values you want to add.

1. 3. Select the range to which you want to apply the first criterion. In this case, it’s the same as the sum range.

1. 4. Add the first criterion. In this example, I want to check that values are greater than or equal to $500.

1. 5. Select the range you want to check using the second criterion. In this case, it’s the same as the sum range and the first range.

1. 6. Add the second criterion. Below, I have specified that values should be less than or equal to $1000.

1. 7. That’s it. You have the total based on values between $500 and $1000.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

SUMIF and SUMIFS are versatile and easy-to-use functions. You can add values based on single or multiple criteria, and these criteria can be applied to single or multiple ranges. You now know the syntax of both functions and how to build criteria using comparison and logical operators on numeric and text values. You also have examples of various scenarios using both functions, with step-by-step instructions.

To learn more about the related functions COUNTIF and COUNTIFS, check out our guide on COUNTIF & COUNTIFS in Google Sheets: Formula & Examples.