Issue with SUM

[isabellar]

Hi all, I'm having an issue with the SUM formula. It won't add the range of cells I've asked it to, and I can't figure out why. I made sure they're formatted as numbers in Excel, there aren't any blank cells or hidden rows, so I'm at a loss as to why it won't work. I've checked manual versus automatic calculation settings and that also didn't work. Any idea of what I'm doing wrong here?

I'm trying to add the values from V3 - V29 (V3:V29). Here's my formula and the output.
The value I should be getting is much larger than 7.5. It seems to not be counting certain cells. Any idea how to fix this?

 

[shamimarita]

Hello Isabellar,

It looks like some of the values in your range are stored as text, even though they appear as numbers. When that happens, Excel ignores those cells in the SUM calculation. Formatting the cells as “Number” does not convert the underlying value.

The easiest way to fix this is to convert all text-numbers in V3:V29 into real numbers:

1. Select the entire range V3:V29.
2. Go to Data tab >> select Text to Columns >> click Finish.

This forces Excel to convert all text entries into actual numeric values.

You can also test a suspicious cell by checking its alignment. Real numbers align to the right; text aligns to the left. Another quick test is:

=ISNUMBER(V3)

• If it returns FALSE, the cell is text.

Once the cells are converted, your SUM(V3:V29) should calculate correctly and give you the larger total you expect.

If you want, you can share one or two of the cells that are not being counted, and I can confirm what is causing them to be treated as text.