SUM funtion not working

[hermithead]

Argh! So many brick walls lately!

A basic SUM function Im using is not calculating. If I type in A1+A2+A3+A4 then I get a result but if I use =SUM(A1:A4) It returns a zero.
The strange thing is its working fine on exactly the same other worksheets.

 

[Aladin Akyurek]

Argh! So many brick walls lately!

A basic SUM function Im using is not calculating. If I type in A1+A2+A3+A4 then I get a result but if I use =SUM(A1:A4) It returns a zero.
The strange thing is its working fine on exactly the same other worksheets.

A1:A4 appears to house text-formatted numbers, not true numbers.
Using + coerces text-numbers into true numbers, hence the non-zero result.

The following would support the observation:

=SUMPRODUCT(A1:A4+0)

Remedy:

Edit|Copy an unused cell.
Select A1:A4.
Run Edit|Paste Special > Add

 

[DonkeyOte]

SUM does not explicitly coerce unlike the + operator implication thus being that A1:A4 are numbers stored as text.

 

[hermithead]

Oh and calculation ISN'T set to manual. Cheers

 

[hermithead]

=SUMPRODUCT(A1:A4+0) - Worked perfectly! Thanks.

How does it explain the SUM function working in the four other worksheets with the same formulas and formats (just different data) ?

 

[DonkeyOte]

How does it explain the SUM function working in the four other worksheets with the same formulas and formats (just different data) ?

Yes, different data.

As outlined where SUM returns 0 the values being summed are not numbers per se - they are in fact Text Strings (numbers stored as text)

To illustrate by means of example...

Create a new sheet
Format A1:A2 to be Text (leave all other cells)
Enter 10 and 20 into A1 and A2 respectively
Enter 10 and 20 into B1 and B2 respectively

In A3 enter: =SUM(A1:A2) and copy across to B3

The result in A3 will be 0 and B3 will be 30

This is because A1:A2 are text strings (numbers stored as text) whereas B1:B2 are actual numbers.

In A4 enter: =A1+A2
Copy to B4

The result in both A4 & B4 will be 30.
The act of addition explicitly coerces the values to numbers, ie the text strings in A1 & A2 change from "10" and "20" to 10 and 20.

Now change A1 and B1 to Apple

You will now find row 4 is generating #VALUE! errors - this is because Apple can not be coerced to a number.
You will note that B3 however now returns 20 - this is because SUM does not try to coerce the text strings it simply ignores them.

Does that clarify things ?