how to fix a formula that returns 0

Marisa 0 Reputation points
2026-09-05T20:21:57.8333333+00:00

I created a simple formula using the sum function but the formula cell is showing 0.00

The formula: =SUM(H3:H197)

That the formula returns 0

ISNUMBER(H3) returns FALSE

VALUE(H3) returns 342

LEN(H3) returns 3

The fact that changing the format to Number/General did not resolve the issue

Microsoft 365 and Office | Excel | Other | Windows
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Barry Schwarz 6,106 Reputation points
    2026-09-06T06:01:20.9533333+00:00

    To convert the text values to numeric in mass:

    • Find an empty column (for example X)
    • In X3, enter the formula =VALUE(H3)
    • Copy this formula down to X197
    • Select cells X3:X197
    • Press Ctrl-C (for copy)
    • Select cell H3
    • Press Alt-E S V (for paste special values)
    • Confirm ISNUMBER(H3) returns TRUE
    • Delete the data in column X

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2026-09-05T23:23:19.9866667+00:00

    Hi,

    Select that single column range of numbers and go to Data > Text To Columns > Finish.

    Was this answer helpful?

    0 comments
  3. Marisa 0 Reputation points
    2026-09-05T20:27:59.58+00:00
    1. Convert the source cells to real numbers
      • Select H3:H197.
      • Change the format to General.
      • Press F2 to put a cell in edit mode, then press Enter to accept it again.
      • Repeat for the range as needed after changing the format.

    This solution worked for one cell only. I have 1,182 cells. Do I have to do each cell one at a time? I tried selecting all of them and it only converted one cell.

    Was this answer helpful?

  4. AI answer

    2026-09-05T20:22:14.5566667+00:00

    ISNUMBER(H3)=FALSE, VALUE(H3)=342, and LEN(H3)=3 indicate the cells likely contain numbers stored as text. SUM(H3:H197) ignores text values, so the result can be 0.00 even when the cells look numeric.

    Try these checks and fixes:

    1. Confirm the cells are text, not real numbers
      • ISNUMBER(H3) returning FALSE already points to that.
      • VALUE(H3) returning 342 shows Excel can convert the text to a number.
    2. Convert the source cells to real numbers
      • Select H3:H197.
      • Change the format to General.
      • Press F2 to put a cell in edit mode, then press Enter to accept it again.
      • Repeat for the range as needed after changing the format.
    3. Remove hidden space-like characters if conversion still fails
      • Copy one “space looking” character from one of the affected cells.
      • Select H3:H197.
      • Press Ctrl+H.
      • In Find what, press Ctrl+V.
      • Select Replace All.
      • Then try the SUM formula again.
    4. Check for mixed data types in the range
      • Excel can return unexpected results when formulas use cells with different data types.
      • After cleaning the cells, test again with =SUM(H3:H197).
    5. Check for a circular reference if other formulas in the workbook also return 0 unexpectedly
      • Circular references can cause calculations to return 0.
      • If this issue is happening beyond just column H, inspect the workbook for circular references.

    If the values in H3:H197 are converted from text to real numbers, =SUM(H3:H197) should calculate normally.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.