📖 What is the SUMIFS Function?
SUMIFS is Excel's multi-condition aggregation function that sums values in a sum range only when all specified criteria pairs are satisfied simultaneously. It is the plural, more powerful successor to SUMIF, introduced in Excel 2007 and available in all modern versions.
While SUMIF handles only one condition, SUMIFS can evaluate up to 127 criteria range / criteria pairs — enabling complex queries like: "Total sales for North Zone, Product = Laptop, Month = March" — all in a single formula without helper columns or pivot tables.
SUMIF (1 condition)
=SUMIF(region,"North",sales)✗ Can't filter by product or date
SUMIFS (multiple conditions)
=SUMIFS(sales,region,"North",product,"Laptop")✓ Both conditions applied at once
⚙️ Syntax & Arguments
=SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )
sum_range → Required. The range of cells to sum (e.g., C2:C100).
criteria_range1 → Required. First range to evaluate (e.g., A2:A100).
criteria1 → Required. Condition to match in criteria_range1.
[criteria_range2] → Optional. Second range to evaluate.
[criteria2] → Optional. Condition for criteria_range2.
… → Up to 127 range/criteria pairs allowed.
── Real-world formula examples ───────────────────────────────────
=SUMIFS(C:C, A:A,"North", B:B,"Laptop") → 2 text conditions
=SUMIFS(D:D, A:A,"HR", C:C,">50000") → text + numeric comparison
=SUMIFS(E:E, A:A,G1, B:B,H1, C:C,">"&I1) → cell refs + dynamic criteria
=SUMIFS(C:C, B:B,">="&DATE(2025,1,1), B:B,"<="&DATE(2025,3,31)) → date range
| Criteria Type | Example | What It Does |
|---|---|---|
| "North" | ="North" | Exact text match |
| ">50000" | =SUMIFS(...,">50000") | Greater than 50,000 |
| "<>Cancelled" | not equal to "Cancelled" | Excludes a value |
| "*Ltd*" | contains "Ltd" | Wildcard partial match |
| ">="&DATE() | dynamic date range | Date-based filtering |
ℹ️ Critical rule:
sum_range and every criteria_range must be
the same size and shape. SUMIFS uses AND logic — all conditions must be true
simultaneously for a row to be included. For OR logic, use two SUMIFS and add them:
=SUMIFS(...,"A",...) + SUMIFS(...,"B",...)
📊 Example 1 — Region + Product Filter
Basic Use Case
A Sales Manager wants the total sales for "North" zone where Product is "Laptop". Two conditions applied simultaneously on a transaction register.
| A — Region | B — Product | C — Salesperson | D — Sales (₹) |
|---|---|---|---|
| North | Laptop | Amit Sharma | 1,45,000 |
| South | Laptop | Priya Nair | 98,000 |
| North | Mobile | Rajiv Gupta | 72,000 |
| North | Laptop | Kavita Joshi | 2,10,000 |
| West | Laptop | Mohan Pillai | 1,32,000 |
| North | Laptop | Sunita Das | 1,87,000 |
| North + Laptop Total | ₹ 5,42,000 | ||
Formula in F2 → =SUMIFS(D2:D7, A2:A7,"North", B2:B7,"Laptop")
✅ Result: ₹ 5,42,000
📊 Example 2 — MIS Report: Department + Grade + Amount Threshold
Advanced / Practical Use Case
An HR & Finance team needs to find the total salary paid to Grade-A employees in the Marketing department whose CTC exceeds ₹8,00,000 — three conditions applied simultaneously using cell references for a reusable MIS dashboard.
| A — Dept | B — Grade | C — Employee | D — CTC (₹) |
|---|---|---|---|
| Marketing | A | Ritu Agarwal | 12,00,000 |
| Marketing | B | Anil Mehta | 7,50,000 |
| HR | A | Neha Singh | 9,20,000 |
| Marketing | A | Vikram Desai | 6,80,000 |
| Marketing | A | Pooja Sharma | 10,50,000 |
| Finance | A | Suresh Iyer | 11,00,000 |
| Marketing + Grade A + CTC > 8L | ₹ 22,50,000 | ||
Criteria cells: G1 = "Marketing" | G2 = "A" | G3 = 800000
Formula in G4 → =SUMIFS(D2:D7, A2:A7, G1, B2:B7, G2, D2:D7, ">"&G3)
Formula in G4 → =SUMIFS(D2:D7, A2:A7, G1, B2:B7, G2, D2:D7, ">"&G3)
✅ Result: ₹ 22,50,000 (changes dynamically with G1, G2, G3)
💡 Key Applications
- MIS Building dynamic MIS dashboards that slice revenue or cost by multiple dimensions — region, product, month, channel — without pivot tables.
- Finance Summing approved invoices above a threshold amount for a specific vendor and within a financial quarter for cash flow reporting.
- Tax Aggregating GST-eligible B2B sales by state code and HSN chapter for precise GSTR-1 outward supply breakup.
- HR Calculating total payroll liability for a specific department, grade, and employment type (permanent vs. contract) from HR master data.
- Audit Flagging and summing transactions that are both above ₹2,00,000 and posted to suspense accounts — dual-condition high-risk review.
- Retail Computing category-wise, store-wise revenue for a specific week from daily POS data — three-condition aggregate in one formula.
- Sales Tracking quarterly attainment by computing actual sales per salesperson per product per region against target in a single cell.
⚠️ Common Mistakes to Avoid
1. Putting sum_range LAST (SUMIF habit carried into SUMIFS)
In SUMIF, sum_range is the 3rd argument. In SUMIFS, sum_range comes FIRST.
Writing
=SUMIFS(A:A,"North",B:B,"Laptop",C:C) will return wrong results or a #VALUE! error.
✔ Fix: Always start with sum_range:
=SUMIFS(C:C, A:A,"North", B:B,"Laptop")2. Unequal Range Sizes
Using
=SUMIFS(C1:C10, A1:A10,"North", B1:B8,"Laptop") where B range has fewer rows
causes Excel to return a #VALUE! error — all ranges including sum_range must be identical in size.
✔ Fix: Use consistent, same-size ranges:
=SUMIFS(C1:C10, A1:A10,"North", B1:B10,"Laptop")3. Using SUMIFS for OR Logic (Gets AND Instead)
Writing
=SUMIFS(C:C,A:A,"North",A:A,"South") expecting North OR South totals returns 0 —
SUMIFS applies AND logic. A single cell cannot simultaneously be "North" and "South".
✔ Fix: Add two SUMIFS:
=SUMIFS(C:C,A:A,"North") + SUMIFS(C:C,A:A,"South")4. Missing Quotes Around Comparison Operators
Writing
=SUMIFS(C:C,D:D,>50000) without quotes around the operator throws a formula
parse error. Excel requires the entire comparison to be wrapped as a text string.
✔ Fix: Always quote operators:
=SUMIFS(C:C,D:D,">50000") or use ">"&E1 for dynamic values.5. Not Locking Reference Ranges When Copying Formula
Dragging a SUMIFS formula across cells without absolute references causes the data ranges to shift
row-by-row, producing incorrect totals silently — the most dangerous mistake in dashboard builds.
✔ Fix: Lock data ranges with $:
=SUMIFS($C$2:$C$100, $A$2:$A$100, F2, $B$2:$B$100, G2)