In this article:

SUMIF Google Sheets: Formula, Syntax and Examples [2024]

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.  

Sum If with text match Google Sheets function

1. Type =SUMIF( into an empty cell

Google Sheets sum if function Step 1

2. Choose the range

Sum if function in Google Sheets Step 2

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

Google Sheets sum if function Step 3

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

How to select the sum range in Google Sheets sum if function

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

Final step with text criterion Google Sheets sum if function

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

Sum If Google Sheets function example with wildcards

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

Sum If function Google Sheets with numeric 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. 

Use our capital expenditure approval software to easily automate capex approvals from your spreadsheet in just a few clicks. 

If you want to learn how to send emails based on cell value in Google Sheets, we also suggest checking out our detailed guide.

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!