SUMIF in Excel
Last updated
SUMIF adds up the numbers in a range that meet one condition. For example, =SUMIF(A2:A11,"North",D2:D11) adds the amounts in D2:D11 on every row where column A says North. Put text and comparisons like ">1000" in double quotes.
SUMIF syntax
=SUMIF(range, criteria, [sum_range])
- range
- The cells to test against the condition, such as a column of regions. Select it from the first data row, without the header.
- criteria
- The condition: a value (
"North"), a comparison (">1000"), a cell reference (F2) or a wildcard pattern ("Laptop*"). Text and comparisons go in double quotes. - sum_range
- Optional. The cells to add, e.g.
D2:D11. It should have the same size as range. Leave it out to add the cells of range itself.
Practice SUMIF here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Total sales for one region
In F2, write a SUMIF that adds the amounts in column D for the North region, using the orders in A1:D11.
| Region | Salesperson | Product | Amount ($) | North total ($) | |
| South | James Carter | Monitor 24" | 420.00 | ||
| East | Sofia Patel | Laptop 15" | 1,480.00 | ||
| West | Emma Hughes | Monitor 27" | 650.00 | ||
| North | Olivia Reed | Laptop 14" | 980.00 | ||
| North | Liam Brooks | Keyboard | 180.00 | ||
| South | Noah Bennett | Laptop 14" | 1,000.00 | ||
| East | Ava Collins | Headset | 240.00 | ||
| North | Olivia Reed | Monitor 27" | 1,150.00 | ||
| West | James Carter | Laptop 15" | 1,520.00 | ||
| South | Sofia Patel | Keyboard | 95.00 |
How to use SUMIF, step by step
- Click the cell where you want the total and type
=SUMIF(. - Select the column to test (for example the regions), then type a comma.
- Type the condition in double quotes, such as
"North", or click a cell that holds it. Type a comma. - Select the column of amounts to add, with the same rows as the first range, close the bracket and press Enter.
SUMIF examples
Total sales for one region
=SUMIF(A2:A11,"North",D2:D11)
Adds the amounts in column D on every row where column A is North. The match ignores upper and lower case.
Add only large orders
=SUMIF(D2:D11,">1000")
Adds every amount greater than 1,000. With no sum_range, SUMIF adds the cells it tests. Use ">=1000" to include 1,000 itself.
Take the condition from a cell
=SUMIF($A$2:$A$11,F2,$D$2:$D$11)
Type the region in F2 and the total updates. With the ranges locked by $, you can copy the formula down a summary table.
Match part of a text with a wildcard
=SUMIF(C2:C11,"Laptop*",D2:D11)
The asterisk stands for any characters, so this adds every product that starts with Laptop. A question mark stands for exactly one character.
Common SUMIF problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| 0 instead of a total | No cell matches the condition: a typo, an extra space in the data, or a number stored as text. It also happens when a cell reference is typed inside the quotes, like ">=F2". | Check the spelling, clean the data with TRIM, and join a comparison to a cell with &: ">="&F2. |
| Wrong total, no error | range and sum_range don’t start on the same row, so the rows don’t line up. | Select both ranges from the same first row to the same last row, e.g. A2:A11 and D2:D11. |
| Wrong totals after copying | The ranges moved down one row each time the formula was copied. | Lock both ranges with $ (F4) and leave only the criteria cell relative. |
| #VALUE! | The ranges point to another workbook that is closed, or the text criteria is longer than 255 characters. | Open the other workbook, or shorten the criteria, for example with a wildcard. |
SUMIF vs SUMIFS
SUMIFS adds with several conditions at once, such as region and product. Watch the argument order: in SUMIF the range to add comes last, =SUMIF(A2:A11,"North",D2:D11), while in SUMIFS it comes first, =SUMIFS(D2:D11,A2:A11,"North",C2:C11,"Laptop*"). SUMIFS also works with a single condition, so some people use it everywhere to keep one habit.
Frequently asked questions
Why does my SUMIF return 0?
Nothing matches the condition. Look for typos, extra spaces at the end of cells and numbers stored as text. Also check that a comparison with a cell is written ">="&F2 and not ">=F2".
Is SUMIF case-sensitive?
No. "north", "North" and "NORTH" all match the same cells.
How do I use SUMIF with a date or a number in a cell?
Join the operator and the cell with &: =SUMIF(A2:A11,">="&F2,D2:D11). Inside quotes, F2 would be read as the text F2.
Can SUMIF use more than one condition?
No, SUMIF takes one condition. For two or more, use SUMIFS, which puts the range to add first.
Keep practising
All 10 SUMIF exercises, with explanations and progress tracking.
Open the SUMIF practice set