On this page ⌄
SUM adds up everything. Most real questions are more specific than that: revenue for one product, sales in one region, spending in March. SUMIF and SUMIFS answer those questions in a single formula, with no filtering or copying rows around.
The examples use this sales log in A1:F11:
| A: Date | B: Region | C: Rep | D: Product | E: Units | F: Revenue | |
|---|---|---|---|---|---|---|
| 2 | Jan 4, 2026 | North | Ava | Laptop | 3 | $3,600 |
| 3 | Jan 9, 2026 | South | Leo | Monitor | 5 | $1,250 |
| 4 | Jan 15, 2026 | North | Maya | Laptop | 2 | $2,400 |
| 5 | Jan 22, 2026 | East | Sam | Keyboard | 12 | $720 |
| 6 | Feb 3, 2026 | West | Zoe | Monitor | 4 | $1,000 |
| 7 | Feb 11, 2026 | North | Ava | Keyboard | 8 | $480 |
| 8 | Feb 19, 2026 | South | Ivy | Laptop | 4 | $4,800 |
| 9 | Feb 27, 2026 | East | Sam | Monitor | 6 | $1,500 |
| 10 | Mar 6, 2026 | West | Noah | Laptop | 1 | $1,200 |
| 11 | Mar 14, 2026 | North | Maya | Monitor | 7 | $1,750 |
SUMIF: add up with one condition
=SUMIF(range, criteria, [sum_range])| Argument | What it means | Example |
|---|---|---|
| range | The cells to check against the condition | D2:D11 |
| criteria | The condition | "Laptop" |
| sum_range | The cells to add up for the rows that match | F2:F11 |
How much revenue came from laptops?
=SUMIF(D2:D11, "Laptop", F2:F11)→ $12,000SUMIF walks down D2:D11, and every time it finds "Laptop" it adds the revenue from the same row: 3,600 + 2,400 + 4,800 + 1,200.
When the condition is about the numbers you're adding, you can leave out sum_range. Total revenue from orders over $2,000:
=SUMIF(F2:F11, ">2000")→ $10,800Try it yourself. The sheet is live, so you can drag across a column to insert the range.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
What was the total revenue from the North region?
Write your formula in the green cell, I2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
Writing conditions
The condition is the part people get stuck on. Here are the patterns that cover nearly everything:
| You want | Write | Notes |
|---|---|---|
| Equal to some text | "Laptop" | Not case-sensitive |
| Equal to a number | 5 | No quotes needed |
| Greater than a number | ">2000" | The operator goes inside the quotes |
| Not equal to | "<>East" | |
| Equal to a cell | H2 | No quotes around a cell reference |
| Compared with a cell | ">"&H2 | Join the operator to the cell with & |
| Contains some text | "*board*" | * means any characters |
| Blank cells | "" |
For example, revenue from everything that isn't East, and revenue from any product with "board" in its name:
=SUMIF(B2:B11, "<>East", F2:F11)→ $16,480=SUMIF(D2:D11, "*board*", F2:F11)→ $1,200SUMIFS: add up with several conditions
SUMIFS adds a row only when every condition is true. How much laptop revenue came from the North?
=SUMIFS(F2:F11, B2:B11, "North", D2:D11, "Laptop")→ $6,000Notice the order. In SUMIFS, the range you're adding up comes first, followed by pairs of range and condition. You can add up to 127 pairs.
| SUMIF | SUMIFS | |
|---|---|---|
| Conditions | One | One or more |
| Where the sum range goes | Last | First |
| Can you skip the sum range? | Yes | No |
Because SUMIFS also works with a single condition, many people just use SUMIFS all the time so they never have to remember two argument orders:
=SUMIFS(E2:E11, C2:C11, "Sam")→ 18That's the total units Sam sold.
Adding up between two dates
To total a date range, use two conditions on the same date column, one for the start and one for the end. Revenue for February 2026:
=SUMIFS(F2:F11, A2:A11, ">="&DATE(2026,2,1), A2:A11, "<"&DATE(2026,3,1))→ $7,780DATE builds a real date, and & joins it to the operator. Using "before March 1" instead of "on or before February 28" means you never have to think about how many days a month has.
Either-or conditions
SUMIFS only does AND logic: every condition must match. For OR, like East or West, add two SUMIFs together:
=SUMIF(B2:B11, "East", F2:F11) + SUMIF(B2:B11, "West", F2:F11)→ $4,420Common mistakes
| Problem | What's going on | Fix |
|---|---|---|
| Result is 0 | The text doesn't match exactly, often because of extra spaces | Check the data, or use "*North*" to test |
#VALUE! in SUMIFS | The ranges are different sizes | Make every range cover the same rows |
">"H2 doesn't work | The operator and cell weren't joined | Write ">"&H2 |
| Numbers aren't added | They're stored as text | Convert them with VALUE or Text to Columns |
| Wrong argument order | SUMIF puts the sum range last, SUMIFS puts it first | Use SUMIFS everywhere |
Related functions
The same pattern works for counting and averaging. COUNTIF and COUNTIFS count the matching rows, and AVERAGEIF and AVERAGEIFS average them. MAXIFS and MINIFS find the largest and smallest matching values. Once you know SUMIFS, you know all of them.
Key takeaways
- SUMIF adds up the rows that match one condition. SUMIFS handles one or more.
- In SUMIFS the sum range comes first. In SUMIF it comes last.
- Put operators inside quotes, and join them to cells or dates with
&. - For a date range, use two conditions on the same column: on or after the start, and before the day after the end.
Practice
Keep going with SUMIF and SUMIFS exercises
Reading gets you started. Solving a few of these is what makes it stick.
- 1Revenue for one regionFreeWhat was the total revenue from the North region?→
- 2Two conditions at once PremiumWhat was the revenue from Monitors sold in the South region?→
- 3Average for one rep PremiumOn average, how many units did Sam sell per order?→
- 4Count one productFreeHow many orders were for Laptops?→
About the author
Brian Millot
Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.