Fix Excel SUMIFS Returning Zero Due to Trailing Spaces
Question details
Identify and remove hidden spaces that prevent an Excel SUMIFS criterion from matching the source data.

- 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.
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.
Find and remove hidden spaces from SUMIFS values
Use this method when a criterion looks correct but does not exactly match the source text.
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.
In another empty cell, enter =A2=TRIM(A2), replacing A2 with the criterion cell. A FALSE result indicates that TRIM changes the value.
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.
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.
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.

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. Open the workbook: In WPS Spreadsheets, select Open and choose the Excel workbook containing the SUMIFS formula.
- 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. Clean the criterion: Edit the SUMIFS formula and replace the criterion reference with TRIM(SUBSTITUTE(A2,CHAR(160)," ")), using the actual cell reference.
- 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.

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.




