What is the SUMIFS Function in Google Sheets?
The Google Sheets SUMIFS function sums data from cells that meet multiple criteria—expanding upon the SUMIF function you would use to add data on the basis of a single criterion.
SUMIFS Syntax Google Sheets
=SUMIFS(sum_range, criteria_range1, criterion1,[ criteria_range2, criterion2, …])
Where:
sum_range=
the cells whose numeric values will be summed by the function if they meet the specified criteria.
criteria_range1=
the range of cells to be evaluated for inclusion against criterion 1. In Google Sheets, both criteria ranges must be the same size as the sum range or you will get an error.
criterion1=
specifies the condition that the cells in range 1 must meet for the associated values to be included in the sum.
criteria_range2=
the range of cells to be evaluated for inclusion against criterion 2.
criterion2=
specifies the condition that the cells in range 2 must meet for the associated values to be included in the sum.
The syntax continues in the same form for all the additional criteria you wish to include. Google Sheets supports up to 127 criteria for this function.
SUMIFS Function Examples
SUMIFS function with text criteria
You might use this Google Sheets function to calculate a sum based on a set of different text criteria.
Say you were analyzing the box office performance of different films over an actor’s career and you wanted to total the opening gross only for the movies they released with a particular distributor (here, Restoration Pictures) that were also shot in Los Angeles. You can open our example data set and use it to follow this example by clicking here.
1. Type =SUMIFS( into an empty cell
2. Choose the sum range
Here we select F2 through F10 to indicate that it’s the opening gross data that will be summed.
3. Choose the first criteria range
The first condition to be met for inclusion in the sum is in the Distributor column, B2 through B10.
4. Enter the first criterion
When using text conditions in Google Sheets function, the letters must be surrounded by double-quotes.
5. Choose the second criteria range
Our second set of conditions is in the Shooting Location column, C2 through C10.
6. Enter the second criterion
7. Type ) and hit enter
Completing the syntax and hitting enter runs the function and produces your result.
SUMIFS function with numeric criteria and comparison operators
You may also want to use Google Sheets to perform a calculation with this function determined by numeric criteria. In many such cases, you will also be employing comparison operators, so that you can refine the data with relative conditions.
The comparison operators viable in Google Sheets include:
- Equals (=)
- Not equal to (<>)
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)
When an absolute number serves as your criteria, you do not need to enclose it with quotation marks, but you must include them when using comparison operators.
Let’s say you wanted to tally the opening grosses for movies with budgets larger than $10 million that opened in fewer than 3,800 theaters.
1. Type =SUMIFS( into an empty cell
2. Choose the sum range
To sum opening grosses, we select the full range of data in the F column: =SUMIFS(F2:F10.
3. Choose the first criteria range,
Our first set of criteria involves the budget column: =SUMIFS(F2:F10,E2:E10.
4. Enter the first criterion
To include movies whose budget was larger than $10 million, we begin the argument with double-quotes and our greater than (>) operator.
5. Choose the second criteria range
Adding the Theaters column as our second criteria range, the syntax continues: =SUMIFS(F2:F10,E2:E10,">10,000,000",G2:G10.
6. Enter the second criterion
Since we are once again employing comparison operators, the argument begins with quotation marks and our less than (<) operator: =SUMIFS(F2:F10,E2:E10,">10,000,000",G2:G10,"<3,800".
7. Type ) and hit enter
SUMIFS function with date criteria
Dates can also serve as one or more of the criteria in the function. Let’s say we want to add the total number of theaters for movies with a budget larger than $20 million that were released before March 2020.
1. Type =SUMIFS( into an empty cell
2. Choose the sum range
Since we’re adding the total number of theaters, we select G2 through G10: =SUMIFS(G2:G10.
3. Choose the first criteria range
Our first criteria falls in the budget column: =SUMIFS(G2:G10,E2:E10.
4. Enter the first criterion
Since we’re employing comparison operators, we begin with double quotes and the more than (>) character and close the quotes at the end of the argument: =SUMIFS(G2:G10,E2:E10,">20000000".
5. Choose the second criteria range
The second set of criteria spans Column D, which records the release dates: =SUMIFS(G2:G10,E2:E10,">20000000",D2:D10.
6. Enter the second criterion
The date can be entered in DD/MM/YYYY for or by using the DATE function with the following syntax: DATE (year, month, day).
Since this criterion must indicate dates prior to March 2020, we must begin the argument with double quotes and the Less Than (<) comparison operator: =SUMIFS(G2:G10,E2:E10,">20000000",D2:D10,"<03/01/2020".
7. Type ) and hit enter
Using Wildcards
If you want one or another criteria to identify data that only occupies part of a cell, Google Sheets supports the use of wildcards:
- Question mark (?), to match a single character
- Asterisk (*), to match a sequence of characters
- Tilde (~), which can be employed as an escape character when the partial text you want to identify contains one of the other wildcards
Using Blank and Non-Blank Cells
You can also establish a criterion that will include data in the sum on the basis of whether or not the cell is blank.
- Empty double-quotes (“”) in the criterion field will sum all blank cells in a range
- The not-equal comparison operator (“<>”) in the criterion field (surrounded by double-quotes) will sum all non-blank cells in a range.
Related Functions:
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
COUNTIF: Count instances that fulfill a certain condition
COUNTIFS: Used for counting rows that fulfill two or more criteria
COUNT IF Not Blank: Count cells that are not empty.
IFS: Allows you to combine many if functions in one statement
IFERROR: Replaces formula error messages with specified text or a blank cell.
IF THEN: Evaluates data against a condition and take a corresponding action when the result is TRUE.
IF AND: Combining the IF and AND functions allows you to include multiple conditions that must be met to return a TRUE result.
IF ELSE: Evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF: Learn how to use multiple if statements in a single formula
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
Recommended articles
More resources
