SUMIF & SUMIFS — Complete Tutorial with 15 Examples

SUMIF and SUMIFS are the workhorses of every MIS report. If you work with sales data, attendance sheets, or financial reports in Excel, you use these daily. Yet they're one of the most-confused formulas for beginners because the argument order is different between SUMIF and SUMIFS.

SUMIF — One Condition

Syntax: =SUMIF(range, criteria, [sum_range])

Plain English: "Add up values in sum_range, but only where range meets the criteria."

Example 1: Total Sales for Delhi

Data: Column A = City, Column B = Sales Amount

=SUMIF(A:A, "Delhi", B:B) — sums all sales where city is Delhi.

Example 2: Sales Greater Than ₹10,000

=SUMIF(B:B, ">10000") — when sum_range is omitted, it sums the criteria range itself.

Example 3: Using Cell Reference as Criteria

If D1 contains the city name: =SUMIF(A:A, D1, B:B)

Example 4: Wildcard — All Products Starting with "Laptop"

=SUMIF(A:A, "Laptop*", B:B) — the * matches any characters after "Laptop".

Example 5: Not Equal To

=SUMIF(A:A, "<>Delhi", B:B) — sum all sales EXCEPT Delhi.

SUMIFS — Multiple Conditions

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Important: Notice the argument order is DIFFERENT from SUMIF. In SUMIFS, sum_range comes FIRST.

Example 6: Sales in Delhi AND Department = Sales

=SUMIFS(C:C, A:A, "Delhi", B:B, "Sales")

Example 7: Sales in Delhi AND Amount > ₹5,000

=SUMIFS(C:C, A:A, "Delhi", C:C, ">5000") — yes, sum_range and criteria_range can be the same column.

Example 8: Sales Between Two Dates

=SUMIFS(C:C, D:D, ">="&DATE(2026,1,1), D:D, "<="&DATE(2026,3,31)) — Q1 2026 sales.

Example 9: Sales for a Specific Month

If you only have dates, not month numbers:
=SUMPRODUCT((MONTH(D2:D100)=3)*(C2:C100)) — March sales. SUMIFS can't extract month directly, so SUMPRODUCT is the workaround.

Example 10: Three Conditions

=SUMIFS(E:E, A:A, "Delhi", B:B, "Sales", C:C, "2026") — Delhi + Sales department + Year 2026.

Example 11: Criteria from Cell References

=SUMIFS(C:C, A:A, G1, B:B, G2) — G1 has the city, G2 has the department. Dynamic and reusable.

Example 12: SUMIFS with OR Logic

SUMIFS uses AND logic by default. For OR (Delhi OR Mumbai):
=SUMIFS(C:C, A:A, "Delhi") + SUMIFS(C:C, A:A, "Mumbai")
Or use SUMPRODUCT: =SUMPRODUCT((A2:A100="Delhi")+(A2:A100="Mumbai"))*(C2:C100))

Real MIS Report Examples

Example 13: Monthly Sales Summary

Create a summary table with months in rows, regions in columns:
=SUMIFS(Sales, MonthCol, $A2, RegionCol, B$1) — lock row for month, lock column for region. Copy across the grid.

Example 14: Year-over-Year Comparison

=SUMIFS(Sales, Year, 2026, Product, "Widget") - SUMIFS(Sales, Year, 2025, Product, "Widget")

Example 15: Running Total with SUMIFS

=SUMIFS(B:B, A:A, ">="&$A$2, A:A, "<="&A2) — cumulative sum from the first date to current row's date.

Common Mistakes

• Wrong argument order — SUMIF: range, criteria, sum_range. SUMIFS: sum_range, range, criteria. They're swapped!
• Criteria not in quotes — ">5000" needs quotes. =SUMIF(A:A, >5000, B:B) will error.
• Mixing text and numbers — if "5000" is stored as text, ">5000" won't work. Clean data first.
• Date criteria without DATE() — use ">="&DATE(2026,1,1), not ">="&"01/01/2026".