SUMIFS adds up the cells in one range only when every condition you set is met, for example sales of one product by one salesperson. Type =SUMIFS(sum_range, criteria_range1, criteria1, ...), and remember that the range you're adding comes first, which is the opposite of SUMIF.
Microsoft files SUMIFS under math and trig functions and describes it as adding all of its arguments that meet multiple criteria. Its own example of a use is summing the number of retailers in the country who live in a single zip code and whose profits exceed a set value. That's two conditions at once, and that's exactly where SUMIFS earns its place.
SUMIFS syntax, slot by slot
The full syntax is SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The first three arguments are required; every pair after that is optional. You can enter up to 127 range and criteria pairs.
| Argument | What goes in it |
|---|---|
| sum_range | The cells you want to add up |
| criteria_range1 | The first range Excel searches |
| criteria1 | What it searches that range for |
| criteria_range2, criteria2 and so on | Extra pairs, up to 127 in total |
Each criteria_range and its criteria form a search pair. Excel searches the range for the criteria, and when a row matches every pair, it adds that row's value from sum_range.
Criteria can take several forms. Microsoft lists 32, ">32", B4, "apples" and "32" as valid. Comparisons and text go in straight double quotes; a cell reference doesn't need them.
The argument-order trap that breaks copied formulas
Microsoft flags this one itself: the order of arguments differs between SUMIFS and SUMIF. In SUMIFS, sum_range is the first argument. In SUMIF, it's the third. If you copy a SUMIF formula and edit it into a SUMIFS, or the other way round, and you don't move sum_range, Excel will happily add up the wrong column.
| SUMIF | SUMIFS | |
|---|---|---|
| Criteria allowed | One | Up to 127 pairs |
| Argument order | range, criteria, sum_range | sum_range, criteria_range1, criteria1, ... |
| sum_range position | Third (optional) | First (required) |
| Range sizes | sum_range same shape as range | Every criteria_range same size as sum_range |
If you've only got one condition, SUMIF is fine, and my SUMIF guide covers it in detail. In SUMIF, if you leave out sum_range, Excel adds the cells in range itself. Microsoft's SUMIF example sums commission on property values: =SUMIF(A2:A5,">160000",B2:B5) returns $63,000 of commission for property values over $160,000 (Microsoft's example figures, as of October 2026). Swap the threshold for a cell with =SUMIF(A2:A5,">" & C2,B2:B5), where the ampersand joins the operator to the cell.
The same-size rule
Every criteria_range in SUMIFS must have the same number of rows and columns as sum_range. SUMIF is looser, and that's not a good thing: Microsoft says that if sum_range isn't the same size and shape as range, performance may suffer, and Excel sums a range that starts at the first cell of sum_range with the dimensions of range. In other words, it may not add the cells you think it's adding.
Microsoft's SUMIFS example, worked through
Microsoft's sample data is a small sales table: quantity sold in column A, product in column B and salesperson in column C, in rows 2 to 9.
| Quantity Sold | Product | Salesperson |
|---|---|---|
| 5 | Apples | Tom |
| 4 | Apples | Sarah |
| 15 | Artichokes | Tom |
| 3 | Artichokes | Sarah |
| 22 | Bananas | Tom |
| 12 | Bananas | Sarah |
| 10 | Carrots | Tom |
| 33 | Carrots | Sarah |
Two formulas from Microsoft's page show the idea:
=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom")adds the quantity of products starting with A that Tom sold. The asterisk is a wildcard.=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, "Tom")adds Tom's sales of everything that isn't bananas. The<>means not equal to.
Read each one left to right and it almost speaks: add column A, where column B starts with A, and column C is Tom.
Wildcards in SUMIFS criteria
| Character | Matches | Example |
|---|---|---|
? | Any single character | "To?" matches names that begin with To plus one more letter |
* | Any sequence of characters | "=A*" matches anything that starts with A |
~ | Lets you search for a real ? or * | Type a tilde in front of the character |
Microsoft's wildcard example is =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "To?"), which adds sales for names beginning with To and ending in one varying letter.
Why SUMIFS returns 0 or a strange total
Microsoft lists two common problems, and they're the two I see most:
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
- A 0 instead of the result you expected. If you're testing for text, such as a person's name, put the criteria in quotation marks.
- An odd total when sum_range holds TRUE and FALSE. TRUE counts as 1 and FALSE as 0, which gives totals you didn't intend.
One more from the SUMIF page that applies to conditional sums generally: SUMIF returns incorrect results for strings longer than 255 characters. If you're matching long text, test it on a small sample first.
How to write a SUMIFS formula
- Click where the total should appear and type
=SUMIFS( - Select the range you want to add (sum_range) and type a comma.
- Select the first range to test, a comma, then its criterion. Text and comparisons such as
">20"go in quotes. - Repeat range, comma, criterion for each extra condition.
- Close the bracket and press Enter.
If you need to count rows that meet several conditions rather than add them up, the logic carries straight over. Start with my COUNTIF walkthrough and add pairs the same way.
SUMIFS in Google Sheets
Google Sheets uses the same order, with sum_range first: SUMIFS(sum_range, criteria_range1, criterion1, ...). Google's examples are =SUMIFS(A1:A10, B1:B10, ">20") and =SUMIFS(A1:A10, B1:B10, ">20", C1:C10, "<30"). Google spells out the rule that makes SUMIFS work: all criteria must be met in the same row for that row to be summed.
My verdict
Reach for SUMIFS the moment you've got two conditions: rep and product, month and region. I'd go further and use it even for one condition, because its argument order never changes as you add more, so you'll never have to rebuild a formula. Put sum_range first, wrap text criteria in quotes, and keep every range the same size.
Want the details on the other half? Try the guide on how to calculate days between dates in Excel.
Frequently asked questions
What's the difference between SUMIF and SUMIFS?
SUMIF handles one condition and puts sum_range third. SUMIFS handles up to 127 condition pairs and puts sum_range first.
Why is my SUMIFS returning 0?
Microsoft says to put the criteria in quotation marks when you're testing for text, such as a person's name.
Can I use wildcards in SUMIFS?
Yes. A question mark matches one character, an asterisk matches any sequence, and a tilde lets you search for a real ? or *.
Do the ranges in SUMIFS have to be the same size?
Yes. Every criteria_range must have the same number of rows and columns as sum_range.
Does SUMIFS work the same way in Google Sheets?
Yes. sum_range comes first, and all criteria must be met in the same row for it to be summed.
