FormuladrillSUMIFS
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) adds the values in sum_range for rows where every condition is true. The sum range comes first, then pairs of range and condition. All conditions must hold (AND logic).
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Part | What it is |
|---|---|
sum_range | The numbers to add. |
criteria_range1 | The first column to test. Same number of rows as sum_range. |
criteria1 | The first condition, such as "North", ">500", "<>Cancelled" or a cell reference. |
criteria_range2, criteria2 | More pairs, up to 127. |
Examples
=SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Shipped")Result: Total of C where region is North and status is Shipped
=SUMIFS(C:C, D:D, ">=" & DATE(2026, 1, 1), D:D, "<=" & DATE(2026, 1, 31))Result: Total of C for dates in January 2026
Use the same date column twice with a lower and upper bound.
=SUMIFS(C2:C100, A2:A100, E2, C2:C100, ">1000")Result: Total of amounts above 1000 for the region in E2
=SUMIFS(C2:C100, A2:A100, "<>North", B2:B100, "*Pending*")Result: Total for every region except North where status contains Pending
Common mistakes
- #VALUE!: one criteria range has a different number of rows than sum_range. Every range must be the same size.
- The result is 0 for a month: dates compared as text. Build the bound with DATE or reference a cell that holds a real date.
- Putting the sum range last, as in SUMIF: SUMIFS needs the sum range first.
- Expecting OR: SUMIFS(C:C, A:A, "North", A:A, "South") is 0 because no row is both. Add two SUMIFS or use SUMPRODUCT.
Practice SUMIFS
These drills check your answer on live data, free, no account needed.
- Two conditions at onceAdd up the units where the region is the one in F2 AND the product is the one in F3. Put the total in F4.
Questions
How do I SUMIFS between two dates?
Use the date column twice: =SUMIFS(amount, dates, ">=" & start, dates, "<=" & end), where start and end are cells holding dates.
How do I SUMIFS with OR?
Sum several SUMIFS, or use =SUM(SUMIFS(C:C, A:A, {"North","South"})), which evaluates the formula for each value in the braces.
Can SUMIFS sum across multiple columns?
No, sum_range must be one column or one row. Use one SUMIFS per column or restructure the data.
Related
- SUMIFHow to use SUMIF in Excel to add up numbers that match one criterion, with examples for text, greater than, cell references and wildcards, and common fixes.
- COUNTIFSHow to use COUNTIFS in Excel to count rows where two or more conditions are all true, with examples for text plus number, date ranges and not-equal.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.
- AVERAGEIFHow to use AVERAGEIF and AVERAGEIFS in Excel to average only the cells that match a condition, with examples for one region, positive values and two criteria.