Google Sheets’ SUMIF formula works to sum data from cells only if they meet a certain condition.
=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.
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.
Here the product column serves as the range since it’s where the make and model are indicated by name.
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.
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.
Closing the syntax with a quotation mark and pressing the enter key will complete the function.
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:
Let’s say you wanted to sum the total sales for only Toyota models.
To capture the sales totals for all Toyotas in the Product column, set the name of the make between asterisks and double quotation marks.
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.
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.
With comparison operators, you can refine the data tallied. In Google Sheets, these include:
For instance, the dealership might want to total sales from the days in which a particular model brought in more than $100,000.
<alt =”comparison criterion Google Sheets function example” >
Both the operator and the number serving as the criterion must be enclosed in double quotation marks.
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.
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.
Here we select A2 through A3, in order to test the criterion against all the dates in the data set.
<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).
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.
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!
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.
If you want to learn how to send emails based on cell value in Google Sheets, we also suggest checking out our detailed guide.
