Skip to content
Sheet ErrorSheet ErrorSheet Error

Fix SUMIF/COUNTIF missing a number in Google Sheets (CHAR(160))

Updated

Both rows look like sku 101 with qty 10, yet COUNTIF returns 1 and SUMIF returns 10. The formulas ran. They only matched one row. There is no red error triangle. The leftover character is CHAR(160), a non-breaking space from a web paste. TRIM does not remove it.

Recreate it on a blank sheet

Section titled “Recreate it on a blank sheet”
  1. Open a blank spreadsheet. Leave the tab named Sheet1. In A1 type sku, B1 qty.
  2. In A2 enter ="101"&CHAR(160). The cell looks like 101 and sits left-aligned. In A3 type 101 as a number. That 101 is right-aligned. B2 10. B3 10. Both quantities are numbers.
  3. In C1 type len, D1 trim_len, E1 countif, F1 sumif. In C2 enter =LEN(A2). The cell shows 4. In D2 enter =LEN(TRIM(A2)). That cell is still 4. In E2 enter =COUNTIF(A2:A3,101). The cell shows 1. In F2 enter =SUMIF(A2:A3,101,B2:B3). The cell shows 10. None of those four cells has a red error triangle.
E2 selected. Formula bar shows =COUNTIF(A2,101). E2 is 1. A2 is left-aligned 101. A3 is right-aligned 101. LEN is 4. LEN of TRIM is still 4. SUMIF is 10.
E2 selected. Formula bar shows =COUNTIF(A2,101). E2 is 1. A2 is left-aligned 101. A3 is right-aligned 101. LEN is 4. LEN of TRIM is still 4. SUMIF is 10.
  1. TRIM did not strip the extra character. In J2 enter =VALUE(TRIM(A2)). That cell is #VALUE!. Do not use this as the cleanup.

Leave E2, F2, and J2 broken so the miss stays on the sheet.

Second recreate — an ordinary trailing space, which is not this bug. In A5 enter ="101"&CHAR(32) (a normal space). B5 10. In C5 enter =LEN(A5). The cell shows 4. In D5 enter =LEN(TRIM(A5)). The cell shows 3. TRIM worked. That drop is the control: ordinary CHAR(32) is VLOOKUP no match / a TRIM cleanup. Stay on A2 when LEN and LEN(TRIM(...)) are both 4. That is CHAR(160).

What is actually wrong

Section titled “What is actually wrong”

A2 is the digits 101 plus CHAR(160), a non-breaking space, often left over from a web paste. It looks like 101. LEN is 4. TRIM removes ordinary spaces (CHAR(32)). It does not remove CHAR(160), so LEN(TRIM(A2)) stays 4. COUNTIF and SUMIF looking for the number 101 match only A3. A2 is a different value. Qty is not missing. B2 is 10. The criteria never included A2.

This is not '101 stored as text. Sheets coerces that apostrophe-text in COUNTIF / SUMIF. Type '101 in a scratch cell and COUNTIF of 101 will usually count it. A leading apostrophe would not reproduce this miss. ISTEXT(A2) is TRUE, but the reason is the extra character, not the apostrophe trick from VLOOKUP no match.

This is not a VLOOKUP trailing space (apple vs apple ). That space is CHAR(32). TRIM fixes it, and LEN drops. See that page when LEN(TRIM(...)) is already smaller than LEN. This page is when both stay 4.

This is not COUNTIFS / SUMIFS with ranges of different heights. That is #VALUE! (or a quiet 0 if the ranges are the same size but shifted). See COUNTIFS range size. E2 is a quiet 1, not an error value.

This is not a CSV import type split by itself. Import can also leave ids as numbers while the lookup stays text. See CSV text vs number. Check CHAR(160) when LEN is one too high and TRIM does not move.

Fix

Section titled “Fix”

Leave E2 and F2 as the broken formulas so the miss stays on the sheet.

Do not stop at VALUE(TRIM(...)). That still hits the non-breaking space and returns #VALUE!. J2 is that failure.

Clean the sku. In G1 type sku_clean. In G2 enter =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) and fill down through G3. Both rows become the number 101. A3 was already a number; it stays 101. SUBSTITUTE is what removes CHAR(160). TRIM then removes any ordinary spaces. VALUE turns the leftover digits into a number so COUNTIF(...,101) can match.

Then count and sum the cleaned column. In H1 type countif_ok, I1 sumif_ok. In H2 enter =COUNTIF(G2:G3,101). The cell shows 2. In I2 enter =SUMIF(G2:G3,101,B2:B3). The cell shows 20.

G2 selected. Formula bar shows VALUE TRIM SUBSTITUTE of A2 CHAR(160). G2 and G3 are 101. COUNTIF on the cleaned column is 2. SUMIF is 20. The original COUNTIF is still 1 and SUMIF is still 10.
G2 selected. Formula bar shows VALUE TRIM SUBSTITUTE of A2 CHAR(160). G2 and G3 are 101. COUNTIF on the cleaned column is 2. SUMIF is 20. The original COUNTIF is still 1 and SUMIF is still 10.

For a whole imported column, fill the helper down and point COUNTIF / SUMIF at the helper, not at A. Do not wrap E2 in IFERROR. There is no error to catch. The formula counted 1 because it was asked to.

SUBSTITUTE is what removes CHAR(160). TRIM alone does not.