Formulas - 2026-05-12

SUMIFS in Excel: Practical Examples

Learn SUMIFS in Excel with examples for dates, regions, products, status fields, month filters, and common zero-result mistakes.

Quick answer: sumifs examples

  • SUMIFS examples use a Data sheet (transactions), a Setup sheet (StartDate, EndDate, Region), and a Report sheet with formulas referencing Setup cells.
  • Lock Setup references with $ when copying formulas.
  • Pair with [SUMIF vs SUMIFS](/compare/sumif-vs-sumifs/).

Overview

  • SUMIFS powers sales dashboards, payroll summaries, and operations KPIs. These examples use a Data sheet plus a Setup sheet so criteria stay easy to change without editing formulas.

Example workbook structure

  • Data: Date, Region, Product, Status, Amount (Excel Table recommended).
  • Setup: StartDate, EndDate, Region in F2, Status in F3.
  • Report: =SUMIFS(Data[Amount],Data[Region],Setup!$F$2,Data[Status],Setup!$F$3).

12 patterns to copy

  • Current month with EOMONTH on Date column.
  • Between two date cells with >= and <= criteria.
  • Wildcard product: =SUMIFS(Amount,Product,"*"&G2&"*",Region,"East").
  • Exclude closed: Status,"<>Closed".
  • YTD by year helper column matching Setup!Year.

Pair with other metrics

  • Volume: COUNTIFS with the same criteria pairs.
  • Share: [percentage formula](/blog/excel-percentage-formula/).
  • Function reference: [SUMIFS](/functions/sumifs/).

What SUMIFS Does

  • SUMIFS adds numbers that meet one or more conditions. It is essential for reports, dashboards, and month-end analysis. Basic syntax: \\\ =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) \\\

Sum by Region

  • \\\ =SUMIFS(D:D,A:A,"East") \\\ This adds values in column D where column A equals East.

Sum by Region and Status

  • \\\ =SUMIFS(D:D,A:A,"East",B:B,"Closed") \\\ Use multiple conditions to create precise report totals.

Sum by Date Range

  • \\\ =SUMIFS(D:D,C:C,">="&F1,C:C,"<="&G1) \\\ This sums values between the start date in F1 and the end date in G1.

Common SUMIFS Mistakes

  • Sum range and criteria range sizes do not match Dates stored as text Criteria missing quotes Extra spaces in category names Numbers stored as text

Related Guides

  • SUMIFS function SUMIF vs SUMIFS SUMIF Not Working? Excel Percentage Formula

SUMIFS Reporting Templates

  • Use these common report patterns: Report question SUMIFS pattern Sales by region =SUMIFS(Sales,Region,"East") Sales by region and status =SUMIFS(Sales,Region,"East",Status,"Closed") Sales between dates =SUMIFS(Sales,Date,">="&StartDate,Date,"<="&EndDate) Sales for current month =SUMIFS(Sales,Date,">="&EOMONTH(TODAY(),-1)+1,Date,"<="&EOMONTH(TODAY(),0))

Expert Checks Before Trusting the Result

  • SUMIFS is reliable only when the source data is clean. Check that all criteria ranges are the same size, dates are real dates, category names are consistent, and numbers are not stored as text. If a report will grow, convert the source range into an Excel Table and use structured references. This makes formulas easier to read and reduces broken ranges.

Internal Links for Deeper Work

  • Review the SUMIFS function, compare SUMIF vs SUMIFS, and use Excel Percentage Formula for margins and variance after totals are calculated.

Example workbook layout

  • Sheet Purpose Data Raw transactions with Date, Region, Product, Amount Setup StartDate, EndDate, selected Region in F2 Report SUMIFS formulas referencing Setup cells Download pattern: keep Setup cells unlocked and lock formula cells when sharing.

Frequently asked questions

  • What is SUMIFS used for? SUMIFS adds values that meet multiple criteria, such as region, product, status, or date range.
  • Why is SUMIFS returning zero? Common causes include text dates, mismatched range sizes, extra spaces, or criteria that do not match the source data.
  • Can SUMIFS use a cell as criteria? Yes. Use the cell directly for exact criteria, or concatenate operators for comparisons, such as ">="&F1.
  • Why does SUMIFS work on one sheet but not another? The most common causes are inconsistent range sizes, dates stored as text, different category spelling, or extra spaces in imported data.