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

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

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  Register To Reply

2. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

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  Register To Reply

3. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

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.  Register To Reply

4. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

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?  Register To Reply

5. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

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

6. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

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.  Register To Reply

7. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

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.  Register To Reply

8. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

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

9. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

And that was it. Thank you for the assist!  Register To Reply

10. ## Re: Excel 2016 - SUM Function Doesn't Add but + Does

You're welcome & thanks for the feedback  Register To Reply