Formula Errors

Fix Excel SUMIFS Returning Zero Due to Trailing Spaces

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

Identify and remove hidden spaces that prevent an Excel SUMIFS criterion from matching the source data.

Fix Excel SUMIFS Returning Zero Due to Trailing Spaces
Product
Microsoft Excel
Device & OS
not provided
Scenario
A SUMIFS formula in column AJ totals stock supplied to TTFj using data from an invoice analysis sheet.
Observed behavior
The formula returns zero because a criterion contains an invisible trailing space and does not match the source values.
Before you start

Select a cell in column AJ and inspect its SUMIFS formula to identify the criterion cell, criteria range, and sum range. Locate the corresponding TTFj values on the invoice analysis sheet.

Solution 1Recommended

Find and remove hidden spaces from SUMIFS values

Use this method when a criterion looks correct but does not exactly match the source text.

1
Check the criterion length

In an empty cell, enter =LEN(A2), replacing A2 with the criterion cell. Compare the result with the expected character count; TTFj has four characters.

2
Test for ordinary spaces

In another empty cell, enter =A2=TRIM(A2), replacing A2 with the criterion cell. A FALSE result indicates that TRIM changes the value.

3
Clean the criterion

Edit the SUMIFS formula in column AJ and replace the criterion reference A2 with TRIM(SUBSTITUTE(A2,CHAR(160)," ")), using the actual criterion cell reference. This handles ordinary spaces and common nonbreaking spaces.

4
Clean source values if needed

If the source values also contain spaces, add a helper column on the invoice analysis sheet. Enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) for the first source value, fill the formula down, and update the SUMIFS criteria range to use the helper column.

5
Verify the result

Recheck the formula in column AJ and compare its result with the matching invoice rows. If it still returns zero, check that the sum range contains numbers and that all criteria ranges align with the sum range.

Find and remove hidden spaces from SUMIFS values
Use wildcards cautiously: The criterion "TTFj*" can be a temporary workaround, but it also matches other text beginning with TTFj. Cleaning the values is more precise.
Fix SUMIFS in WPS

Clean SUMIFS Criteria in WPS Spreadsheets

Open the workbook in WPS Spreadsheets, check the criterion for extra spaces, and use TRIM and SUBSTITUTE to clean the values used by SUMIFS.

  1. 1. Open the workbook: In WPS Spreadsheets, select Open and choose the Excel workbook containing the SUMIFS formula.
  2. 2. Inspect the criterion: Select the SUMIFS cell to find its criterion reference, then enter =LEN(A2) in an empty cell, replacing A2 with that reference.
  3. 3. Clean the criterion: Edit the SUMIFS formula and replace the criterion reference with TRIM(SUBSTITUTE(A2,CHAR(160)," ")), using the actual cell reference.
  4. 4. Check the source data: If source values also contain spaces, use the same cleaning formula in a helper column, fill it down, and change the SUMIFS criteria range to that column.
Check and clean SUMIFS criteria with familiar spreadsheet functionsOpen and edit Microsoft Excel XLSX workbooksUse a familiar, easy-to-navigate spreadsheet interfaceWork with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMIFS return zero when the text looks identical?

A criterion or source value may contain an invisible trailing space. Use =LEN(A2) to check its length and =A2=TRIM(A2) to test whether ordinary spaces change the value.

Why doesn't TRIM remove every hidden space?

TRIM removes ordinary spaces but does not remove nonbreaking spaces by itself. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to replace common nonbreaking spaces before trimming.

Can I use a wildcard instead of cleaning the data?

Yes. A criterion such as "TTFj*" matches text beginning with TTFj, including values with trailing spaces. It can also match unintended longer values, so cleaning the text is preferable.

What should I check if SUMIFS still returns zero?

Inspect the SUMIFS formula to confirm the criteria and sum ranges cover corresponding rows. Also check that the sum range contains numbers rather than numbers stored as text.