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

ArgumentTypeRequiredDescription
sum_rangeRangeRequiredThe numeric column to sum (e.g. Revenue!D:D).
criteria_range1RangeRequiredThe range evaluated against criterion1.
criterion1ExpressionRequiredThe 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").

Related Google Sheets Formulas