Excel Basic – Sum vs. “+”

+ – x / would be the four basic operators you learned in Excel basic.  When you learned to use SUM instead of +, did you ask yourself or your teacher why?

1) SUM is much easier to input and edit.

Obviously =SUM(B2:B6) contains fewer characters than =C2+C3+C4+C5+C6
Not obviously enough??? Try =sum(B2:F6) by using =B1+B2+B3+B4+B5+B6+C1+C2……

2) SUM ignores text


Like two sides of a coin… SUM does not handle “Number stored as Text” while + can convert “Number stored as Text” to number and give you the answer:
Excel Tips - Sum 7

3) SUM expands and contracts automatically – giving you flexibility
ROW 3 was deleted.

  • SUM changes the range from B2:B6 to B2:B5 automatically.
  • + shows #REF! as one of the reference was deleted.

ROW 4 was inserted.  2.5 was input.

  • SUM changes the ranges from B2:B6 to B2:B7 automatically – giving you updated result
  • + shifts the cell reference below row 4 by 1, giving you the “original result”; and missing the newly inserted data

Nevertheless, both SUM and + do not accept error.


So which one is better? well, it depends… ;p

This entry was posted in Excel Basic, Formula and tagged . Bookmark the permalink.

9 Responses to Excel Basic – Sum vs. “+”

  1. Pingback: 15-significant-digit issue with SUMIF(S), COUNTIF(S), AVERAGEIF(S) | wmfexcel

  2. Pingback: Adding Values with Formulas Attached

  3. Pingback: SUM across different worksheets (aka 3D SUM) | wmfexcel

  4. Jeffu says:

    Under the example, as a master data file which consolidated 6+ different excel file and some matching, calculating and other functions. These formula (included IF, INDEX, MATCH, Vlockup, etc) definitively affect the speed. I have tried many ways to SUIT my old stone-aged computer and 2003 Excel. To certain extent, this ”痛恨” is covered my computer.


  5. Jeffu says:

    現實工作, 我是絕對, repeat絕對 唔會用 SUMIF, 我試過無論file 有幾大 (20K – 120M), 當你有過萬行data, 若你改任何一個data, SUMIF 就”計一次”, 每次約1分鍾,我試過最高記錄用成個下午run一個有SUMIF 的file, 拆開幾個 columns 之後, 10分鍾唔使就做完, 你話我幾咁”痛恨” SUMIF, 囹圄40 column data , 拆50行formula 都不用SUMIF


    • MF says:

      Thanks so much for your comment.
      SUMIF is a non-volatile function, which should not have significant impact on lowering calculation performance when you make a change on the worksheet.

      I have tried SUMIF for a table of 50,000 rows and 10 columns, no delay in calculation is observed. I think, in you case, the delay could be caused by some other functions that you were not aware of.

      Take a look at this if you have time:

      Hope this help!🙂


  6. Jeffu says:

    Fung Wong =SUMIF(B2:B6,”>=”&-9.9E+307)



  7. Jeffu says:

    For solving those “not number” Error, Column D: =ISNUMBER(C3); Column E: =IF(D3=TRUE,C3*1,0), then Sum Column E


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s