How to Write a SUMIF Formula in Excel (2026 Guide)

SUMIF is the formula for "add up only the rows that match." Total sales for one region, expenses for one category, hours for one project — if you have ever filtered a column and added the visible numbers by hand, SUMIF does it in one cell, and it updates itself when the data changes.

The syntax, in plain English

=SUMIF(range, criteria, sum_range)

• range — the column you check (for example, the Status column).
• criteria — what counts as a match (for example, "Approved").
• sum_range — the numbers to add up (for example, the Amount column).

When the range and the numbers are the same column, you can leave the third part off: =SUMIF(B2:B100,">1000") adds up every number in B2:B100 that is over 1,000.

Example 1: Add up sales for one region

Column A holds the region, column B holds the amount. Total for the East region:

=SUMIF(A2:A100,"East",B2:B100)

Text criteria go in double quotes. Numbers and cell references do not: =SUMIF(A2:A100,D1,B2:B100) uses whatever is in D1 as the criteria, so you can change the region in D1 and watch the total update.

Example 2: Add up numbers over a threshold

Total of all orders over $1,000:

=SUMIF(B2:B100,">1000")

Comparison operators (>, <, >=, <=, <>) go inside the quotes, glued to the number. To compare against a cell instead of a fixed number, join them with &:

=SUMIF(B2:B100,">"&D1)

Example 3: Match part of a text value with wildcards

Total for every product whose name contains "shirt":

=SUMIF(A2:A100,"*shirt*",B2:B100)

* stands for any characters, so "*shirt*" matches "T-shirt", "Shirt Dress", and "shirt" on its own. ? stands for exactly one character. Note that SUMIF is not case-sensitive — "shirt" and "Shirt" both match.

Example 4: Add up by month with dates

Total for all rows dated in January 2026:

=SUMIF(A2:A100,">=2026-01-01",B2:B100)-SUMIF(A2:A100,">2026-01-31",B2:B100)

That subtracts everything after January from everything from January onward. It works, but it is clumsy — two conditions is exactly what SUMIFS is for.

Example 5: Two conditions with SUMIFS

Total for East-region orders that are also marked Approved:

=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Approved")

Watch the order: in SUMIFS the sum range comes first, then pairs of range-and-criteria. That is the opposite of SUMIF, and mixing the order up is the most common SUMIFS mistake. You can add as many condition pairs as you need.

The 4 mistakes that break SUMIF

• Trailing spaces. "East " with a space at the end does not match "East". If your total is 0 for no visible reason, check for spaces with =LEN(A2) — a 5-character "East" has a hidden space.
• Numbers stored as text. If the amount column was pasted from a PDF or a website, the "numbers" may be text. SUMIF skips text, so the total comes out 0. Convert with Data → Text to Columns → Finish, or multiply the column by 1.
• Ranges of different sizes. The criteria range and the sum range should cover the same number of rows. =SUMIF(A2:A100,"East",B2:B50) silently adds up the wrong rows.
• SUMIF vs SUMIFS order. SUMIF is range, criteria, sum_range. SUMIFS is sum_range, range, criteria. Same function family, opposite order.

FAQ

What is the difference between SUMIF and SUMIFS?

SUMIF handles one condition; SUMIFS handles one or more. SUMIF puts the sum range last, SUMIFS puts it first — that ordering difference is the most common source of errors when switching between them.

Why does my SUMIF return 0?

Usually one of three things: the criteria text does not exactly match (extra spaces are the usual culprit), the numbers are stored as text, or the criteria range and sum range are different sizes.

Can SUMIF use a cell reference as the criteria?

Yes — put the cell reference in without quotes, for example =SUMIF(A2:A100,D1,B2:B100). Change D1 and the total updates. For a comparison like greater-than with a cell, use =SUMIF(B2:B100,">"&D1).

Does SUMIF work in Google Sheets?

Yes — SUMIF and SUMIFS work the same way in Google Sheets and in Excel, with the same syntax.