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, ...])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.
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.
Performance Pro Tips
- Unlike single-condition SUMIF, in SUMIFS the `sum_range` is always the FIRST parameter, not the last.
Frequently Asked Questions
Does SUMIFS recalculate automatically when data changes?
Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.
Is this formula compatible with Microsoft Excel?
Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.