The Google Sheets SUMIFS function is used to sum ranges based on conditional tests. In other words, the SUMIFS in Google Sheets adds values to a total only when multiple conditions are met.
Suppose we want to calculate the total for Large products in New York:
The SUMIFS function to calculate the total for two conditions, Large and New York, is:
which gives an answer of $3,020.
In this case, there are four rows, highlighted in yellow, that match the criteria of New York in column B and Large in column C.
The total values of these four rows are added together by the SUMIFS function, all the other rows are discarded.
🔗 Get this example and others in the template at the bottom of this article.
Google Sheets SUMIFS Function Syntax
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
It takes a minimum of three arguments:
This is the range to use to calculate the sum total.
This range is the data that you want to test with your criterion. It must be the same size as the sum range (i.e. have the same number of rows).
The criterion is the test you want to apply.
Although it works perfectly well with a single criterion, it’s more common to use the SUMIF function in this case. Just be aware that the order of the test range and the sum range are reversed in the SUMIF.
[criteria_range2, criterion2, ...]
We can add optional pairs of range/tests to SUMIFS to test more conditions. Each range must match the size of the original sum range (i.e. have the same number of rows).
SUMIFS Function Notes
The following dataset (available in the template below) is used in all the examples that follow:
Text criterion must be enclosed by quotes.
This formula calculates the total revenue for the Training sector in Denmark:
Exact matches with numeric values do not require quotes but logical tests do require quotes.
For example, this formula calculates the total revenue from Consultant sector orders that have exactly 10 items as the quantity:
However, if the criterion includes a logical test, then the formula requires double-quotes.
For example, this formula calculates the total revenue of orders where revenue > $20k:
Notice how this last SUMIFS formula only has three arguments, i.e. one pair of test range + criterion.
In this case, a SUMIF would also work and is more succinct:
Logical operators are used to create conditional tests:
- > greater than
- >= greater than or equal to
- < less than
- <= less than or equal to
- = equal to
- <> not equal to
They must be enclosed with double-quotes.
This formula calculates the total revenue for the Training sector and orders with more than 5 items:
The SUMIFS function is case insensitive. For example, testing for “training”, “TRAINING”, or “Training” will all produce identical results.
SUMIFS Function with Blanks and Non Blanks
To sum blank cells in a range, use empty double-quotes.
For example, this formula tests for Software and blank customer names:
To sum non-blank cells in a range, use the not-equal logical operator “<>“.
Here, this formula tests for Germany and non-blank customer names:
Reference another cell
The test criterion for SUMIFS function can be contained in a different cell and referenced by the SUMIFS formula:
If you want to use a logical operator, then it must still be enclosed in double-quotes. Use an ampersand to combine with the reference cell, e.g.
Using Wildcards with SUMIFS
SUMIFS Google Sheets supports three wildcards, *, ?, and ~.
The star * matches zero or more characters.
The question mark ? matches exactly one character.
The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards.
Let’s see some examples:
This formula calculates the revenue from “Pro” products from Denmark:
It will include “Office Pro”, “Pro Balance Suite”, “ViperLabs Pro”, etc.
Question Mark Example
This formula calculates revenue from customers 10, 11 or 12 in Software sector:
The tilde escape character lets you search for a * or ? without the special meaning above being applied.
For example, this formula calculates total revenue for Germany and starred products Office Pro *:
=SUMIFS(G2:G21,B2:B21,"Germany",C2:C21,"Office Pro ~*")
If your input ranges are different sizes then you’ll see a #VALUE! error, saying “Array arguments to SUMIFS are of different size.”.
which results in this error message:
It’s easily fixed: simply change your ranges to be the same dimensions, i.e. all have the same number of rows.
SUMIFS Function Template
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.
The SUMIFS function is also covered in the Day 2 lesson of my free Advanced Formulas 30 Day Challenge course.
It’s part of the Math family of functions in Google Sheets, along with its siblings the SUMIF function (for a single criterion), the COUNTIF function (for counting with a single criterion), and the COUNTIFS function (for counting with multiple criteria).
You can also read about it in the Google Documentation.