In Excel, sometimes you don’t want to add all numbers.
You only want to add numbers that match a condition.
For example:
- Total sales from East region only
- Total marks greater than 70
- Total salary for HR department
For this, Excel gives us two powerful functions:
- SUMIF – Adds numbers with one condition
- SUMIFS – Adds numbers with multiple conditions
This guide will explain both functions step by step with clear examples.
1. SUMIF Function – One Condition
The SUMIF function adds values based on a single condition.
Formula:
=SUMIF(range, condition, [sum_range])
- range – Cells you want to check
- condition – the rule to match (example: “East”, “>70”)
- sum\_range (optional) – the cells to add (if different)
Example 1 – Add marks greater than 70:
| Student | Marks |
| Suresh | 78 |
| Dev | 65 |
| Kiran | 92 |
Formula:
=SUMIF(B2:B4, “>70”)
Excel checks column B and adds only numbers greater than 70.
Result → Adds the valid numbers → 78 + 92 = 170
Example 2 – Add sales from East region:
| Region | Month | Sales |
| East | Jan | 5000 |
| East | Feb | 3000 |
| West | Jan | 4000 |
Formula:
=SUMIF(A2:A4, “East”, C2:C4)
Excel checks column A for “East” and adds matching sales from column B.
Result → 8000 (5000 + 3000)
Pro Tip:
Use cell references instead of typing conditions directly. For example:
=SUMIF(A2:A4, “A2”, B2:B4) (if A2 contains “East”).
2. SUMIFS Function – Multiple Conditions
The SUMIFS function lets you add values that match more than one condition.
Formula:
=SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2, …)
- sum\_range → the numbers to add
- condition\_range1 → first range to check
- condition1 → first rule
- condition\_range2, condition2 → second rule, and so on
Example 1 – Add sales from East region in January
| Region | Month | Sales |
| East | Jan | 5000 |
| East | Feb | 3000 |
| West | Jan | 4000 |
Formula:
=SUMIFS(C2:C4, A2:A4, “East”, B2:B4, “Jan”)
Excel checks:
- Region = East
- Month = Jan
- Result → 5000
Example 2 – Add marks greater than 70 for Science subject:
| Student | Subject | Marks |
| Arjun | Math | 78 |
| Meera | Science | 65 |
| Rahul | Science | 92 |
| Arjun | Science | 85 |
Formula:
=SUMIFS(C2:C5, B2:B5, “Science”, C2:C5, “>70”)
Excel checks:
- Subject = Science
- Marks > 70
- Result → 177 (92 + 85)
Difference Between SUM and SUMIF and SUMIFS:
| Function | What it Does | Example |
| SUM | Adds all numbers | =SUM(A1:A10) |
| SUMIF | Adds numbers with 1 condition | =SUMIF(A1:A10, “>50”) |
| SUMIFS | Adds numbers with many conditions | =SUMIFS(C2:C10, A2:A10, “East”, B2:B10, “Jan”) |
The SUMIF and SUMIFS functions are must-know tools for anyone working with Excel.
Start by practicing with one condition using SUMIF.
Then move to SUMIFS for multiple conditions.
The more you use them, the faster and smarter your Excel reports will become.
You can also combine them with Excel features like Tables, Filters, and Conditional Formatting for powerful reporting.