How to Use SUMIF in Excel
Adds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
What is SUMIF in Microsoft Excel?
SUMIF looks down a criteria range, finds cells that match your condition, and adds the matching values from a separate sum range. Use it for a single condition. When you need two or more conditions, move to SUMIFS, which is SUMIF with unlimited criteria.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| range | Required | The column Excel checks against your criteria, such as the Region column. |
| criteria | Required | The condition to match. Text, a number, a cell reference, or an operator string like ">1000". |
| sum_range | Optional | The cells to add. If omitted, Excel adds the range itself. |
When to Use SUMIF (Real-World Business Scenarios)
- 1
Totalling sales for one region, one salesperson or one product category.
- 2
Adding all invoices above a value threshold using a criteria like ">50000".
- 3
Summarising a status column, for example all amounts where Status equals Delivered.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Region | Product | Amount |
|---|---|---|
| North | Laptop | 55000 |
| South | Laptop | 48000 |
| North | Mouse | 1200 |
How it works: Checks the Region column for 'North', finds rows 2 and 4, then adds their Amount values: 55,000 + 1,200.
Common Errors & How to Fix Them
Why it happens: The criteria text has a leading or trailing space, or the range and sum_range are different sizes so rows no longer line up.
Fix: Use TRIM on the criteria column and confirm both ranges start and end on the same rows.
Why it happens: sum_range was omitted, so Excel summed the criteria column instead of the amount column.
Fix: Always pass the third argument explicitly: =SUMIF(A2:A4,"North",C2:C4).
Try SUMIF Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (SUMIF)
What does the SUMIF formula do in Excel?
SUMIF looks down a criteria range, finds cells that match your condition, and adds the matching values from a separate sum range. Use it for a single condition. When you need two or more conditions, move to SUMIFS, which is SUMIF with unlimited criteria.
What is the syntax for SUMIF?
What are common mistakes when using SUMIF?
Result is 0: The criteria text has a leading or trailing space, or the range and sum_range are different sizes so rows no longer line up. (Use TRIM on the criteria column and confirm both ranges start and end on the same rows.) Result is too high: sum_range was omitted, so Excel summed the criteria column instead of the amount column. (Always pass the third argument explicitly: =SUMIF(A2:A4,"North",C2:C4).)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMAdds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
- COUNTIFCounts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
- AVERAGEReturns the arithmetic mean of a range while automatically ignoring blanks and text.
- MAXReturns the largest number in a range — highest sale, top score, latest date or most recent invoice.
Master SUMIF in Basic Excel
Learn SUMIF along with our formula library, practice files, and verifiable certificates in our complete curriculum.