Why does SUMIF return 0 when the text looks the same?
Formula (Excel and Google Sheets)
=SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8)The data has hidden spaces, so "East " doesn't equal "East". Trim inside the formula: =SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8).
- Result on the example
- $2,965 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
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
The region names were imported with a trailing space, "East ", so SUMIF looking for "East" finds no exact match and returns 0. TRIM(B2:B8) removes the spaces inside the formula, and SUMPRODUCT adds the amounts on rows where the trimmed region matches: 2,965.
=SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8)Result2965
B2:B87 rows × 1 col: "East ", "West", "East ", "North", …A range: 7 cells in one column.
TRIM(B2:B8){7 rows × 1 col} "East", "West", "East", "North", …TRIM: Removes extra spaces, leaving single spaces between words.- text
B2:B87 rows × 1 col: "East ", "West", "East ", "North", …
- text
F1"East"The value in F1.
TRIM(B2:B8)=F1{7 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
C2:C87 rows × 1 col: $1,200, $850, $430, $990, …A range: 7 cells in one column.
TRIM(B2:B8)=F1)*C2:C8{7 rows × 1 col} 1200, 0, 430, 0, …The * operator multiplies.
SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8)2965SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
TRIM(B2:B8)=F1)*C2:C8{7 rows × 1 col} 1200, 0, 430, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8). We ran it in Google Sheets mode on the same data and it returns $2,965.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIF(B2:B8,F1&"*",C2:C8) | $2,965 | A trailing * also matches "East " with its space. It would match "Eastern" too. |
| =SUMIF(B2:B8,F1,C2:C8) | $0Different result | Shown for contrast: plain SUMIF only matches exact text. Returns 0, because every East label has a trailing space. |
Common mistakes
- SUMIF also returns 0 when the amounts are numbers stored as text. Check with
=COUNT(C2:C8). - Criteria with an operator need quotes and &:
">"&F1, not">F1". - Fix the data once with TRIM and Paste Values, and a plain SUMIF works again.
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 SUMIF return 0 when the text looks the same
F2, total the amounts for the region in F1, even though the region names in column B have trailing spaces.Questions
How do I spot hidden spaces?
=LEN(B2) shows 5 for "East ", one more than you expect.
Is SUMIF case-sensitive?
No: "east" and "East" match. Spaces are what break it.
Why doesn't TRIM fix it?
The space may be a non-breaking space from a web page, which TRIM leaves alone. Remove it with SUBSTITUTE(B2,CHAR(160),"").
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.