How to Use SUMIF with Multiple Criteria in Google Sheets
Understanding SUMIF Basics
SUMIF adds up values in a single column (or range) that meet one condition. The syntax is identical in Google Sheets and modern Excel, but Sheets lets you use whole‑column references without worrying about absolute row limits.
SUMIF(range, criterion, [sum_range])
| Argument | Description | Example |
|---|---|---|
| range | Cells to evaluate against the criterion. | A2:A100 |
| criterion | Condition expressed as a number, expression, text, or cell reference. It can include comparison operators (>, <, =, <>) and wildcards (* for any characters, ? for a single character). |
">=10" or B2 |
| sum_range (optional) | Cells that actually get summed. If omitted, Google Sheets sums the cells in range. | C2:C100 |
Simple example
| A (Category) | B (Amount) |
|---|---|
| Food | 12 |
| Travel | 45 |
| Food | 8 |
| Supplies | 20 |
=SUMIF(A2:A5, "Food", B2:B5)
Result: 20 (12 + 8).
If sum_range is omitted, the function would try to add the matching cells from column A, which is rarely useful for numeric totals.
Key points
- One‑criterion only –
SUMIFhandles a single condition. For multiple criteria useSUMIFSor combine severalSUMIFs. - Wildcards –
*matches any sequence of characters,?matches a single character (e.g.,=SUMIF(A:A, "Food*")). - Comparison operators – Must be quoted, e.g.,
">100"or"<"&D1. - Whole‑column references –
A:Aworks in Sheets, but text headers are simply ignored by SUMIF; keeprangeandsum_rangethe same size.
Setting Up Multiple Criteria
Because SUMIF accepts only one condition, you have two practical ways to achieve “multiple‑criteria” sums:
1. Add several SUMIFs together (for OR logic)
To total rows that match either of two values, add two SUMIFs:
=SUMIF(A2:A10, "West", C2:C10) + SUMIF(A2:A10, "East", C2:C10)
Nesting one SUMIF inside another does not work, because the inner result is a single number, not a range.
2. Preferred method (AND logic) – SUMIFS (Google Sheets & Excel 365/2021/2007+)
SUMIFS accepts any number of criteria_range / criterion pairs, making it the cleanest solution for multi‑criteria totals.
=SUMIFS(C2:C10, A2:A10, "West", B2:B10, "Gadget")
| Argument | Meaning |
|---|---|
C2:C10 |
Sum range – numbers to add |
A2:A10 |
First criteria range (Region) |
"West" |
First criterion |
B2:B10 |
Second criteria range (Product) |
"Gadget" |
Second criterion |
Result: 320 (the two rows where Region = West and Product = Gadget).
SUMIFS works the same way in Excel and Sheets; the only difference is that Excel requires the function to be available in versions 2007 and later, while Sheets has it natively.
Using SUMIFS for Advanced Filtering
SUMIFS shines when you need to total values that satisfy three, four, or more conditions.
Syntax
SUMIFS(sum_range, criteria_range1, criterion1,
[criteria_range2, criterion2, …])
| Argument | Description |
|---|---|
| sum_range | Cells containing the numbers to add. |
| criteria_range | Range to evaluate against a criterion. |
| criterion | Condition (text, number, expression, or cell reference). |
| Additional pairs | Add as many criteria_range/criterion pairs as needed. |
Simple example
| A (Category) | B (Region) | C (Sales) |
|---|---|---|
| Food | East | 1200 |
| Food | West | 800 |
| Drink | East | 500 |
| Drink | West | 700 |
Goal: Sum sales for Food sold in the East region.
=SUMIFS(C2:C5, A2:A5, "Food", B2:B5, "East")
Result: 1200.
Dynamic criteria with cell references
| D1 (Category) | D2 (Region) |
|---|---|
| Food | East |
=SUMIFS(C2:C5, A2:A5, D1, B2:B5, D2)
Changing D1 or D2 instantly updates the total—no formula edits required.
Comparison operators
=SUMIFS(C2:C5, A2:A5, "Food", C2:C5, ">=1000")
Adds only Food sales that are at least 1,000.
“Or” logic
SUMIFS uses AND logic; to implement OR you can add separate SUMIFS results or use FILTER with SUM.
=SUM(FILTER(C2:C10, (A2:A10="Food") + (A2:A10="Drink")))
Practical Example: Budget Tracking
| Date | Category | Amount |
|---|---|---|
| 2024‑01‑05 | Food | 45.20 |
| 2024‑01‑12 | Transport | 12.50 |
| 2024‑01‑15 | Food | 27.80 |
| 2024‑02‑03 | Rent | 950.00 |
| 2024‑02‑07 | Food | 33.10 |
| 2024‑02‑14 | Transport | 15.00 |
| 2024‑02‑20 | Utilities | 78.25 |
Question: How much was spent on Food in February 2024?
=SUMIFS(C2:C8, B2:B8, "Food", A2:A8, ">="&DATE(2024,2,1), A2:A8, "<="&DATE(2024,2,29))
Result: 66.90 (27.80 + 33.10).
Tips for real‑world budget sheets
- Dynamic month selection: Store the start/end dates in cells (e.g.,
E1andE2) and reference them:">="&E1,"<="&E2. - True dates: Keep dates as date values, not text, so comparison operators work reliably.
- Case‑insensitivity:
SUMIFSignores case, but if you need case‑sensitive matching, combineEXACTwithFILTERandSUM.
Common Mistakes and How to Avoid Them
| Mistake | Why it Happens | Fix |
|---|---|---|
Using SUMIF instead of SUMIFS |
SUMIF only accepts one condition; extra arguments are ignored or cause errors. |
Switch to SUMIFS for any scenario with two or more criteria. |
| Putting criteria before the range | The function expects a range first; a literal criterion in that position yields #REF!. |
Always write SUMIFS(sum_range, criteria_range1, criterion1, …). |
| Mixing array literals and ranges | {A2:A5, B2:B5} creates a two‑column array that SUMIFS cannot interpret, resulting in #VALUE!. |
Keep each criteria_range as a single column or row. For multi‑column sums, use ARRAYFORMULA with MMULT or SUMPRODUCT. |
| Expecting case-sensitive matching | SUMIF/SUMIFS ignore case, including with wildcards. | Use SUM(FILTER(...EXACT(...))) for case-sensitive totals. |
FAQ
What is the difference between SUMIF and SUMIFS?
SUMIF handles a single condition, while SUMIFS accepts multiple range/criterion pairs, applying AND logic across all of them. Use SUMIF for quick one‑criterion totals; switch to SUMIFS whenever you need two or more conditions.
Can I use SUMIF with text and number criteria together?
Yes, but each SUMIF call can only contain one criterion. To combine a text condition with a numeric one you must either nest two SUMIFs (which is limited) or, more robustly, use SUMIFS where you can specify both a text and a numeric condition in the same formula.
How do I sum only rows where a cell is not blank?
Add a criterion of "<>" on that column, e.g. =SUMIFS(C2:C10, B2:B10, "<>").
Is there a limit to the number of criteria in SUMIFS?
Google Sheets does not publish a low limit; Excel allows up to 127 criteria_range/criterion pairs, which is far beyond typical use cases. Performance may degrade with extremely large datasets, but the function itself imposes no low‑count limit.
Can I combine SUMIF with other functions like ARRAYFORMULA?
Yes. In Google Sheets you can wrap SUMIF (or SUMIFS) inside ARRAYFORMULA to apply the function across multiple rows or to generate a series of subtotals. For example:
=ARRAYFORMULA(SUMIF(A2:A, UNIQUE(A2:A), B2:B))
This returns a column of totals for each unique value in column A. Note that ARRAYFORMULA is Sheets‑only; in Excel you would use dynamic array formulas (e.g., =SUMIF(A2:A, UNIQUE(A2:A), B2:B)) available in Excel 365/2021.