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.

 

[doodleali]

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,.
View attachment 1838 fnf 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?

Most likely some cells in V3:V29 still contain text values or hidden spaces, so SUM skips them even if the format says Number. I’d test one of the missed cells with =ISNUMBER(V3) and then force-convert the range with Text to Columns or multiply by 1.

 

[shamimarita]

Hello doodleali,

Good point! Text-formatted numbers or hidden spaces are often the reason SUM doesn’t include certain cells. Using ISNUMBER is a great way to check, and converting with Text to Columns or multiplying by 1 usually fixes it quickly.

Also, another quick option is using VALUE() or --(cell) to coerce text into numbers if needed.

Thanks for sharing your insight!