📎 This article includes 2 downloadable practice files ↓
In this article
- Syntax & Parameter Breakdown
- Where You Will Use It
- Practical Worked Examples
- Mistakes People Make & How to Avoid Them
- Frequently Asked Questions
- Can SUMIF handle multiple criteria?
- Does SUMIF distinguish between uppercase and lowercase text?
- Can you use wildcards in SUMIF?
- SUMIF in practice
- Where you will use it
- Worked example
- Mistakes people make
- Continue learning
The SUMIF function in Excel is an essential spreadsheet tool designed to Calculate the total of numbers in a range that meet a specific condition, text match, date criteria, or logical comparison.
Syntax & Parameter Breakdown
=SUMIF(range, criteria, [sum_range])| Argument | Requirement & Description |
|---|---|
range |
Required. The range of cells you want evaluated by your criteria. |
criteria |
Required. The condition that determines which cells to add (e.g., “>100”, “Apples”, or cell references like B2). |
sum_range |
Optional. The actual cells to sum. If omitted, Excel sums the cells in the first range argument. |
Where You Will Use It
Used constantly in finance, accounting, and sales analysis to aggregate totals by category, vendor name, transaction department, or date range.
Practical Worked Examples
Here are common real-world formula scenarios utilizing SUMIF:
| Formula | Expected Behavior & Purpose |
|---|---|
=SUMIF(A2:A100, "North", C2:C100) |
Total revenue for all rows where Region equals ‘North’. |
=SUMIF(C2:C100, ">5000") |
Sums all transactions greater than 5,000 directly from the amount range. |
=SUMIF(B2:B100, "*Tech*", C2:C100) |
Wildcard match: sums amounts for any vendor containing ‘Tech’. |
Mistakes People Make & How to Avoid Them
Common error: range and sum_range having unequal sizes, causing slow recalculations or misaligned additions. Criteria strings with operators must be enclosed in quotes (e.g., “>=” & D1).
Frequently Asked Questions
Can SUMIF handle multiple criteria?
No, SUMIF handles only one condition. For multiple criteria across different columns, use the SUMIFS function.
Does SUMIF distinguish between uppercase and lowercase text?
No, SUMIF is case-insensitive. ‘North’ and ‘north’ will match identically.
Can you use wildcards in SUMIF?
Yes! Use ‘?’ to match any single character and ‘*’ to match any sequence of characters.
📚 Master spreadsheet formulas with our Free Excel Course or explore related functions in the Formula Lab.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
SUMIF in practice
Where you will use it
- Sales of one region
- Total of one expense head
- Sum amounts above a limit
Worked example
| Region | Sale | Formula | Result |
|---|---|---|---|
| North | 12000 | =SUMIF($A$2:$A$4,A2,$B$2:$B$4) | 27000 |
| South | 8000 | =SUMIF($A$2:$A$4,A3,$B$2:$B$4) | 8000 |
| North | 15000 | =SUMIF($A$2:$A$4,A4,$B$2:$B$4) | 27000 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Sum range a different size | Keep ranges the same height |
| Numbers as text | Text numbers aren't summed |
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 38 KB)
- 📗SUMIFS/COUNTIFS scenarios workbookConditional sums and counts with dates, OR logic and wildcards, plus 4 classic reasons totals come out wrong — all with PASS/FAIL checks.⬇ XLSX · 9 KB
- 📗Practice workbook: Math & rounding24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.