Ravindra BagaleCourses & study guides Track your progress

Excel SUMIFS Function

By · Last updated:

Chala mitrano — today SUMIFS. One sum, many filters. City, status, date — stack the conditions and Excel adds only the matching rows.

Fictional sample data for learning. Cafe + cloud-kitchen orders (Pune / Dubai). Mixed ₹ and AED. Fictional brands.

Purpose

Add numbers only when every condition you list is true — for example, City = Pune and Status = Delivered.

Return value

A single number: the sum of sum_range cells that pass all criteria. Returns 0 when nothing matches.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

ArgumentRequired?Description
sum_rangeRequiredThe cells to add (must be numbers).
criteria_range1RequiredThe first range to test.
criteria1RequiredThe test for that range (number, text, cell, or expression like ">100").
criteria_range2, criteria2, …OptionalMore range/criteria pairs. All pairs must pass for a row to be included.

Version

Excel 2007 and later (Windows/Mac), Microsoft 365. Supported in LibreOffice Calc.

How to use — Why → What → How

Why: A plain SUM adds everything. Business questions need “only Delivered in Pune” or “only VIP in March”.

What: SUMIFS walks row by row: if all criteria pass, that row’s sum_range value is added.

How — numbered steps:

  1. Decide which column to add (Amount).
  2. Type =SUMIFS( and select the Amount column first.
  3. Select the first filter column (City), comma, then the criterion ("Pune" or a cell).
  4. Add more pairs for Status, Date, etc.
  5. Close the bracket and press Enter. Check the total against a filtered view once.

Ravindra Bagale's Tip

Friends, put sum_range first — opposite of SUMIF’s order. Mixing the order is the number-one SUMIFS typo in class.

Ravindra Bagale's Tip

Keep every criteria_range the same shape as sum_range (same number of rows). Uneven ranges give wrong totals or errors.

Download practice file: practice-sumifs.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) One city total

Add Amount where City is Pune.

CityAmount (₹)
Pune400
Dubai150
Pune250
Formula: =SUMIFS(B2:B4, A2:A4, "Pune")

Result: 650

What it means: Only the two Pune rows are added: 400 + 250.

2) City + status

Pune and Delivered only.

CityStatusAmount
PuneDelivered400
PuneCancelled200
DubaiDelivered150
Formula: =SUMIFS(C2:C4, A2:A4, "Pune", B2:B4, "Delivered")

Result: 400

What it means: Cancelled Pune is skipped; Dubai is skipped.

3) Numeric criterion

Sum amounts greater than 200.

OrderAmount
A1180
A2220
A3310
Formula: =SUMIFS(B2:B4, B2:B4, ">200")

Result: 530

What it means: 220 + 310. The criteria range can be the same as sum_range.

4) Cell reference as criterion

City name sits in E1 so the manager can change it.

CityAmount
Pune400
Dubai150
Pune250
Formula: =SUMIFS(B2:B4, A2:A4, E1) with E1 = Pune

Result: 650

What it means: Changing E1 to Dubai recalculates without editing the formula.

5) Wildcards for partial text

Sum all channels that start with App.

ChannelAmount
App-iOS90
App-Android110
Web70
Formula: =SUMIFS(B2:B4, A2:A4, "App*")

Result: 200

What it means: * means any characters after App.

6) Mixed currency sheet — sum AED rows only

Dubai cafe rows stored as AED amounts.

CityCurrencyAmount
PuneINR400
DubaiAED120
DubaiAED80
Formula: =SUMIFS(C2:C4, B2:B4, "AED")

Result: 200

What it means: Only AED rows; do not mix INR and AED in one KPI without converting.

Common errors and fixes

0 but you expected a total — Extra spaces, wrong case in older sheets, or mismatched spellings. Fix: TRIM data; confirm criteria text matches cells exactly.
#VALUE! — criteria_range size differs from sum_range. Fix: Select matching row counts for every range.
Wrong (too high) total — You used SUMIF argument order, or forgot a filter. Fix: Remember: sum_range is first in SUMIFS.
Dates not matching — Criteria date is text; sheet dates are real dates. Fix: Use a date cell or DATE(y,m,d) in the criterion.

Notes and tips

  • SUMIFS is AND logic across criteria. For OR, use several SUMIFS added together, or FILTER in Microsoft 365.
  • Text criteria are not case-sensitive in Excel.
  • Empty criterion cells can accidentally match blanks — prefer a real input cell with data validation.

SUMIF, COUNTIFS, AVERAGEIFS, SUM, FILTER

Practice tasks (10)

  1. Task 1. Sum Amount for City = Pune on sheet Sales.
    Hint: sum_range first, then City range and "Pune".
  2. Task 2. Sum Amount for Pune and Status = Delivered.
    Hint: two criteria pairs.
  3. Task 3. Sum Amount where Amount > 200.
    Hint: criteria can point at the Amount column itself.
  4. Task 4. Put a city name in H1 and sum using H1 as the criterion.
    Hint: no quotes around H1.
  5. Task 5. Sum all Channel values that start with App.
    Hint: wildcard *.
  6. Task 6. Sum only AED currency rows.
    Hint: filter on the Currency column.
  7. Task 7. Sum Delivered orders for Dubai.
    Hint: City and Status together.
  8. Task 8. Compare your Pune total with a manual filter — do they match?
    Hint: Data → Filter, then read the status bar sum.
  9. Task 9. Write why SUMIFS(0) can still be “correct”.
    Hint: think about filters that exclude every row.
  10. Task 10. Bonus: sum Pune Delivered where Amount >= 250.
    Hint: three criteria pairs.

Quick quiz

Q1. In SUMIFS, which argument comes first?
Q2. SUMIFS adds a row when any one criterion is true.
Q3. To sum values greater than 100 you write:
Q4. If nothing matches, SUMIFS returns:

Answers: Q1=sum_range, Q2=False, Q3=">100", Q4=0 (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: SUMIFS
SUMIFS revision card