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(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.
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.
Here we select F2 through F10 to indicate that it’s the opening gross data that will be summed.
The first condition to be met for inclusion in the sum is in the Distributor column, B2 through B10.
When using text conditions in Google Sheets function, the letters must be surrounded by double-quotes.
Our second set of conditions is in the Shooting Location column, C2 through C10.
Completing the syntax and hitting enter runs the function and produces your result.
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:
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.
To sum opening grosses, we select the full range of data in the F column: =SUMIFS(F2:F10.
Our first set of criteria involves the budget column: =SUMIFS(F2:F10,E2:E10.
To include movies whose budget was larger than $10 million, we begin the argument with double-quotes and our greater than (>) operator.
Adding the Theaters column as our second criteria range, the syntax continues: =SUMIFS(F2:F10,E2:E10,">10,000,000",G2:G10.
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
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.
Since we’re adding the total number of theaters, we select G2 through G10: =SUMIFS(G2:G10.
Our first criteria falls in the budget column: =SUMIFS(G2:G10,E2:E10.
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".
The second set of criteria spans Column D, which records the release dates: =SUMIFS(G2:G10,E2:E10,">20000000",D2:D10.
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".
If you want one or another criteria to identify data that only occupies part of a cell, Google Sheets supports the use of wildcards:
You can also establish a criterion that will include data in the sum on the basis of whether or not the cell is blank.
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.
Use our contract reminder software to easily automate contract reminders from your spreadsheet in just a few clicks.
If you want to learn how to automate emails from Google Sheets, we also suggest checking out our step-by-step tutorial by clicking here.
