How to Use SUMIF in Google Sheets

You can use the SUMIF function in Google Sheets to add up numbers across a range of cells if they meet the conditions. You might use this to summarize data in a category, such as adding up movie box office sales since 2015.

By the end of this tutorial, you’ll be able to sum up data that fits your stated criteria, such as a name, category, or dollar value, within a data set.

SUMIF formula

The syntax for SUMIF is:

=SUMIF(range, criterion, [sum_range])  

Within this formula, you’ll have the following components:

• range: The range of cells to be evaluated by the criterion.

• criterion: The condition to be met.

• sum_range: (Optional) The range used to add up numbers. If this is omitted, then the first range is summed.

How to use SUMIF

Ready to deepen your knowledge of Google Sheets?

To begin, you'll need your tab open to your spreadsheet. If you’re not already working with your own data set and want to follow along with our examples, make a copy of this template to practice. 

Once you have decided what information you’d like to sum up in this data set, click on any blank cell in the sheet to input the SUMIF formula. 

1. Select the range of cells to be summed up.

Use your mouse to drag and highlight (or type out) the alphanumeric range of cells that you’d like to sum up based on your criterion.

In our example, let’s say you want to know the total earnings of films directed by Steven Spielberg. Our range would be the column where Sheets will find “Steven Spielberg.”

Your inputs would look like:

• Range: The list of items that your criterion will be based on. In our practice sheet, that range would be C2:C47, or the list of directors.

• Criterion: The cell or criteria you are evaluating. In this case, you’ll type in “Steven Spielberg” (with the quotation marks).

• Sum range: The range of cells to be summed up. In the example, that would be F2:F47, the entire list of box office earnings.

Each of the above inputs is separated by commas and sits between two parentheses.

2. Add the criterion.

Next, you’ll add the criterion to your formula. This can either be a number, text, or another cell. There are multiple options for SUMIF criteria, which are listed below. 

Using the practice example, add a comma and then "Steven Spielberg" to your formula.

3. Highlight the range of cells to be added up based on the criterion.

Drag (or type out) the alphanumeric range you’d like to sum up using your chosen criteria. This second range should be the range of cells containing amounts to be summed up. 

This step is optional and only necessary if your sum depends on data in a non-sum column.

In the practice example, add a second comma. Then highlight the numbers (box office earnings) in column F to add F2:F47. At this point, your formula should look like this: 

=SUMIF(C2:C47, "Steven Spielberg", F2:F47)

4. Press enter to sum up.

Close the parentheses to indicate a complete formula and press enter. Your sum will appear in the cell.

SUMIF vs. SUMIFS: What’s the difference?

Both SUMIF and SUMIFS functions add up a range of values based on criteria. The main difference is that SUMIF evaluates based on one condition at a time, while SUMIFS can check for multiple criteria. In the SUMIF formula, sum_range is the final (and optional) argument but when using SUMIFS, sum_range is the first (and required) argument for SUMIFS.

SUMIF troubleshooting

Errors to look out for when using the SUMIF function:

• The #VALUE! error can occur if the SUMIF function refers to a cell or range in a closed workbook.

• The SUMIF formula can return incorrect values if you try to match strings that are longer than 255 characters.

• You may only use equal-sized ranges and sum_range in your formula. For example, if your range is C2:C47, your sum_range should reflect F2:F47 rather than F2:F100.

SUMIF limitations

When using the SUMIF function, these are some limitations and solutions to keep in mind.

• SUMIF cannot differentiate between uppercase and lowercase characters. If you have “steven spielberg” on your spreadsheet, SUMIF will still pick it up.

• SUMIF can return the sum of values “not equal to” the #N/A error, but cannot pick up the #DIV/0! error.

More SUMIF criterion

There are several criteria to choose from based on what information you’re looking for. 

Sum that contains text

Add up numbers with specific text in another column in the same row.

To add up box office earnings from Steven Spielberg’s films:

=SUMIF(C2:C47, "Steven Spielberg", F2:F47)

Sum based on conditions

Add up values based on the condition that they are greater than (>), greater than or equal to (>=), less than (<), or less than or equal to (<=) than a certain number. The sum_range argument is not needed in this case.

To add up numbers between F2:F47 that are greater than $10 trillion:

=SUMIF(F2:F47, ">$10,000,000,000")

Sum if equal to or not equal to

Add numbers in a column if the criteria is equal to (or all except for) a specific text, number, or cell.

=SUMIF(C2:C47, "<>Steven Spielberg", F2:F47)

Sum if based on blank or non-blank cells

You can add up numbers in a column based on whether they are blank or not. 

“=” to sum cells that are completely blank

“” to sum blank cells including those that contain zero-length strings

“<>” to sum cells that contain any value, including zero-length strings

=SUMIF(D2:D47, "=”, F2:F47)

Sum if multiple criteria

You have two options for summing with multiple criteria. You can add two or more SUMIF functions in the same formula. For example, you can sum up the box office earnings for movies directed by Steven Spielberg and Tony Scott with this formula:

=SUMIF(C2:C47, "Steven Spielberg", F2:F47)+SUMIF(C2:C47, "Tony Scott",F2:F47)

You can also sum for multiple criteria with the SUMIFS function. The syntax is:

=SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], ...)

Here, you will use the sum_range as a base with multiple criteria which will be added together.

Learn more with Google

Interested in strengthening your abilities to work with data using Google Sheets? Enroll in the Google Data Analytics Professional Certificate. You’ll learn more about spreadsheets and other key analysis tools.