summing - formula acting different than function

I'm adding values from three cells:

Case 1:

A1: 1 (number)
A2: 2 (text)
A3: 3 (number)

Formula

Please Login or Register  to view this content.

gives result of 6.
Function

Please Login or Register  to view this content.

gives result of 4.

Case 2:

A1: 1 (number)
A2: 2 (text)
A3: r (number)

Formula

Please Login or Register  to view this content.

gives ERROR.
Function

Please Login or Register  to view this content.

gives result of 1.


Why do they act differently? What's the logic behind this behavior?

If you carry out arithmetic on a text value, then Excel will try to evaluate it as a number, so =1+"1" will result in 2. But, if it doesn't make sense to do that, e.g. =1+"a" then Excel will return an error (#VALUE!), as the things you are trying to add do not have a numeric value.

The SUM function ignores text values, which is why you get 4 in your first case and 1 in the second.

Hope this helps.

Pete

Let’s refer to article in the following thread which discusses about a similar and see if it helps:

https://excel.tips.net/T002582_Combi...in_a_Cell.html

https://www.youtube.com/watch?v=r0dIvaJRjxE