Excel 2016 - SUM Function Doesn't Add but + Does

[egoche]

I'm working on a worksheet where a simple addition problem isn't working.

Cell H11 contains the number 1. Cell K11 contains the number 1.

In cell L11 is the formula =SUM(H11,K11). The result of this formula is 1.

If I change the formula in L11 to =H11+K11, the result is 2.

I always try to use =SUM() but I may have to revert to the older +.

Any thoughts?

Eric

[dosydos]

sounds like cell H11 or K11 is in fact text and not a number. see if there is a little green mark in the top left hand corner of either of these cells. If so click on the cell and then click on the yellow Exclamation mark. Select convert to number

[MrShorty]

The SUM() function ignores text -- including numbers stored as text. The + operator cannot ignore text, so it coerces numbers stored as text to numbers. My guess -- without being able to see your workbook -- is that one of those 1s is the text string "1" and not the number 1. I would test with the ISTEXT() function.

[egoche]

Thank you for the quick responses.

dosydos...there's nothing to indicate any of the cells to be text. Selecting each cell shows that it's formatted as a number. No yellow exclamation point.

MrShorty...I ran the ISTEXT() function. Lo and behold, cell K11 is text. So now the question becomes, how is it text?

As I mentioned above, the cell is formatted as a number. That being said, cell K11 is a formula:

Please Login or Register  to view this content.

Is the problem caused by having the "N/A" in the formula?

[Fluff13]

What's in K15 on the "Weekly Hours" sheet?

[egoche]

Another formula:

Please Login or Register  to view this content.

J15 is a formula:

Please Login or Register  to view this content.

D15 through I15 are all numbers that are manually entered.

All cells are formatted as numbers.

[MrShorty]

Note the quotation marks in your IF() function -- "3.00" or "2.00". These are text strings, not numbers. Remove the quotation marks from those values in the IF() function so that they are seen as numbers and not text.

[Fluff13]

That formula is inserting text values, not numbers. You need to remove the quotes.

[egoche]

And that was it. Thank you for the assist!

[Fluff13]

You're welcome & thanks for the feedback