+ – 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
wmfexcel 
Pingback: 15-significant-digit issue with SUMIF(S), COUNTIF(S), AVERAGEIF(S) | wmfexcel
Pingback: Adding Values with Formulas Attached
Pingback: SUM across different worksheets (aka 3D SUM) | wmfexcel
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.
LikeLike
Perhaps there is too many resource-eating formula in your file….
Anyway, don’t be “一朝被蛇咬…”. 🙂
LikeLike
現實工作, 我是絕對, repeat絕對 唔會用 SUMIF, 我試過無論file 有幾大 (20K – 120M), 當你有過萬行data, 若你改任何一個data, SUMIF 就”計一次”, 每次約1分鍾,我試過最高記錄用成個下午run一個有SUMIF 的file, 拆開幾個 columns 之後, 10分鍾唔使就做完, 你話我幾咁”痛恨” SUMIF, 囹圄40 column data , 拆50行formula 都不用SUMIF
LikeLike
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! 🙂
LikeLike
Fung Wong =SUMIF(B2:B6,”>=”&-9.9E+307)
Fung Wong =SUM(IFERROR(B2:B6,0)) CTRL SHIFT ENTER
LikeLike
For solving those “not number” Error, Column D: =ISNUMBER(C3); Column E: =IF(D3=TRUE,C3*1,0), then Sum Column E
LikeLike