Numbers stored as text break SUM and lookups. Convert them with the error tag, VALUE, or Text to Columns.
Do it with AI insteadSpot the issue
Text-numbers align left and may show a green triangle.
Quick fix
Select the cells, click the warning icon, and choose Convert to Number.
Formula fix
Use =VALUE(A2), or multiply by 1: =A2*1.
=VALUE(A2)They're text — convert them first; SUM ignores text values.