Google Sheets SUMIF multiple criteria

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.

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.

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.

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.

How do I use Sumif with multiple criteria in Google Sheets?

Can I use Sumif for multiple criteria?

Can you do Sumifs with 3 criteria?

How many criteria can Sumifs have in Google Sheets?