Cell not summing properly

[favorgirl]

Here are the values in my cells:

J3 = 10
J4 = 10
J5 = 10

I am trying to insert a formula that will total these values in cell B6. Of course I am using =SUM(J3:J5); however, the result returned is 10. The result suppose to be 30. I cannot figure out why this cell will not sum these values.

Thanks in advance for all and any assistance!

[daddylonglegs]

SUM will ignore any text formatted values - try testing if those cells contain text or numbers e.g.

=ISNUMBER(J3)

How do you get those numbers in J3:J5 are they results of formulas or input values?

[Harribone]

That should work, try =J3+J4+J5 instead. If that doesn't work then there's something you're doing wrong like cell references are wrong.

If not working still upload a copy of your sheet (change any sensitive data if needed) and we can have a look if it is something else.

[favorgirl]

Thanks for your reply. I tested the numbers and they are true numbers. Regardless of what I set my format to, it just will not sum for whatever reason. The numbers in J3:J5 are raw input values. I've never seen Excel behave this way...interesting stuff.

[tgsekhar]

Check the format of all cells. Only one of the cell will be numeric compatible format such as General,Number, etc. Other two cells might have got formatted as Text or any other format not compatible to numbers. You can check the format of the cell by RightClick -> FormatCells

[daddylonglegs]

Does this work?

=SUMPRODUCT(J3:J5+0)

[favorgirl]

Yes, this works!!! Why am I having to use this function vs. SUM?

[daddylonglegs]

It means some of your cells are formatted as text as I suggested - you can try to convert to numbers

Select J3:J5 > Data > Text to columns > Finish

Now SUM should work

[favorgirl]

Thanks a bunch!