Mastering Google Sheets Sumifs: Multi-Criteria Sums
Learn how to use SUMIFS in Google Sheets to sum values with multiple criteria. This practical guide covers syntax, examples, OR logic workarounds, and tips to avoid common errors in dashboards and budgets.
SUMIFS is Google Sheets' multi-criteria summing function. It adds values in a sum_range only when all corresponding criteria ranges meet their criteria. It supports text, numbers, dates, and operators. With careful range alignment and dynamic criteria, SUMIFS can power dashboards, budgets, and reports without scripting. This guide walks through syntax, examples, and common pitfalls.
Understanding SUMIFS: multi-criteria sums in Google Sheets
SUMIFS is the go-to function when you need to conditionally sum values based on multiple criteria. In Google Sheets, you provide a sum_range and then pairs of criteria_range and criteria. SUMIFS applies all criteria with logical AND; a row is included only if every criterion is true. This section introduces the syntax and a couple of practical examples to get you started.
=SUMIFS(Sales!B:B, Sales!A:A, "West", Sales!C:C, "Widget")Parameters:
-
sum_range: Range to sum (e.g., B:B) -
criteria_range1,criteria1: First criterion pair -
Additional criterion pairs can be added in sequence
-
Use exact matches for text and numeric comparisons for numbers.
Working with multiple criteria and operators
SUMIFS supports a mix of text, numbers, and dates as criteria, including operators. Here are representative patterns:
=SUMIFS(Sales!B:B, Sales!A:A, "West", Sales!D:D, ">100")=SUMIFS(Sales!B:B, Sales!A:A, A2, Sales!C:C, C2)These examples demonstrate straightforward AND logic across two criteria. When criteria are in cells, you can reference them directly. This makes SUMIFS adaptable for dashboards where criteria change month to month or per user input.
OR criteria and multi-criteria sums
SUMIFS implements AND logic, so to emulate OR behavior you can wrap SUMIFS results in a SUM and pass an array of criteria values:
=SUM(SUMIFS(Sales!B:B, Sales!A:A, {"West","East"}, Sales!C:C, "Widget"))This totals rows where region is West or East and the product is Widget. You can apply the same pattern to other ranges and criteria, for example across multiple budgets or date groups.
Dynamic criteria and named ranges
Dynamic criteria are powerful for automation. Use cell references or named ranges for criteria:
=SUMIFS(Sales!B2:B1000, Sales!A2:A1000, $F$1, Sales!C2:C1000, $F$2)Named ranges improve readability:
=SUMIFS(Sales, Regions, "West", Products, "Widget")Dynamic criteria enable live filtering as you adjust input cells, which is ideal for interactive dashboards.
Best practices for scale and reliability
When working with large datasets, limit ranges to the actual data to improve performance and reduce recalculation time:
=SUMIFS(Sales!B2:B1000, Sales!A2:A1000, "West", Sales!C2:C1000, "Widget")Avoid mismatched ranges; all criteria ranges must have the same size as the sum_range. If you must include entire columns, test extensively and consider using FILTER or QUERY for very large sheets.
Steps
Estimated time: 45-75 minutes
- 1
Identify data layout
Map out which columns hold the values to sum and which columns contain the criteria. Decide if you’ll use fixed ranges (e.g., B2:B1000) or dynamic ranges that expand with data.
Tip: Sketch a small mock dataset to validate your ranges before building the formula. - 2
Write the base SUMIFS formula
Start with a simple two-criteria example to confirm you understand the syntax. Use literal values or cell references for criteria and verify the result.
Tip: Use named ranges to improve readability if your sheet is complex. - 3
Test with sample data
Change criteria values and confirm the output updates as expected. Check for edge cases like empty rows or non-matching criteria.
Tip: Try including a date criterion to ensure date handling is correct. - 4
Extend to multiple criteria
Add more criteria_range/criteria pairs as needed. Maintain alignment across all ranges so they have the same length.
Tip: Mind the order of arguments: sum_range first, then each pair of criterion_range and criterion. - 5
Validate performance and readability
If the dataset grows, consider limiting ranges and/or using named ranges. Document assumptions for future maintainers.
Tip: Regularly audit formulas in dashboards to prevent stale references.
Prerequisites
Required
- Required
- Basic understanding of ranges, cell references, and operatorsRequired
Optional
- Familiarity with named ranges (optional)Optional
- Internet connection and a compatible deviceOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy a formula or cell value | Ctrl+C |
| PastePaste into target cell or range | Ctrl+V |
| Fill downExtend a formula to adjacent rows | Ctrl+D |
| Fill rightExtend a formula across columns | Ctrl+R |
| UndoUndo recent edits | Ctrl+Z |
| FindSearch within the sheet | Ctrl+F |
FAQ
What is SUMIFS and how does it differ from SUMIF?
SUMIFS adds values from a sum_range when all specified criteria are met. SUMIF handles a single criterion. SUMIFS is the multi-criteria version and supports complex filters.
SUMIFS lets you sum data based on several conditions at once, unlike SUMIF which handles only one condition.
Can SUMIFS handle dates and date ranges?
Yes. You can compare date columns using operators (e.g., ">=2026-01-01"), or reference date cells. Ensure the date column is actual date values, not text.
Absolutely. Use operators or cell references to filter by dates.
How do I use wildcards in SUMIFS criteria?
Use text criteria with wildcards: "Widget*" matches any text starting with Widget. The characters ? and * function like their SQL SQL counterparts in text comparisons.
You can match patterns with wildcards like * and ? in text criteria.
What happens if the criteria ranges don’t have the same length as the sum range?
All criteria ranges must be the same length as the sum_range. A mismatch returns an error. Prefer fixed ranges that cover your data.
Mismatched lengths cause errors; keep all ranges aligned.
How can I sum across multiple regions (an OR condition) with SUMIFS?
SUMIFS can simulate OR by wrapping in SUM and using an array constant for the region, e.g., SUM(SUMIFS(..., {"West","East"}, ...)).
To combine regions, wrap SUMIFS in SUM with an array of regions.
Can I use SUMIFS with named ranges?
Yes. Named ranges improve readability and maintainability, especially in large spreadsheets. Replace range references with the named range names in the formula.
Absolutely—named ranges work well with SUMIFS and make formulas easier to read.
The Essentials
- Master SUMIFS syntax for multi-criteria sums
- Use fixed or named ranges to improve reliability
- Remember: SUMIFS uses AND across criteria
- For OR logic, sum multiple SUMIFS results
- Validate with real data and edge cases