SUMIFS Function – Google Sheets

Adam Steinfurth

The SUMIFS function adds numbers if multiple conditions are true.

All conditions must be met for the function to sum a value, and all ranges must be the same size. You can use SUMIF to check for one condition.

Feel free to copy the template with these examples to follow along.

Purpose

This function returns the total of a series of numbers, cells, or both that meet specific conditions.

Syntax

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

  • sum_range – The range for the function to sum.
  • criteria_range – The function’s range to check against the criterion.
  • criterion – The condition that must be TRUE to sum.

IF – Returns a value based on a condition.

SUM – Add numbers regardless of their value

SUMIF – Adds numbers based on a single criterion

Video Tutorial

YouTube player

Common Errors

#VALUE! – The ranges are not the same size.

#REF! – One cause is a circular dependency created by overlapping a range with the function’s cell.

Examples

Example 1 – SUMIFS Values Greater Than and Matching a Word

Let’s say you are tracking produce orders at the North warehouse and wondering how many items you will be packing. You will need to fill every order of 12 or more at the North warehouse. We used greater than 11 in the formula instead of greater than or equal to 12 because it keeps the formula shorter.

Adding if greater than and matches word
Summing Items More Than 11 From The North Warehouse

Formula used =SUMIFS(B2:B6,B2:B6,">11",C2:C6,"North")

The SUMIFS function wants the sum_range of B2:B6 first. Next is the criterion_range of B2:B6. The criterion_range is the same as the sum_range in this case.

Notice the quotes around ">11". Even though 11 is not a text value, the SUMIFS function requires quotes around the condition. Typically quotes are used in spreadsheet formulas when referring to text. Read more about this inconsistency here.

We will check if the order pertains to the North warehouse for the second condition. You can check for this by looking in the criterion_range2 of C2:C6 for the string “North.”

How would we do this if we wanted to know how many peaches our customers ordered?

Example 2 – SUMIFS Values Before a Date with a Matching Word

Now let’s find the total number of preaches sold before 2/5/2022.

SUMIFS if a date and word meet conditions
Summing if Peaches are Before a Date

Formula used =SUMIFS(C2:C6,A2:A6,"<"&"2/5/2022",B2:B6,"Peach")

Using dates and times can be tricky, but this function makes it even more complex with the formula’s required use of the & when using a date. After joining the < operator with the date using the &, the first criteria will compare the dates in the first criteria_range to the criterion before February 2, 2022.

Then we look at column B and check which cells have the word “Peach“. Remember the quotes around Peach since it is text.

Example 3 – SUMIFS Using Partial Matches

Lastly, we will use an example showing how to use wildcards in SUMIFS.

Summing values if there is a partial match AND another criteria
Summing any Peaches from Lot D

Formula used =SUMIFS(C2:C6,A2:A6,"Peach",B2:B6,"*D")

This formula looks at the range A2:A6 to see which cells contain the word Peach. Then it checks to see if the cells in B2:B6 have the letter D and any other letters. The * is a wildcard that can represent any character or multiple characters. The other two wildcards available are ?, which is one of any character, and ~ which escapes the * and the ? if you want to use them as non-wildcards.

Live Template

See this template for a spreadsheet with the examples used in this blog. You can make your own copy and use them in your work.