Excel Formulas
SUMIFS with Multiple Criteria and Date Ranges: Practical Workplace Examples
skills.efektifpro.com – Aggregating numerical values across multiple conditions is the cornerstone of business intelligence and financial reporting. While simple totals require only SUM, real-world reporting demands calculating metrics such as revenue generated by a specific regional rep within a defined fiscal quarter. The SUMIFS function handles these multi-layered requirements with exceptional speed and reliability.
⚡ Essential Rule of SUMIFS Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Crucial distinction: Unlike legacy SUMIF, the range to be added (sum_range) is always the first parameter in SUMIFS, followed by pairs of condition ranges and criteria.
Handling Date Criteria: The Quotation & Ampersand Rule
The single most frequent mistake analysts encounter with SUMIFS involves date operators (>=, <=, >, <). Because Excel evaluates logical comparison operators as text, operators must be enclosed in quotation marks and concatenated with date values or cell references using an ampersand (&).
Sample Transaction Ledger
| Date (Col A) | Rep (Col B) | Region (Col C) | Category (Col D) | Revenue (Col E) |
|---|---|---|---|---|
| 2026-03-05 | Sarah Jenkins | North | Software | $8,400 |
| 2026-03-12 | David Chen | South | Hardware | $14,200 |
| 2026-03-18 | Sarah Jenkins | North | Consulting | $6,100 |
| 2026-04-02 | Sarah Jenkins | North | Software | $11,500 |
| 2026-04-15 | Marcus Vance | East | Software | $9,300 |
Example 1: Sum Revenue Between Two Specific Dates
To calculate total sales that occurred exclusively between March 1, 2026 and March 31, 2026, enter two separate boundary conditions referencing Column A:
=SUMIFS(E2:E6, A2:A6, ">="&DATE(2026,3,1), A2:A6, "<="&DATE(2026,3,31))Result: $28,700 ($8,400 + $14,200 + $6,100). The April transactions are excluded automatically.
Example 2: Combining Dates with Rep and Text Wildcards
Suppose leadership requests total Q1 revenue generated by Sarah Jenkins for any category starting with “Soft”:
=SUMIFS(E2:E6, B2:B6, "Sarah Jenkins", D2:D6, "Soft*", A2:A6, ">="&DATE(2026,1,1), A2:A6, "<="&DATE(2026,3,31))Result: $8,400. Her March 18 consulting revenue is omitted, and her April 2 software sale falls outside the Q1 boundary.
SUMIFS Troubleshooting Checklist
#VALUE! Error: Occurs when yoursum_rangeandcriteria_rangediffer in size (e.g., summingE2:E100againstA2:A50). Every criteria range must encompass the exact same number of rows.- Result is Unexpectedly $0: Usually caused by hardcoded dates written as raw text (e.g.,
">=03/01/2026") which fail under differing regional date settings (US MM/DD vs UK DD/MM). Always construct dates withDATE(year, month, day)or reference formatted date cells.
📚 Related Excel Formulas Guides
