Numbers won't auto SUM a column in which the cells are an IF calculation

The SUM function ignores text, treating it as if it wasn't there. By putting "$80.00" and "0" in quotes, you made them text strings. Therefore, when you sum them you will always get zero.

Jerry

AB2302 wrote:

I'm confused! Why will it see it as a number if I format the SUM box like: =SUM(B3+B4+B5...)? Why would it see it as a number in one instance of SUM and not in another instance of SUM?

SUM interprets text as having a numerical value of zero. The arithmetic operators (+, -, *, /) interpret text as numerical values IF they can do so.

When you use an arithmetic expression, such as B3+B4+B5, as an argument in a function (such as SUM), the expression is evaluated first, and that value is used as the argument. So if B3, B4 and B5 contained the (TEXT) values "1", "2" and "3", The expression B3+B4+B5 would return the numeric value 6, and that value would be used as the first (and only) argument for the SUM function. SUM(6) would return the numeric value 6.

That's the 'What' answer. For the 'Why' answer, you'll need to talk to the programmers. Not the ones who wrote the code for Numbers, but the ones who originally set up the SUM function and the arithmetic operators. Failing an answer from them, the best one available is along the lines of "Because I'm the Mom!" or, in a different context, "That's how it is. Live with it."

Regards,

Barry

Hello Barry

May you explain how some one may guess that he must use the syntax =SUM(B3+B4+B5) ?

It means in fact : calculate the sum of (B3+B4+B5) and nothing.

Given the description available in iWork Formulas and Functions User Guide,

logical formulas would be :

=B3+B4+B5

or

=SUM(B3, B4, B5)

or

=SUM(B3:B5)

Upon the OP's question: Why would it see… ?

The answer is simple:

in one case the value is a string (aka a piece of text)

in the other one, it's a numerical value (stored using the IEEE specs)

Would some one be surprised to be unable to sum:

azerty and 123.456

or

1ØØ.34 and 100.34

What is surprising, at least for me, is the fact that if we insert a chunk of characters like which are Unicode ones in the range $1D7D0…, Numbers will decipher them as standard digits.

If B2 contains ="10"

and B3 contains ="100"

=SUM(B2;B3) fails because

as the app claims that the cells are containing strings, it doesn't try to decipher them.

If the formula is

=SUM("10","100")

or

="10"+"100"

it return 110.

In this case, as the active function (SUM or + ) is designed to treat numbers, the app does its best to decipher parameters as numbers.

I disagree with the sentences :

SUM interprets text as having a numerical value of zero. The arithmetic operators (+, -, *, /) interpret text as numerical values IF they can do so.

In fact it just drop them exactly as the function COUNT does.

It's important because it's why we may apply SUM to a range of cells containing numbers and literal strings.

In such a case the operator + would fail.

B3 : ="10"

B4 : ="dog"

B5 : ="100"

=SUM(B3:B4) returns 110

=B3+ B4 + B5 returns the red triangle.

Yvan KOENIG (VALLAURIS, France) samedi 21 mai 2011 10:49:29

Please :

Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

AB2302 wrote:

I'm confused! Why will it see it as a number if I format the SUM box like: =SUM(B3+B4+B5...)? Why would it see it as a number in one instance of SUM and not in another instance of SUM?

AB2302,

SUM ignores text strings because it is designed to operate on large ranges that may contain a combination of text and numeric values. The creators of spreadsheet compilers recognize that when you ask for the sum of an entire column you generally want the sum of the numbers only, ignoring text that may look like a number. B3 + B4 + B5 is a different situation. Here you are pointing to explicit single cells and probably want to do arithmetic on those cells regardless of whether they happen to be formatted as text or numbers.

I'm confused too. I give you a a correct answer and you complain that it doesn't make sense. Whether it makes sense to you or not should have no bearing on the accuracy of the answer. You now know how to properly use both SUM and B3+B4+B5. As Yvan notes, wrapping the arithmetic in a function makes no sense except to prove you point that you find the behavior inconsistent. Rather that thinking of it as inconsistent, thing of it as expanding your options.

Jerry

AB2302 wrote:

Thank you!

You're welcome.

We can't know what you are thinking but not writing. Sometimes complaints come disguised as questions and no credit is given, much less thanks, for the time spent in formulating an answer. We aren't Apple and can't answer for the way things were designed. Even Apple has little choice but to design for compatibility with Excel, whether the function's behavior makes all the sense in the world, or not.

Jerry