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:

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: Adding Values with Formulas Attached

2. 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.

Like

• MF says:

Perhaps there is too many resource-eating formula in your file….
Anyway, don’t be “一朝被蛇咬…”. 🙂

Like

3. Jeffu says:

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

Like

• 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:
http://msdn.microsoft.com/en-us/library/ff700515(v=office.14).aspx

Hope this help! 🙂

Like

4. Jeffu says:

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

Fung Wong =SUM(IFERROR(B2:B6,0)) CTRL SHIFT ENTER

Like

5. Jeffu says:

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

Like