Google Sheets SUMIF with Multiple Criteria
Use SUMIFS for multiple conditions in Google Sheets, or SUMPRODUCT for flexible AND and OR logic, date windows, and cell-reference criteria.
SUMIF is great for summing by a single condition. The moment you need two conditions — sum sales for Region = "West" and Product = "Widget" — you need SUMIFS.
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)Unlike SUMIF, SUMIFS puts the sum_range first, then alternates between criteria ranges and their conditions. You can add as many criteria pairs as you need.
Basic example
Sum all sales where the region is "West" and the product is "Widget":
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, "Widget")C2:C100— the column you're summing (Sales)A2:A100— first criteria column (Region)"West"— first criteria valueB2:B100— second criteria column (Product)"Widget"— second criteria value
All ranges must be the same size.
SUMIF and SUMIFS text criteria are case-insensitive. West, WEST, and west match the same criterion; use a different method only when capitalization is genuinely part of the rule.
TIP
Criteria values can reference cells instead of being hardcoded. Replace "West" with a cell reference like E2 to make the formula dynamic.
Using comparison operators
Criteria can include comparison operators as text strings:
=SUMIFS(C2:C100, A2:A100, "West", D2:D100, ">1000")This sums sales in the West region where the order value is greater than 1000.
| Operator | Example |
|---|---|
| Greater than | ">1000" |
| Less than or equal | "<=500" |
| Not equal | "<>Cancelled" |
| Contains (wildcard) | "*Widget*" |
To use a cell reference with a comparison operator, concatenate:
=SUMIFS(C2:C100, A2:A100, "West", D2:D100, ">"&E2)Summing by date range
To sum values between two dates, use two criteria on the same date column:
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">="&DATE(2026,1,1), B2:B100, "<"&DATE(2026,4,1))This sums West region sales in Q1 2026.
NOTE
Dates in Google Sheets are stored as numbers. SUMIFS compares them numerically, so >= and < work correctly with date values.
When to use SUMPRODUCT instead
SUMPRODUCT is more flexible than SUMIFS — it can handle OR logic and more complex array operations:
=SUMPRODUCT((A2:A100="West")*(B2:B100="Widget")*C2:C100)Each condition in parentheses evaluates to an array of 1s and 0s. Multiplying them together creates an AND: only rows where both are 1 contribute to the sum.
For OR logic — sum where Region is West or Product is Widget:
=SUMPRODUCT(((A2:A100="West")+(B2:B100="Widget")>0)*C2:C100)The >0 converts the OR result back to a binary array.
For a short OR list, adding separate SUMIFS results is often easier to audit:
=SUMIFS(C2:C100, A2:A100, "West") + SUMIFS(C2:C100, A2:A100, "South")Why SUMIFS returns zero or the wrong total
| Check | What to verify |
|---|---|
| Range alignment | The sum range and every criteria range start and end on corresponding rows |
| Data types | Amounts and dates are stored as numbers, not text that only looks numeric |
| Operators | A comparison using a cell reference is joined, such as ">"&E2 |
| Spaces | Imported labels do not have leading, trailing, or non-printing characters |
| OR logic | OR choices are not passed as separate criteria pairs, which would create AND logic |
TIP
Do this with AI
SUMIFS and SUMPRODUCT get tricky fast once you're dealing with date ranges, partial text matches, or OR conditions. Describe the calculation you need:
"Sum revenue from the West and South regions for orders placed in Q1 where the product category is Hardware"
Griddy writes the correct formula for your exact columns and data structure — no guessing at syntax or debugging #VALUE! errors.
Use the result in a small-business budget or expense tracker, and see SUMIF not working when an existing total is wrong.
Sources
Do this with AI
Turn the instructions into a finished spreadsheet.
Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.
Use this on real templates
Use conditional sums in finance templates
Multi-criteria sums are a core pattern for spending analysis, monthly totals, and management views by category, month, or owner.
Budget Tracker
Track income, expenses, and savings in one place. Line items, budgeted vs actual totals, and monthly net savings — free to use in your browser.
Open templateFinanceExpense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Open templateFinanceSmall Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Open template