Don’t forget to share this post

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 post, 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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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.

SUMIF in Google Sheets Formula Examples Sales Data
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Sales Data

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.
SUMIF in Google Sheets Formula Examples Add Formula
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add SUMIF Formula
  1. 2. Select the range to which you want to apply the criterion. In this case, the column with the products.
SUMIF in Google Sheets Formula Examples Select Range
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Range
  1. 3. Add the criterion you want to apply to the selected range. For this example, I want an exact match for “productA”.
SUMIF in Google Sheets Formula Examples Add Criterion
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add SUMIF Criterion
  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.
SUMIF in Google Sheets Formula Examples Select Sum Range
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Sum Range
  1. 5. That’s it. We have the total for sales of productA.
SUMIF in Google Sheets Formula Examples SUMIF Result
SUMIF & SUMIFS in Google Sheets: Formula & Examples - SUMIF Result
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
SUMIF in Google Sheets Formula Examples Add Formula 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add SUMIF Formula
  1. 2. Select the range to which you want to apply the criterion. In this example, I’ll select the column with the dates.
SUMIF in Google Sheets Formula Examples Select Range 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Range
  1. 3. The criterion needs to check whether the date is greater than or equal to February 1st, 2022.
SUMIF in Google Sheets Formula Examples Add Criterion 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add SUMIF Criterion
  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.
SUMIF in Google Sheets Formula Examples Select Sum Range 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Sum Range
  1. 5. That’s it. The total amount for sales made on or after February 1, 2022, is $332,194.88.
SUMIF in Google Sheets Formula Examples SUMIF Result 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - SUMIF Result

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.
SUMIF in Google Sheets Formula Examples Add SUMIFS Formula
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add SUMIFS Formula
  1. 2. Select the range containing the values you want to sum.
SUMIF in Google Sheets Formula Examples Select Sum Range 3
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Sum Range
  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.
SUMIF in Google Sheets Formula Examples Select First Range
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select First Range
  1. 4. Add the criterion you want to apply to the first range. In this example, I want an exact match for “productA”.
SUMIF in Google Sheets Formula Examples Add First SUMIFS Criterion
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add First SUMIFS Criterion
  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.
SUMIF in Google Sheets Formula Examples Select Second Range
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Second Range
  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.
SUMIF in Google Sheets Formula Examples Add Second SUMIFS Criterion
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add Second SUMIFS Criterion
  1. 7. That’s it. Sales of productA made on or after the selected date total $70,811.22.
SUMIF in Google Sheets Formula Examples SUMIFS Result
SUMIF & SUMIFS in Google Sheets: Formula & Examples - SUMIFS Result
How To Merge Google Sheets Manually Automatically
How To Merge Google Sheets (Manually & Automatically)

Oftentimes we need to combine data from multiple sheets into one master sheet. Here’s how to merge Google Sheets manually and automatically.

READ MORE

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.
SUMIF in Google Sheets Formula Examples Add SUMIFS Formula 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add SUMIFS Formula
  1. 2. Select the range of cells containing the values you want to add.
SUMIF in Google Sheets Formula Examples Select Sum Range 4
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Sum Range
  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.
SUMIF in Google Sheets Formula Examples Select First Range 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select First Range
  1. 4. Add the first criterion. In this example, I want to check that values are greater than or equal to $500.
SUMIF in Google Sheets Formula Examples Add First SUMIFS Criterion 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add First SUMIFS Criterion
  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.
SUMIF in Google Sheets Formula Examples Select Second Range 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Select Second Range
  1. 6. Add the second criterion. Below, I have specified that values should be less than or equal to $1000.
SUMIF in Google Sheets Formula Examples Add Second SUMIFS Criterion 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - Add Second SUMIFS Criterion
  1. 7. That’s it. You have the total based on values between $500 and $1000.
SUMIF in Google Sheets Formula Examples SUMIFS Result 2
SUMIF & SUMIFS in Google Sheets: Formula & Examples - SUMIFS Result

How to Automate Your Data on Top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

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 post on COUNTIF & COUNTIFS in Google Sheets: Formula & Examples.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Dec 19 2022, Updated Dec 20 2022