The SUMIF function in Excel
SUMIF adds the numbers in one column for the rows that match a condition in another, like total travel expenses or revenue for one client. It's the quickest way to build a subtotal by category without a pivot table, and a frequent first step in finance and operations tests.
Syntax
=SUMIF(range, criteria, [sum_range])
| range | The cells to test against the condition, like a category column. |
|---|---|
| criteria | The condition, like "Travel", ">100", or "<>"&F2. |
| sum_range | Optional. The cells to add. If left out, SUMIF adds the cells in range itself, which is useful for conditions on the numbers themselves. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In F2, use SUMIF to total the Amount of all invoices whose status is Unpaid.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Status | Unpaid total | |||||
| 2 | Paid | ||||||
| 3 | Unpaid | ||||||
| 4 | Unpaid | ||||||
| 5 | Paid | ||||||
| 6 | Paid | ||||||
| 7 | Unpaid | ||||||
| 8 | |||||||
| 9 | |||||||
| 10 |
Tip: Test the Status column, and add up the Amount column.
Examples
=SUMIF(B2:B8,"Travel",C2:C8)→ 915Totals every expense claim in the Travel category.
=SUMIF(A2:A8,"Priya Nair",C2:C8)→ 607Same idea with a different column as the test: everything Priya claimed.
=SUMIF(C2:C8,">100")→ 1044No sum_range here, so SUMIF adds the amounts that are themselves over 100.
Example data
| 1 | Employee | Category | Amount |
|---|---|---|---|
| 2 | Priya Nair | Travel | 420 |
| 3 | Tom Avery | Meals | 64 |
| 4 | Priya Nair | Software | 129 |
| 5 | Lena Fox | Travel | 310 |
| 6 | Tom Avery | Travel | 185 |
| 7 | Lena Fox | Meals | 47 |
| 8 | Priya Nair | Meals | 58 |
Common mistakes
Mixing up the order with SUMIFS
In SUMIF the cells to add come last. In SUMIFS they come first. If your total looks wrong after switching functions, check the argument order.
Ranges that don't line up
SUMIF(B2:B8,"Travel",C3:C9)doesn't error, it just adds the row below each match. Start both ranges on the same row.Leaving out sum_range by accident
Without the third argument, SUMIF tries to add the category column, which is text, so you get 0. Add the amount column as
sum_range.
FAQ
What's the difference between SUMIF and SUMIFS?+
SUMIF handles one condition. SUMIFS handles one or more, and puts the sum range first. Many people use SUMIFS for everything so they only remember one argument order.
Can SUMIF use multiple criteria?+
Not in one formula. Use SUMIFS for AND logic, or add two SUMIFs together for OR logic.
How do I sum if a cell contains certain text?+
Use wildcards: =SUMIF(A2:A50,"*north*",C2:C50) adds rows where column A contains "north" anywhere.
Lesson
Master SUMIF step by step: SUMIF
Add up only the numbers that meet a condition, with and without a separate sum range.