What it does
SUMIF takes three parts: the range to test, the condition, and the range to add. Excel compares each cell of the first range with the condition and sums the matching rows of the sum range. If you leave out the sum range, the tested range itself is added.
The condition can be a value ("West", 500), a comparison (">=1000", "<>Cancelled") or a pattern with wildcards ("KBL*"). Conditions with a comparison operator go in quotes; to compare with a value in a cell, join the operator with &: ">"&F1.
When you need several conditions (region West and month October), use SUMIFS. To count matching rows use COUNTIF, to average them use AVERAGEIF.
Syntax
=SUMIF(range, criteria, [sum_range])=ETOPLA(aralık; ölçüt; [toplam_aralığı])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
range | Required | The cells to test against the condition, e.g. the region column B2:B9. |
criteria | Required | The condition that decides whether a row is added: "West", ">=1000", "KBL*" or a cell such as F2. It is not case-sensitive. |
sum_range | Optional | The cells to add. It should be the same size as range. If omitted, range itself is added. |
Examples
1Total the sales of one region
The sales list is in A:D. The total for the region in F2 should appear in G2: the tested range is the region column (B), the sum range is the amount column (D).
=SUMIF(B2:B7,F2,D2:D7)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | Region | Total | |
| 2 | 1-Oct-2025 | West | Emma | 12,500.00 | West | 20,350.50 | |
| 3 | 2-Oct-2025 | North | Liam | 8,300.00 | |||
| 4 | 3-Oct-2025 | West | Olivia | 4,750.50 | |||
| 5 | 6-Oct-2025 | South | Noah | 9,900.00 | |||
| 6 | 7-Oct-2025 | West | Emma | 3,100.00 | |||
| 7 | 8-Oct-2025 | North | Ava | 15,200.00 |
West appears in three rows, and SUMIF adds those three amounts: 20,350.50. Type North in F2 and you get North's total.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Add up payments above an amount
Without a sum range, SUMIF adds the tested range itself. E2 adds payments above 10,000; F2 does the same but takes the threshold from a cell (D2).
=SUMIF(B2:B6,">10000")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Payment | Threshold | Over 10,000 | Over threshold | |
| 2 | F-1001 | 4,200.00 | 10000 | 45,150.00 | 45,150.00 | |
| 3 | F-1002 | 18,750.00 | ||||
| 4 | F-1003 | 9,999.90 | ||||
| 5 | F-1004 | 10,000.00 | ||||
| 6 | F-1005 | 26,400.00 |
- E2
=SUMIF(B2:B6,">10000") - F2
=SUMIF(B2:B6,">"&D2)
F-1004 is exactly 10,000, so it is not "greater than"; both formulas return 45,150.00. Use ">=" to include the threshold.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Add up codes that start with certain letters
The condition "KBL*" catches every code starting with KBL, so the total quantity of cable products comes from one formula.
=SUMIF(A2:A6,D2,B2:B6)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Code | Qty | Pattern | Total Qty | |
| 2 | KBL-1525 | 120 | KBL* | 245 | |
| 3 | PRZ-0210 | 25 | |||
| 4 | KBL-1540 | 80 | |||
| 5 | SGT-0016 | 10 | |||
| 6 | KBL-2500 | 45 |
The quantities of the three cable codes are added: 245. Change the pattern to "*0016" to add codes ending in 0016.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Result is 0
Cause: No row met the condition. Often the condition is spelled differently from the list (an extra space, another spelling) or the amounts are stored as text.
Fix: Copy the condition from the list or put it in a cell and refer to that cell; convert amounts stored as text into numbers.
- Missing amounts
Cause: sum_range is shorter than range or shifted (
B2:B9againstC3:C10), so rows are paired up wrongly.Fix: Start and end both ranges on the same rows.
- #VALUE!
Cause: The condition is a text longer than 255 characters, or a range points to a closed workbook.
Fix: Shorten the condition; for ranges in other files, open that file or use SUMPRODUCT.
Good to know
- Put comparison conditions in quotes:
">=1000". A plain number (1000) means equal to. - Wildcards:
*stands for any number of characters,?for exactly one. Use~*to look for a real asterisk. - Building a date condition with DATE avoids regional date formats:
">="&DATE(2025,1,1).
In Nexell
Get Nexell to do it from a sentence
Bring your sales list into the tool and type this. Nexell builds each region's total with conditional sum formulas, so the summary updates in Excel when you add rows or change an amount.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and the sum range comes last. SUMIFS takes several conditions and the sum range comes first: =SUMIFS(D2:D9,B2:B9,"West",C2:C9,"October").
How do I add rows that meet one condition or another?
Add two SUMIFs together: =SUMIF(B2:B9,"West",D2:D9)+SUMIF(B2:B9,"South",D2:D9). As one cell cannot be both values, nothing is counted twice.
Related functions
- SUMIFSÇOKETOPLA · Adds the cells that meet multiple conditions.
- COUNTIFEĞERSAY · Counts cells that meet a condition.
- AVERAGEIFEĞERORTALAMA · Average of the cells that meet a condition.
- SUMPRODUCTTOPLA.ÇARPIM · Multiplies matching items of arrays and sums them; the classic conditional sum: SUMPRODUCT((City="Ankara")*Amount).
- SUBTOTALALTTOPLAM · Subtotal with a chosen function (9 = sum, 1 = average…).