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.

Contents

## 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.

## Related Functions

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

## 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.

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`

.

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.

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.