Why does SUMIFS return 0 with two criteria on the same column?
Excel formula
=SUM(SUMIFS(C2:C8,B2:B8,E2:E3))SUMIFS joins its criteria with AND, and a cell can't be East and West at once. Total each and add: =SUM(SUMIFS(C2:C8,B2:B8,E2:E3)).
- Result on the example
- $3,345 in
F2 - Works in
- Every current version
- Google Sheets
- Different formula, see below
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMIFS joins its conditions with AND, so B2:B8,"East",B2:B8,"West" asks for rows that are both, and the total is 0. Passing both regions as one criteria range, E2:E3, makes SUMIFS return one total per region, and SUM adds them: 3,345.
=SUM(SUMIFS(C2:C8,B2:B8,E2:E3))Result3345
C2:C87 rows × 1 col: $1,200, $850, $430, $990, …A range: 7 cells in one column.
B2:B87 rows × 1 col: "East", "West", "North", "East", …A range: 7 cells in one column.
E2:E32 rows × 1 col: "East", "West"A range: 2 cells in one column.
SUMIFS(C2:C8,B2:B8,E2:E3){2 rows × 1 col} 2190, 1155SUMIFS: Adds values that meet every condition.- sum_range
C2:C87 rows × 1 col: $1,200, $850, $430, $990, … - criteria_range1
B2:B87 rows × 1 col: "East", "West", "North", "East", … - criteria1
E2:E32 rows × 1 col: "East", "West"
- sum_range
SUM(SUMIFS(C2:C8,B2:B8,E2:E3))3345SUM: Adds numbers and ranges. Skips text and blanks.- number1
SUMIFS(C2:C8,B2:B8,E2:E3){2 rows × 1 col} 2190, 1155
- number1
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(SUM(SUMIFS(C2:C8,B2:B8,E2:E3))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(C2:C8,B2:B8,E2)+SUMIFS(C2:C8,B2:B8,E3) | $3,345 | One SUMIFS per region, added together. Works everywhere. |
| =SUMPRODUCT(((B2:B8=E2)+(B2:B8=E3))*C2:C8) | $3,345 | Adding the tests works as OR inside SUMPRODUCT. |
Common mistakes
- In Excel 2019 and earlier, confirm the SUM(SUMIFS(…)) form with Ctrl+Shift+Enter.
- OR across two different columns can count a row twice when it matches both. Within one column that can't happen.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does SUMIFS return 0 with two criteria on the same column
F2, total the amounts for either of the regions in E2:E3.Questions
Can I type the regions into the formula?
Yes, as an array constant: =SUM(SUMIFS(C2:C8,B2:B8,{"East","West"})).
How do I add another AND condition?
Add the pair as usual: =SUM(SUMIFS(C2:C8,B2:B8,E2:E3,A2:A8,"Ana")).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.