SUMIF adds the values in a range only when they meet a condition you specify. It lets you total sales for one region, hours for one project, or expenses in one category without sorting or filtering.
=SUMIF(range, criteria, [sum_range])What does SUMIF do?
SUMIF is Excel's conditional adding tool. Instead of summing every number in a column, you give it a rule, and it adds only the rows that match. The rule can be a region name, a product code, a date, or a number comparison such as greater than 500.
This matters because most real spreadsheets mix categories together. You often need one total for a single customer, team, or month while the full list stays intact. SUMIF answers that question instantly and updates when the data changes, so you avoid manual filtering and re-adding.
Imagine a monthly sales sheet with hundreds of rows across four regions. Your manager asks for the North region total. With SUMIF you point at the region column, type "North", and point at the sales column. The answer appears in one cell, and you can copy the formula for other regions.
When to use SUMIF
- Total sales for a single region, product, or salesperson.
- Add hours logged against one project code.
- Sum expenses in a specific category, such as travel or software.
- Calculate amounts due from one customer.
- Total invoices dated on or after a given date.
- Add scores above a pass mark in a student list.
SUMIF syntax
=SUMIF(range, criteria, [sum_range])| Argument | Required? | What to enter |
|---|---|---|
range | Required | The cells Excel checks against your condition. Example: A2:A10 if column A holds region names. |
criteria | Required | The condition a cell must meet. It can be text like "North", a number like 500, or a comparison like ">=500". Text and operators go in quotes. |
sum_range | Optional | The cells to add when the condition is met. If omitted, Excel adds the cells in range. Example: B2:B10 for sales amounts. |
What it returns: A single number: the total of all cells in sum_range whose corresponding cell in range meets the criteria. If no cells match, it returns 0.
SUMIF examples
1 Total sales for one region
The goal is to add all sales for the North region. SUMIF checks each cell in A2:A7 for the text "North". For every match, it adds the value in the same row of B2:B7. The North rows hold 1200, 900 and 1500, so the formula returns 3600.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 3600 | ||
| 3 | South | 800 | |||
| 4 | North | 900 | |||
| 5 | East | 1100 | |||
| 6 | North | 1500 | |||
| 7 | West | 700 | |||
| 8 |
2 Add amounts above a threshold
Here you want the total of orders worth more than 500. The criteria ">500" tells SUMIF to compare each value in B2:B8 with 500. Only rows 2, 4, 6 and 8 qualify. Their amounts are 750, 620, 900 and 540, which add to 2810.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Amount | |||
| 2 | 1001 | 750 | 2810 | ||
| 3 | 1002 | 300 | |||
| 4 | 1003 | 620 | |||
| 5 | 1004 | 450 | |||
| 6 | 1005 | 900 | |||
| 7 | 1006 | 200 | |||
| 8 | 1007 | 540 | |||
| 9 |
3 Total expenses for one category
You need the total spent on Travel. SUMIF scans A2:A9 for "Travel" and adds the matching amounts from C2:C9. The Travel rows contain 250, 180 and 320, giving 750. Other categories are ignored even though they sit in the same list.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Description | Amount | |||
| 2 | Travel | Train ticket | 250 | 750 | ||
| 3 | Software | License | 400 | |||
| 4 | Travel | Hotel | 180 | |||
| 5 | Office | Chairs | 600 | |||
| 6 | Travel | Taxi | 320 | |||
| 7 | Software | Support | 150 | |||
| 8 | Office | Printer | 220 | |||
| 9 | Software | Upgrade | 300 | |||
| 10 |
4 Sum invoices from a date onward
The aim is to total invoice amounts dated on or after 1 March 2024. The criteria ">="&DATE(2024,3,1) builds the comparison. SUMIF checks each date in B2:B8 and adds the matching amounts from C2:C8. Rows dated 2024-03-01, 2024-03-15 and 2024-04-02 qualify, with amounts 500, 700 and 650, so the result is 1850.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Date | Amount | |||
| 2 | INV-01 | 2024-01-15 | 400 | 1850 | ||
| 3 | INV-02 | 2024-02-10 | 300 | |||
| 4 | INV-03 | 2024-03-01 | 500 | |||
| 5 | INV-04 | 2024-02-28 | 200 | |||
| 6 | INV-05 | 2024-03-15 | 700 | |||
| 7 | INV-06 | 2024-04-02 | 650 | |||
| 8 | INV-07 | 2024-01-30 | 450 | |||
| 9 |
5 Total hours for one project code
A timesheet lists hours by employee and project. You want all hours booked to project P-100. SUMIF looks for "P-100" in C2:C9 and adds the hours from D2:D9. The matching rows hold 7.5, 6, 8 and 4 hours, which total 25.5.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Day | Project | Hours | |||
| 2 | Ana | Mon | P-100 | 7.5 | 25.5 | ||
| 3 | Ben | Mon | P-200 | 8 | |||
| 4 | Ana | Tue | P-100 | 6 | |||
| 5 | Cara | Tue | P-300 | 5 | |||
| 6 | Ben | Wed | P-100 | 8 | |||
| 7 | Cara | Wed | P-200 | 7 | |||
| 8 | Ana | Thu | P-300 | 6.5 | |||
| 9 | Ben | Thu | P-100 | 4 | |||
| 10 |
Common SUMIF errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The criteria is a number or comparison typed without quotes, such as >500 instead of ">500". | Wrap text and comparison criteria in double quotes: ">500". Plain numbers like 500 can stay unquoted. |
| Wrong total (too high or too low) | range and sum_range are different sizes, so Excel lines them up incorrectly. | Make both ranges cover the same number of rows, for example A2:A10 and B2:B10. |
| Result is 0 when matches exist | The criteria has extra spaces, different case-sensitive text, or numbers stored as text. | Trim spaces, check spelling, and convert text-numbers to real numbers with Text to Columns or VALUE. |
| #NAME? | The function name is misspelled, such as SUMIFS used with the wrong argument order, or SUMIF typed as SUM IF. | Use =SUMIF( exactly and let Excel's formula suggestions complete the name. |
| Dates do not match | Dates are stored as text, or the criteria date is written as plain text without a comparison operator. | Use real dates and build criteria with DATE, for example ">="&DATE(2024,3,1). |
Tips for using SUMIF
- Use cell references for criteria, such as
=SUMIF(A2:A10,D2,B2:B10), so you can change the condition without editing the formula. - Wildcards work in text criteria:
"Nor*"matches North, Norway, and Norwich. - If
sum_rangeis omitted, SUMIF adds therangeitself, which is useful when the condition and the numbers are in the same column. - For two or more conditions, switch to
SUMIFS, which uses the same idea with extra range/criteria pairs. - Lock ranges with
$(for example$A$2:$A$100) before copying the formula down or across.
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition. SUMIFS handles multiple conditions and puts the sum range first: =SUMIFS(sum_range, range1, criteria1, range2, criteria2).
Can SUMIF add values between two numbers?
Not in one SUMIF. Use two SUMIF formulas and subtract, or use SUMIFS with ">=100" and "<=500" criteria.
Why does SUMIF return 0 for text that looks identical?
Hidden spaces or non-breaking spaces often cause this. Clean the data with TRIM or CLEAN, or copy the exact criteria from the cell.
Does SUMIF work with dates?
Yes, as long as the dates are real Excel dates. Use operators in quotes and join a date, for example ">="&DATE(2024,1,1).
Is SUMIF case-sensitive?
No. "north" and "North" are treated the same. For case-sensitive totals, use SUMPRODUCT with EXACT.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SUMIF reference