What is the SUMIF Function in Google Sheets?
Google Sheets’ SUMIF formula works to sum data from cells only if they meet a certain condition.
SUMIF Syntax Google Sheets
=SUMIF(range, criterion, [sum_range])
Where:
range=
the group of cells against which the criterion will be tested.
criterion=
identifies the condition the cells in the range must meet if the associated numbers are to be summed.
[sum_range]=
an optional argument indicating the cells to sum. If left blank, the sum range will default to the range.
SUMIF Function Examples
SUMIF function with exact text match
The SUMIF function is often employed to tally numbers associated with a particular category identified by text.
Say a car dealership wanted to determine the total sales for a particular make and model during a given period—in this case, the 2022 Toyota Prius.
1. Type =SUMIF( into an empty cell
2. Choose the range
Here the product column serves as the range since it’s where the make and model are indicated by name.
3. Enter the criterion in double quotation marks
When using a text criterion, the words must be surrounded by double quotation marks. Here, “2022 Toyota Prius” is the condition that the function will identify in cells B2 through B30 in order to include the number value in the sum range in the tally.
4. Select the sum range
The sum range identifies the cells containing the numbers that will be included in the sum. In this case, we select D2–D30 to capture the numbers in the Total column.
5. Type ) and hit enter
Closing the syntax with a quotation mark and pressing the enter key will complete the function.
SUMIF function with wildcards
If you needed to produce a sum associated with only part of the text in a cell, you can use one of the three wildcard characters:
- 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
Let’s say you wanted to sum the total sales for only Toyota models.
1. Type =SUMIF( into an empty cell
2. Choose the range
3. Enter the criterion surrounded by the right wildcard characters and double quotation marks
To capture the sales totals for all Toyotas in the Product column, set the name of the make between asterisks and double quotation marks.
4. Select the sum range
5. Type ) and hit enter
SUMIF function with numbers
If you wanted to sum the total sales for all the cars from a particular year, let’s say 2021, the SUMIF function works just as well when the criterion is a numeric value.
1. Type =SUMIF( into an empty cell
2. Choose the range
3. Enter the criterion
Unlike text criteria, a numeric criterion doesn’t necessarily have to be set within quotation marks to work—but when you’re looking for a partial match, you must use the wildcards and double-quotes as you would with text.
4. Select the sum range
5. Type ) and hit enter
SUMIF function with comparison operators
With comparison operators, you can refine the data tallied. In Google Sheets, these include:
- Equals (=)
- Not equal to (<>)
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)
For instance, the dealership might want to total sales from the days in which a particular model brought in more than $100,000.
1. Type =SUMIF( into an empty cell
2. Choose the range
3. Enter the criterion
<alt =”comparison criterion Google Sheets function example” >
Both the operator and the number serving as the criterion must be enclosed in double quotation marks.
4. Select the sum range
In our example, the range in which we’ll test the criterion is the same as the range we want to total, so we do not need to enter a sum range.
5. Type ) and hit enter
SUMIF function with Dates
There are a few different ways to use the SUMIF function to sum data based on conditions set by dates. For example, you might want to sum only the sales made during a particular part of the month: say, after January 15, 2022.
1. Type =SUMIF( into an empty cell
2. Choose the range
Here we select A2 through A3, in order to test the criterion against all the dates in the data set.
3. Enter the criterion
<alt =”sum if criterion for dates in Google Sheets” >
This is where things get a little trickier. In most cases using this formula in Google Sheets, you’ll want to start with a comparison operator, in order to indicate that you want the data that falls before, after, or between a particular date or dates—and then set it within quotation marks. Here, where we are looking for data after a particular date, we begin: “>”.
Next we must use the DATE function so that Google Sheets can properly interpret the info. First, use the ampersand (&) to concatenate the two formulas, and then enter the appropriate date in the following syntax: DATE (year, month, day).
In our example, the full argument then reads: ">"&DATE(2022,1,15).
4. Select the sum range
5. Type ) and hit enter
SUMIF function with blank and non-blank cells
Another way to cut through the data you want to add up with the Google Sheets SUMIF function is to set the criterion to include cells on the basis of whether or not they are 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.
Sample Sheet
Click here to make a copy of our sample sheet. It contains the data set that we used in the examples above so that you can practice and master this important function!
Related Functions:
IF: Allows you to check for specific conditions across a dataset
Using SUMIFS: Use SUMIFS to tally numbers in a range that meet more than one condition.
COUNT IF: In Google Sheets, COUNTIF can be used to count numbers in a range which meet a given criterion.
How to Use COUNTIFS: Counts numbers in a range meeting multiple different criteria.
COUNT IF Not Blank: Learn how to count cells that are not empty.
IFS Formula: IFS is a more elegant way to evaluate data against multiple criteria.
IF ERROR: IFERROR is a tool that searches Google Sheets data for errors and replaces error messages with specified text or a blank cell.
IF THEN Statements: IF THEN in Google Sheets allows you to evaluate data against a condition and take a corresponding action when the result is TRUE.
IF AND Statements: Combining the IF and AND functions allows you to include multiple conditions that must be met to return a TRUE result.
IF ELSE: Google Sheets’ IF ELSE statement evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF: Nesting multiple IF arguments allows you to for TRUE or FALSE results against more than one logical expression
IF OR: IF OR evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: By combining three different Google Sheets tools, you can search for cells that contain particular text.
AVERAGEIF: Use AVERAGE IF to calculate an average for numbers within a data range which meet provided criteria.
Recommended articles
More resources
