0

The + operator in Excel gives an error when adding two cells whose values are empty text, while the SUM function treats these cells as zero (as I expected). I am trying to understand if this is the intended operation for the + operation or a bug I need to report. If it is the intended operation for the + operator, what is the advantage of or reason for this difference?

To reproduce, I have a spreadsheet with ="" in cells C1 and D1. Cell A1 has the formula =C1+D1, while cell B1 has the formula =SUM(C1,D1). The spreadsheet is otherwise empty. The result is a #VALUE! error in cell A1, and 0 in cell B1. My expectation was that both would be 0.

enter image description here

I am using Excel for Mac 16.84 (24041420) from an Office 365 subscription running on macOS Sonoma 14.4.1.

Improve this question
3
  • 3
    SUM() function ignores Text values and will give you the sum of numeric values only, and hence why it returns 0 while using + operator it returns error because there are non-numeric text values and thus returns an error.
    – Mayukh Bhattacharya
    Commented May 13 at 14:29
  • 2
    If you want to use + operator and your data may probably be a text you can use N function as number filter to avoid errors. =N(C1)+N(D1). N converts any values (except errors) to zero if they are not numbers. However, if you have text-formatted numbers, then if you simply add them =C1+D1, they will be added as numbers, while both SUM and N will treat them as 0.
    – MGonet
    Commented May 13 at 20:38
  • Here is a similar thread: stackoverflow.com/questions/61838009/…
    – Emily
    Commented May 14 at 7:53

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .