When SUMIF or SUM returns 0 on a column of amounts that clearly have numbers in them, the cells are almost always text that looks like numbers. This is common after you paste or import a bank CSV. Confirm with =ISNUMBER(C2), which says FALSE for text, and then convert the column with Text to Columns, VALUE or multiply by 1.
How can I tell the numbers are really text?
- They are left-aligned in the cell while real numbers sit to the right.
- A small green triangle shows in the corner of the cell, with a warning about a number stored as text.
- =ISNUMBER(C2) returns FALSE.
- The status bar shows a Count but no Sum when you select the column.
- =SUMPRODUCT(--ISNUMBER($C$2:$C$500)) counts how many cells are real numbers. Compare it with the number of rows.
Text amounts creep in when a file comes with a leading apostrophe, spaces around the number, commas in a format Excel does not recognise, or a suffix such as Dr or Cr stuck to the figure.
What are the fixes, from quickest to most flexible?
Text to Columns
Select the amount column, go to Data, then Text to Columns, and click Finish on the first screen. Excel re-reads each cell and converts those that look like numbers. This is the fastest fix for a single column.
Multiply by 1
Type 1 in an empty cell and copy it. Select the amounts, choose Paste Special, then Multiply. Text that looks like a number becomes a number. The same idea works in a helper column: =C2*1 or =--C2.
VALUE in a helper column
In D2 enter =VALUE(TRIM(C2)). TRIM removes stray spaces, VALUE converts the rest. Fill down, then use column D in your formulas, or paste it back over column C as values only.
What if the amount has Dr or Cr, or an odd space?
If a cell reads 1,250.00 Dr you can strip the suffix with =VALUE(SUBSTITUTE(C2," Dr","")). If TRIM does not remove a gap, the space may be a non-breaking space copied from a web page or PDF. Use =VALUE(SUBSTITUTE(TRIM(C2),CHAR(160),"")). Take a few rows and test before filling down the whole column.
Worked example with made-up rows
| Cell content | ISNUMBER says | Fix | Result |
|---|---|---|---|
| 1250 | FALSE (text) | =C2*1 | 1250 |
| " 980 " with spaces | FALSE | =VALUE(TRIM(C3)) | 980 |
| 2,400.50 | FALSE in some imports | Text to Columns | 2400.5 |
| 3200 (typed) | TRUE | none | 3200 |
Before the fix SUM skipped the three text rows and showed only ₹3,200. After the fix the total is ₹1,250 + ₹980 + ₹2,400.50 + ₹3,200 = ₹7,830.50.
Why does SUMIF still return 0 after the numbers are fixed?
The category column can be the real cause. If your criterion says "Food" and the cell holds "Food " with a trailing space, SUMIF finds no match and returns 0 even though the amounts are fine. Use a helper column with =TRIM(B2), or count matches with =COUNTIF($B$2:$B$500,"Food*"). A count of zero for "Food" but a nonzero count for "Food*" proves there is a hidden extra character.
How do I stop this happening next month?
- Keep a clean data tab and paste the raw statement into a separate tab.
- Build helper columns once with VALUE, TRIM and DATEVALUE, and point all reports at the helper columns.
- Run the ISNUMBER count after every paste. It takes ten seconds.
If cleaning every statement is the part you dislike, SpendixAI takes PDF, CSV or Excel statements and shows the rows for review before saving, which moves the clean-up out of your formulas.
One more trap is a column that is a mix. Some rows are real numbers because you typed them, and others are text because they were pasted. SUM then returns a total that is too small but not zero, which is harder to spot. The ISNUMBER count against the number of rows catches this. Whenever a total looks lower than you expect, run that count before you suspect the data.
Final checklist
- Amounts return TRUE for ISNUMBER.
- Categories have no trailing spaces.
- Calculation is set to Automatic.
- Totals match a hand-added filtered sample.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


