=SUMIF(A2:A7,"North",C2:C7) adds the sales in C2:C7 on every row where column A says North. SUMIF checks one range against a condition and adds the matching rows of another range.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Region | Total | |
| 2 | North | Apple | 120 | North | 230 | |
| 3 | South | Pear | 45 | South | 245 | |
| 4 | North | Pear | 80 | |||
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 |
F2 adds 120, 80 and 30, the three North rows, and shows 230. F3 takes its region from E3: change E3 to East and it shows 55. Change any number in column C and both totals update.
SUMIF syntax
=SUMIF(range, criteria, [sum_range])
rangeis the cells the condition is checked against.criteriais the condition: text in quotes ("North"), a comparison (">100"), a wildcard pattern ("*apple*"), or a cell (E3, or">"&F2with an operator).sum_rangeis the cells to add. It is optional: without it, SUMIF adds the matching cells ofrangeitself.
range and sum_range are matched row by row, so they should start on the same row and have the same size. If your regional settings use a decimal comma, Excel separates arguments with semicolons: =SUMIF(A2:A7;"North";C2:C7).
SUMIF greater than or less than
To add the numbers above or below a value, the condition and the sum are on the same column, so you can leave out sum_range.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Total | |
| 2 | North | Apple | 120 | Over 100 | 320 | |
| 3 | South | Pear | 45 | 100 or less | 210 | |
| 4 | North | Pear | 80 | Not North | 300 | |
| 5 | East | Apple | 55 | Limit | 50 | |
| 6 | South | Apple | 200 | Over the limit | 455 | |
| 7 | North | Apple | 30 |
F2 adds 120 and 200, F3 adds the other four, and the two together always make the grand total. F4 shows "not equal to": everything except North. F6 reads its limit from F5. Write the operator in quotes and join the cell with &: ">"&F5, never ">F5".
SUMIF if a cell contains text
* in the criterion stands for any number of characters and ? for exactly one. "*apple*" matches any product with apple anywhere in its name, in any case.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Condition | Total | |
| 2 | Apple | 120 | contains apple | 250 | |
| 3 | Pineapple | 45 | exactly Apple | 120 | |
| 4 | Pear | 80 | starts with P | 325 | |
| 5 | Green apple | 55 | word in D6 | 80 | |
| 6 | Plum | 200 | pear | ||
| 7 | Apple cider | 30 |
E2 adds Apple, Pineapple, Green apple and Apple cider. E3, with no wildcards, adds only the cell that is exactly Apple. E5 builds the pattern from the word in D6: change D6 to cider and E5 shows the cider sales alone. More patterns, and ~ for a literal asterisk, are on the wildcards page.
To add the values next to any filled cell, use "<>" (not empty): =SUMIF(A2:A7,"<>",B2:B7). "" adds the rows where the cell is empty.
SUMIF by date
Dates are numbers, so ">=", "<" and the rest work on them. Build the date with DATE or keep it in a cell, and join it to the operator with &.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Sales | Date | Condition | Total | |
| 2 | North | 120 | 2026-01-05 | From Feb 1 | 365 | |
| 3 | South | 45 | 2026-01-12 | Cutoff | 2026-03-01 | |
| 4 | North | 80 | 2026-02-03 | Before the cutoff | 300 | |
| 5 | East | 55 | 2026-02-18 | On Jan 12 | 45 | |
| 6 | South | 200 | 2026-03-02 | |||
| 7 | North | 30 | 2026-03-20 |
F2 adds the four orders from February 1 on: 80, 55, 200 and 30, which is 365. F4 adds the orders before the date in F3. A total for one month needs a start and an end date, two conditions on the same column, which is a job for SUMIFS: =SUMIFS(B2:B7,C2:C7,">="&DATE(2026,2,1),C2:C7,"<"&DATE(2026,3,1)).
SUMIF from another sheet
The ranges can be on another sheet: put the sheet name and ! before them. Below, the data is on the Sales tab and the summary on its own tab. Click the Sales tab to see the rows being added.
| A | B | |
|---|---|---|
| 1 | Region | Total |
| 2 | North | 230 |
| 3 | South | 245 |
| 4 | East | 55 |
B2 is filled down to B4: the criterion A2 moves to A3 and A4, and the $ keeps both ranges on the same rows of the Sales sheet. Without the $, the third copy would read Sales!A4:A9 and miss the first rows. That pattern, a list of labels with one SUMIF beside each, is how you build a summary by category without a pivot table. A sheet name with a space needs quotes: 'Sales 2026'!$A$2:$A$7.
Practice: SUMIF
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Product | Total | |
| 2 | North | Apple | 120 | Apple | ||
| 3 | South | Pear | 45 | |||
| 4 | North | Pear | 80 | |||
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Plum | 30 | |||
| 8 | West | Apple | 65 |
Your turn: Add up the sales of the Apple rows. Write the formula in F2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Amount | Condition | Total | |
| 2 | A-101 | 120 | 100 or more | ||
| 3 | A-102 | 45 | |||
| 4 | A-103 | 310 | |||
| 5 | A-104 | 100 | |||
| 6 | A-105 | 85 | |||
| 7 | A-106 | 140 | |||
| 8 | A-107 | 99 |
Your turn: Add up the orders of 100 or more in B2:B8. Write the formula in E2.
SUMIF with multiple criteria
SUMIF takes one condition. Two cases come up often.
Both conditions must be true (North and Apple): use SUMIFS. Its sum range comes first, then the condition pairs.
=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple")
Either value in one column (North or South): give SUMIF a list in braces and SUM the two results, or add two SUMIFs.
=SUM(SUMIF(A2:A7,{"North","South"},C2:C7))
=SUMIF(A2:A7,"North",C2:C7)+SUMIF(A2:A7,"South",C2:C7)
For a condition on a calculation (the month of a date, sales times price), SUMIF cannot help; SUMPRODUCT can.
Why SUMIF returns 0 or the wrong total
- The criterion never matches. "North " with a trailing space does not equal "North". Test the condition with
=COUNTIF(A2:A7,"North"): if that is 0, the problem is the match, not the sum. - Numbers stored as text in the sum range. SUMIF skips them silently. They usually sit at the left of the cell with a green triangle; convert them to numbers first.
- A cell inside the quotes.
">F5"compares against the text F5. Write">"&F5. - Ranges of different sizes.
=SUMIF(A2:A7,"North",C2)does not return an error: Excel resizessum_rangeto matchrange, starting from its first cell. It works, but a sum range that starts on the wrong row silently adds the wrong cells, so always give both ranges the same rows. - The ranges are in another workbook that is closed. SUMIF shows
#VALUE!until the file is opened.
Frequently Asked Questions
How does SUMIF work in Excel?
=SUMIF(range, criteria, sum_range) checks each cell of range against the criteria and adds the cell in the same row of sum_range when it matches. =SUMIF(A2:A7,"North",C2:C7) totals column C for the North rows.
How do I sum values greater than a number?
Leave out the sum range and put the comparison in quotes: =SUMIF(C2:C7,">100") adds every value in C2:C7 above 100. With the limit in a cell, write =SUMIF(C2:C7,">"&F2).
How do I sum if a cell contains text?
Use asterisks around the text: =SUMIF(B2:B7,"*apple*",C2:C7) adds column C wherever column B contains apple, such as Apple, Pineapple or Green apple. The match ignores case.
How do I use SUMIF with multiple criteria?
For conditions that must all be true, use SUMIFS: =SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple"). For either of two values in one column, add the results of a list: =SUM(SUMIF(A2:A7,{"North","South"},C2:C7)).
Why does my SUMIF return 0?
Usually the criterion never matches (a trailing space in the data, or ">F2" written instead of ">"&F2), or the numbers in the sum range are stored as text, which SUMIF skips. Check the match with COUNTIF using the same range and criteria.
