SUMIFS Function – Google Sheets

The SUMIFS function is used in Google Sheets to add numbers if multiple conditions are true.

SUMIFS checks for the conditions then adds values if those conditions are TRUE. Remember that 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

Video thumbnail
YouTube Video Tutorial about SUMIFS

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 that you are tracking orders of produce at the North warehouse, and you are wondering how many items you will be packing. You will need to fill every order of 12 or more at the North warehouse into a container. 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

The SUMIFS function wants the sum_range first. In this case, they are located at B2:B6. Next is the criterion_range of B2:B6. This range is the same as the sum_range in this case.

We will be checking to see if the order pertains to the North warehouse for the second condition. You can calculate the total Items to pack using the formula =SUMIF(B2:B6,">=12") shown in the image above. Notice the quotes around ">=12". Even though 12 is not a Text value, the SUMIF function requires quotes around the condition. Typically quotes are used in spreadsheet formulas when referring to text. Read more about this inconsistency here.

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

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

Then we look at column B and check to see 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

This formula looks at the range A2:A6 to see which cells contain the word Peaches. Then it checks to see if the cells in B2:B6 have the letter D and any other letters.

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.

Leave a Comment