Math & Aggregation FormulaGoogle Sheets V8 Engine Verified
Google Sheets SUMIFS: Multi-Condition Summation with Wildcards
SUMIFS calculates the mathematical sum of values in a range that meet multiple criteria, such as date boundaries, status strings, customer tiers, and wildcard patterns.
Formula Syntax:
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])💡 Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.
Technical Overview & Engine Mechanics
SUMIFS sums values in a range that meet multiple criteria across multiple condition ranges. Unlike legacy single-condition SUMIF, SUMIFS places the sum_range as the first argument, followed by pairs of criteria_range and criterion. It is one of the most widely used functions in corporate accounting, revenue modeling, and inventory tracking.
Formula Comparison: SUMIFS vs. Alternatives
Compared to SUMPRODUCT or array formulas, SUMIFS is heavily optimized in Google's calculation engine and evaluates multi-condition totals with sub-millisecond latency. For reporting across 50,000+ rows, SUMIFS is faster than running complex QUERY aggregations.
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| sum_range | Range | Required | The numeric column to sum (e.g. Revenue!D:D). |
| criteria_range1 | Range | Required | The range evaluated against criterion1. |
| criterion1 | Expression | Required | The condition (e.g. ">1000", "Completed", or cell ref A2). |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Sum Paid Invoices for Specific Client in 2026
=SUMIFS(D2:D, A2:A, "Acme Corp", B2:B, "Paid", C2:C, ">=2026-01-01")How it works: Sums column D where client is Acme Corp, status is Paid, and date is on or after Jan 1, 2026.
Example 2: Wildcard Summation by Product Prefix
=SUMIFS(D2:D, A2:A, "Cloud-*", C2:C, ">500")How it works: Sums all items where SKU begins with "Cloud-" and revenue exceeds $500.
Example 3: Dynamic Cell Reference Criteria
=SUMIFS(D:D, B:B, ">=" & G1, B:B, "<=" & G2)How it works: Sums revenue between dynamic start date G1 and end date G2.
Example 4: Q4 High-Value Enterprise Revenue Sum
=SUMIFS(D2:D5000, B2:B5000, "Enterprise", C2:C5000, ">=2026-10-01", C2:C5000, "<=2026-12-31", E2:E5000, "Won")How it works: Sums revenue (D) where Segment is Enterprise, Close Date is in Q4 2026, and Deal Stage is Won.
Example 5: Dynamic Sum Excluding Internal Test Accounts
=SUMIFS(Revenue, AccountType, "Paid", Email, "<>*@company.com")How it works: Uses wildcards (* and <>) to sum revenue excluding internal company email addresses.
Common Errors & Solutions
❌ #VALUE! Array arguments to SUMIFS are of different size
Resolution: All criteria ranges must have the exact same starting and ending row as the sum_range.
❌ #VALUE! Range dimension mismatch
Resolution: The sum_range and all criteria_ranges must share identical height and width dimensions (e.g. D2:D5000 and B2:B5000).
❌ Incorrect operator concatenation with cell references
Resolution: Comparison operators must be quoted and concatenated with ampersands when referencing cells: ">=" & F1, not ">=F1".
Performance Pro Tips
- Unlike single-condition SUMIF, in SUMIFS the `sum_range` is always the FIRST parameter, not the last.
Frequently Asked Questions: SUMIFS
Why is sum_range first in SUMIFS but last in SUMIF?
SUMIFS allows an unlimited number of criteria pairs, so placing sum_range first ensures the calculation target is fixed.
Can I use wildcards in SUMIFS?
Yes. Asterisk (*) matches any string of characters; question mark (?) matches any single character.
How do I sum by date ranges in SUMIFS?
Provide two date criteria pairs: criteria_range, ">="&StartDate, criteria_range, "<="&EndDate.
Does SUMIFS handle case sensitivity?
SUMIFS is case-insensitive: 'West' and 'west' match identically.
Can SUMIFS evaluate OR conditions across the same column?
Add two SUMIFS together: =SUMIFS(range, cat, "A") + SUMIFS(range, cat, "B").