SUM funtion not working

hermithead

New Member
Joined
Sep 7, 2009
Messages
37
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
SUM does not explicitly coerce unlike the + operator implication thus being that A1:A4 are numbers stored as text.
 

hermithead

New Member
Joined
Sep 7, 2009
Messages
37
=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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,401
Messages
5,595,939
Members
414,034
Latest member
torreyanderson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top