SUMIF vs SUMIFS, How to Sum With One or More Conditions

Most totals in an office spreadsheet are not "add up the whole column." They are "add up October," "add up what Ana sold," or "add up online orders from Ana in October." Excel and Google Sheets both have two functions for this, SUMIF and SUMIFS, and the one detail that trips people up is that they take their arguments in a different order.

What each function does

Microsoft's documentation describes SUMIFS as adding all of its arguments that meet multiple criteria. Its syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

You name the column to add first, then one or more pairs: a range to test and the condition to test it against. Microsoft notes you can enter up to 127 range and criteria pairs. Google Sheets uses the same structure, and its help page adds a short but important note: to be added, a row must meet all the criteria.

SUMIF handles a single condition, and Microsoft's page flags the trap directly: the sum range is the first argument in SUMIFS but the third argument in SUMIF. Microsoft calls this a common source of problems, especially when you copy one formula and edit it into the other.

Microsoft's example, rebuilt

Microsoft's SUMIFS page uses this table in A1:C9:

Quantity Sold (A)	Product (B)	Salesperson (C)
5	Apples	Tom
4	Apples	Sarah
15	Artichokes	Tom
3	Artichokes	Sarah
22	Bananas	Tom
12	Bananas	Sarah
10	Carrots	Tom
33	Carrots	Sarah

Its two documented formulas both return the numbers Microsoft gives when entered in Excel:

=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom")
=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, "Tom")

The first returns 20: products starting with A (the asterisk is a wildcard for any characters) sold by Tom, which is 5 apples plus 15 artichokes. The second returns 30: everything Tom sold except bananas, 5 plus 15 plus 10.

Now compare a single-condition total, everything Tom sold, written both ways:

=SUMIF(C2:C9,"Tom",A2:A9)
=SUMIFS(A2:A9,C2:C9,"Tom")

Both return 52 (5 + 15 + 22 + 10). Same answer, different order. If you only remember one function, SUMIFS works for one condition too, which removes the need to remember two argument orders.

Summing a date range

The most useful everyday pattern is a total between two dates. Suppose a small orders table has dates in J2:J7, the sales rep in K2:K7, the channel in L2:L7 and the amount in M2:M7:

Date	Rep	Channel	Amount
Sep 28, 2026	Ana	Online	1,200
Sep 30, 2026	Ben	Store	800
Oct 1, 2026	Ana	Store	450
Oct 3, 2026	Ana	Online	975
Oct 6, 2026	Ben	Online	620
Oct 9, 2026	Ana	Online	300

Put the start date in O1 (October 1, 2026) and the end date in O2 (October 31, 2026). Then:

=SUMIFS(M2:M7,J2:J7,">="&O1,J2:J7,"<="&O2)

This returns 2,345, the four October orders. Notice that the same date column appears twice, once for each boundary. That is allowed, and it is how you build a "between" condition.

The ">="&O1 part matters. The ampersand joins the comparison operator to the value in O1. If you type ">=O1" inside the quotes instead, the formula looks for the literal text "O1" and returns 0, with no error to warn you.

Add more pairs to narrow it down. Put "Ana" in O3:

=SUMIFS(M2:M7,J2:J7,">="&O1,J2:J7,"<="&O2,K2:K7,O3)
=SUMIFS(M2:M7,J2:J7,">="&O1,J2:J7,"<="&O2,K2:K7,O3,L2:L7,"Online")

The first returns 1,725 (Ana's October orders: 450 + 975 + 300). The second returns 1,275 (only her online ones: 975 + 300). Keeping the dates and the rep's name in cells, rather than typing them into the formula, lets you change the report by editing one cell.

Three problems Microsoft lists, and what they look like

A total of 0 when you expected a number. Microsoft says to check that text criteria are in quotation marks. Writing =SUMIFS(A2:A9,C2:C9,Tom) without quotes returns 0 in Excel instead of 52.

Ranges of different sizes. Microsoft states the criteria range must contain the same number of rows and columns as the sum range. A formula like =SUMIFS(A2:A9,B2:B8,"Apples"), where one range stops a row early, returns #VALUE!.

TRUE and FALSE in the sum column. Microsoft notes that TRUE counts as 1 and FALSE as 0 when they appear in the sum range, which can produce unexpected totals if a column mixes checkboxes and numbers.

Comparison operators you can use

Criteria can be a number, a cell reference, text, or a comparison in quotes. Microsoft's examples include 32, ">32", B4, "apples" and "32". For example, =SUMIFS(A2:A9,A2:A9,">10") adds only quantities above 10 in the first table, which gives 82 (15 + 22 + 12 + 33). The question mark wildcard matches exactly one character, so "To?" matches "Tom".

Key takeaways

• SUMIFS puts the sum range first; SUMIF puts it third. Mixing them up is the most common mistake.
• SUMIFS also handles a single condition, so it can be the only one you use.
• A row is added only if it meets every condition.
• For a date range, test the same date column twice and join operators to cells with &, as in ">="&O1.
• Unquoted text criteria give 0, and mismatched range sizes give #VALUE!.