# Why does SUMIF return 0 when the text looks the same?

The data has hidden spaces, so "East " doesn't equal "East". Trim inside the formula: =SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8).

**Excel:** `=SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Rep | Region | Amount |  | Region | East |
| Ana | East | 1200 |  | Total |  |
| Ben | West | 850 |  |  |  |
| Cara | East | 430 |  |  |  |
| Dev | North | 990 |  |  |  |
| Eli | East | 615 |  |  |  |
| Fay | West | 305 |  |  |  |
| Gus | East | 720 |  |  |  |

Formula in F2: `=SUMPRODUCT((TRIM(B2:B8)=F1)*C2:C8)`

Result: $2,965

## 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.

## Other ways

- `=SUMIF(B2:B8,F1&"*",C2:C8)`: A trailing * also matches "East " with its space. It would match "Eastern" too.
- `=SUMIF(B2:B8,F1,C2:C8)`: 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.

## 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),"")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sumif-returns-zero