SUM or +

[methody]

Hi
I have one very large range of data and another of the same size. I need to add the corresponding cells in each range to create another range of the same size with the sum of the corresponding cells.

I am currently using a simple SUM(A1, A10000) function. In terms of the speed and size of the file is it better to use =A1+A10000.

thanks

 

[jasonb75]

SUM(A1, A10000) is more reliable, A1+A10000 will give you a #VALUE! error if either cell contains text (including formula blanks).

In terms of speed and size, the difference (if any) will likely be negligible.

 

[joeu2004]

SUM(A1, A10000) is more reliable, A1+A10000 will give you a #VALUE! error if either cell contains text (including formula blanks).

Not entirely correct. As usual, the answer is: it depends.

Consider an example where A1 contains the text "123000" and A2 contains the text "456".

SUM(A1,A2) results in zero, whereas A1+A2 results in the number 123456.

So, the plus operator ("+") works with text that Excel can interpret as a number (includes some forms of currency, dates, times and percentages).

The SUM function ignores all text, including text that could be interpreted as a number.

 

[jasonb75]

Not entirely correct.

It was entirely correct as stated. I said "if either cell contains text", not "if either cell contains a number formatted as text"

Using your interpretation, neither of the methods is appropriate. The SUM function will ignore "numbers formatted as text" whilst A1+B1 will error if the text cannot be coerced into a numeric value.

If we are to allow for all possibilities then it would need to be more like =IFERROR(VALUE(A1),0)+IFERROR(VALUE(B1),0)

As with many threads, we could spend all day looking for things that are not there. I tend to omit bad problems that could be caused by bad practice, such as numbers formatted as text, ( in simple questions from established members) unless there is an obvious reason to allow for them. In this case, either errors, or zero results when values are expected would stand out and prompt the OP to ask additional questions.

 

[methody]

thank you folks