Math & Trig

SUMIFS Function in Excel

Adds cells that meet multiple criteria across multiple ranges.

Syntax

  • =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments

  • sum_range (required): The range to sum
  • criteria_range1 (required): First range to evaluate
  • criteria1 (required): Criteria for first range
  • criteria_range2 (optional): Additional range to evaluate
  • criteria2 (optional): Criteria for additional range

Examples

  • =SUMIFS(C:C, A:A, "East", B:B, ">1000") - Sum sales in East region over $1000 - Result: $45,000
  • =SUMIFS(D:D, A:A, A2, B:B, ">=2024-01-01") - Sum by product and date - Result: Filtered sum

Quick answer: sumifs multiple criteria

  • SUMIFS sums values where every criteria pair is true. Start with sum_range, then add criteria_range and criteria pairs.
  • Date range: =SUMIFS(Amount,Date,">="&Start,Date,"<="&End).
  • criteria_range and sum_range must be the same height.
  • Returns 0 when no row matches — verify criteria spelling first.

SUMIFS best practices

  • Use SUMIFS for totals that depend on multiple criteria, such as product, region, status, and date range.
  • Keep each criteria_range the same size and shape as the sum_range.
  • Use DATE for date criteria to avoid regional date format issues.

Date and month criteria patterns

  • Current month: =SUMIFS(Amount,Date,">="&EOMONTH(TODAY(),-1)+1,Date,"<="&EOMONTH(TODAY(),0)).
  • Between two date cells: =SUMIFS(Amount,Date,">="&Start,Date,"<="&End).
  • Year from another cell: =SUMIFS(Amount,YearCol,2026) when YearCol is a helper column.

Pivot Table vs SUMIFS

  • Use SUMIFS when the report layout is fixed and you need formula-driven KPI tiles.
  • Use a Pivot Table when users need to drag fields, slice data interactively, or explore many dimensions quickly.
  • Read [Pivot Table mistakes](/blog/pivot-table-mistakes/) when totals look wrong in either approach.

Reporting checklist

  • Lock criteria ranges with $ when copying across a summary matrix.
  • Exclude headers from criteria ranges unless you intend to include the header row.
  • Pair totals with [percentage formulas](/blog/excel-percentage-formula/) for margin and share KPIs.

People also ask

  • What is the order of arguments in SUMIFS? — sum_range, then criteria_range1, criteria1, pairs repeat.
  • Can SUMIFS sum between two dates? — Yes, with >= and <= criteria referencing date cells.

Common errors

  • #VALUE! when ranges have different sizes
  • Criteria must be in quotes if text/operators

Use cases

  • Multi-criteria totals
  • Filtered reporting
  • Conditional aggregation

Frequently asked questions

  • What is the difference between SUMIF and SUMIFS? SUMIF handles one criterion, SUMIFS handles multiple criteria with AND logic. SUMIFS syntax puts sum_range first: =SUMIFS(sum_range, criteria_range1, criteria1, ...). All criteria must be met for a value to be included.
  • How do I use OR logic with SUMIFS? SUMIFS uses AND logic. For OR, add multiple SUMIFS: =SUMIFS(C:C,A:A,"East")+SUMIFS(C:C,A:A,"West"). Or use SUMPRODUCT: =SUMPRODUCT((A:A="East")+(A:A="West"))*C:C) for complex OR conditions.
  • Can SUMIFS use wildcards? Yes! Use * for any characters, ? for single character: =SUMIFS(B:B, A:A, "*phone*") sums where column A contains 'phone'. Use ~* or ~? to match literal asterisk or question mark.
  • How do I sum between two dates with SUMIFS? Use two date criteria: =SUMIFS(C:C, A:A, ">=2024-01-01", A:A, "<=2024-12-31"). Or reference cells: =SUMIFS(C:C, A:A, ">="&D1, A:A, "<="&D2) where D1 and D2 contain dates.
  • Why is SUMIFS returning 0 when there should be matches? Common causes: 1) Criteria ranges different size than sum_range; 2) Numbers stored as text; 3) Extra spaces in data; 4) Date format mismatch. Check with COUNTIFS first to verify matches exist.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.