How to Use SUMIFS in Excel (and Google Sheets): A Step‑by‑Step Guide
What Is SUMIFS and When to Use It?
SUMIFS adds numbers that meet multiple criteria.
- SUMIF checks only one condition.
- SUMIFS can filter on two, three, or more dimensions (e.g., date range and region and product).
| Function | Minimum Excel version | Google Sheets equivalent |
|---|---|---|
SUMIFS |
Excel 2007 (all later) | SUMIFS (identical syntax) |
SUMIF |
Excel 2007 | SUMIF |
SUMPRODUCT (alternative) |
Any version | Any version |
Typical scenarios
| Scenario | How SUMIFS helps |
|---|---|
| Sales by month + region | Returns a single total for the month and the region you specify. |
| Project expenses within a date range | Sums only rows that match the project ID and fall between two dates. |
| Inventory by supplier + product line | Filters on both supplier name and product category. |
Because it evaluates all criteria before adding, SUMIFS is ideal for dynamic dashboards, large data sets, and formulas that need to be easy to audit. Whenever you need one total that satisfies more than one condition, reach for SUMIFS. The function works the same in Excel and Google Sheets.
Understanding the SUMIFS Syntax
SUMIFS(sum_range, criteria_range1, criteria1,
[criteria_range2, criteria2], …)
| Argument | Description | Required? |
|---|---|---|
| sum_range | Cells containing the values to total. | Yes |
| criteria_range1 | First range to evaluate. | Yes |
| criteria1 | Condition for criteria_range1 (e.g., ">100" or "East"). |
Yes |
| criteria_range n, criteria n | Additional pairs for extra conditions. | No |
Key points
- All ranges must be the same size; otherwise you get
#VALUE!. - Criteria are text strings. Use comparison operators inside quotes or concatenate a cell reference (
">"&B2). - Wildcards
*(any number of characters) and?(single character) work in text criteria. - Date criteria can be literal dates (
"2024-01-01") or built withDATE(). In Excel you must concatenate the operator (">="&DATE(2024,1,1)); Google Sheets accepts the same syntax.
Simple example
| A (Region) | B (Product) | C (Units) |
|---|---|---|
| East | Widget | 120 |
| West | Gadget | 85 |
| East | Gadget | 150 |
| West | Widget | 60 |
Formula
=SUMIFS(C2:C5, A2:A5, "East", B2:B5, "Widget")
Result: 120 (only the first row matches both criteria).
Common SUMIFS Examples with Sample Data
The same formulas work in Excel 365/2021 and Google Sheets. The only nuance is that Excel allows omitting criteria_range when it is identical to sum_range; Google Sheets requires it.
| Row | Region | Product | Month | Sales |
|---|---|---|---|---|
| 1 | North | Widget | Jan | 1200 |
| 2 | South | Gadget | Jan | 800 |
| 3 | North | Widget | Feb | 1500 |
| 4 | East | Widget | Jan | 600 |
| 5 | South | Widget | Feb | 900 |
| 6 | North | Gadget | Jan | 400 |
| 7 | East | Gadget | Feb | 700 |
1. Total sales for a single region
=SUMIFS(E2:E8, B2:B8, "North")
Result: 4 200 (rows 1, 3, 6).
2. Sales for a region and a product
=SUMIFS(E2:E8, B2:B8, "North", C2:C8, "Widget")
Result: 2 700 (rows 1 + 3).
3. Sales for a product in a specific month
=SUMIFS(E2:E8, C2:C8, "Widget", D2:D8, "Jan")
Result: 1 800 (rows 1 + 4).
4. Excluding a region (all sales except South)
=SUMIFS(E2:E8, B2:B8, "<>South")
Result: 4 300 (rows 1, 3, 4, 6, 7).
Troubleshooting SUMIFS Errors
| Problem | Why it happens | Quick fix |
|---|---|---|
| Mismatched range sizes | sum_range and a criteria_range have different row counts. |
Ensure every range covers the exact same rows/columns. |
| Numbers stored as text | Text "100" doesn’t match numeric criteria >100. |
Convert the column to numbers (Data → Text to Columns or =VALUE(cell)). |
| Date criteria not recognized | Dates entered as strings are treated as text. | Use real date values or DATE(), e.g., ">="&DATE(2024,1,1). |
| Missing comparison operator | "100" is interpreted as an exact match, not “greater than”. |
Include the operator: ">100", "<>"&A2, etc. |
| Wildcard used without quotes | *Inc is read as multiplication. |
Enclose the pattern in quotes: "*Inc". |
| Whole‑column references in older Excel | Excel 2007‑2013 can’t handle A:A in SUMIFS. |
Use a bounded range (A2:A1000) or a Table column reference. |
Advanced SUMIFS Techniques
1. Summing by a date range
| Date | Category | Amount |
|---|---|---|
| 2024‑01‑01 | Food | 120 |
| 2024‑01‑15 | Travel | 300 |
| 2024‑02‑05 | Food | 85 |
| 2024‑02‑20 | Travel | 150 |
=SUMIFS(C2:C5, A2:A5, ">="&DATE(2024,1,1), A2:A5, "<="&DATE(2024,1,31))
Result: 420 (January rows).
2. Case‑insensitive text match
| Product | Region | Sales |
|---|---|---|
| Apple | East | 200 |
| apple | West | 150 |
| Banana | East | 90 |
| Apple | West | 130 |
=SUMIFS(C2:C5, A2:A5, "Apple")
Result: 480 (both “Apple” and “apple” match).
For a case‑sensitive match:
=SUM(FILTER(C2:C5, EXACT(A2:A5, "Apple")))
3. Using wildcards for partial matches
| Item | Store | Revenue |
|---|---|---|
| Red Apple | A | 300 |
| Green Apple | B | 250 |
| Apple Juice | A | 180 |
| Banana Split | B | 120 |
=SUMIFS(C2:C5, A2:A5, "*Apple*")
Result: 730 (all rows containing “Apple”).
Tips for Optimising SUMIFS Performance
| Tip | Why it helps | How to apply |
|---|---|---|
Use exact range references (avoid whole‑column A:A) |
Reduces the number of cells Excel/Sheets must scan. | Define a Table or a dynamic named range, e.g., Table1[Amount]. |
| Limit the number of criteria | Each extra pair adds another pass through the data. | Combine related logic in a helper column (e.g., a column that flags “West & Q1”) and reference that column once. |
| Convert the data to an Excel Table | Tables use optimized internal structures and auto‑expand when rows are added. | Insert > Table → use structured references like =SUMIFS(Table1[Sales], Table1[Region], "West"). |
| Turn off automatic calculation while building formulas | Prevents constant recalculation on every edit. | Formulas > Calculation Options > Manual (Excel) or File > Spreadsheet settings > Calculation > Manual (Google Sheets). Press F9 (Excel) or Ctrl + R (Sheets) to recalc. |
Avoid volatile functions inside SUMIFS (NOW(), RAND(), INDIRECT()) |
Volatile functions force a full workbook recalculation. | Use static values or non‑volatile alternatives (INDEX instead of INDIRECT). |
| Use helper columns for complex logic | Complex array criteria slow down SUMIFS. |
Add a column that returns TRUE/FALSE or a numeric flag, then reference that column as a single criterion. |
Prefer SUMPRODUCT for OR logic |
SUMIFS only handles AND conditions; simulating OR with multiple SUMIFS adds overhead. |
Example: =SUMPRODUCT((Region="West")*(Product={"Widget","Gadget"})*Sales) for “West and (Widget or Gadget)”. |
FAQ
How do I sum values with multiple criteria in Excel?
Use SUMIFS, listing the sum range first, then each criteria_range/criteria pair. Example: =SUMIFS(D2:D100, A2:A100, "East", B2:B100, ">100") adds values in D where column A equals “East” and column B is greater than 100.
Can I use SUMIFS with date ranges?
Yes. Combine two date criteria, one for the start and one for the end, e.g., =SUMIFS(C2:C50, A2:A50, ">="&DATE(2024,1,1), A2:A50, "<="&DATE(2024,12,31)). In Google Sheets the same syntax works.
What are the differences between SUMIF and SUMIFS?
SUMIF accepts a single criterion; SUMIFS accepts two or more, all of which must be true (AND logic). The order of arguments is also different: SUMIF(sum_range, criteria, [sum_range]) versus SUMIFS(sum_range, criteria_range1, criteria1, …).
How to handle blank cells in SUMIFS?
Blank cells are ignored unless your criterion explicitly looks for them, e.g., =SUMIFS(D2:D100, B2:B100, "="). If you want to treat blanks as zero, no extra step is needed because they contribute nothing to the sum.
Is SUMIFS available in Google Sheets?
Yes. Google Sheets implements SUMIFS with the same syntax as Excel. The only practical difference is that Sheets always requires the criteria_range argument, even when it matches the sum_range.
End of guide.