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.
This function returns the total of a series of numbers, cells, or both that meet specific conditions.
=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
#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.
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.
The SUMIFS function wants the
B2:B6 first. Next is 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
C2:C6 for the string “
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
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
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.
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.
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.