Skip to content
Sheet ErrorSheet ErrorSheet Error

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

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.

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.

  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.

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.
  • This is not '101 stored as text. Sheets coerces that apostrophe-text in COUNTIF / SUMIF. A leading apostrophe would not reproduce this miss.

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

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.

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.

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