Sum formula returning zero value

[Teresa Devlin]

I am trying to sum a column with values using a report downloaded from Celltracker, however the sum total is returning as zero. When added using =a1+a2 the value returns correctly.

 

[crimson_b1ade]

format the cells as numbers. not as text.

 

[Teresa Devlin]

Thanks, but that isn't working either

 

[crimson_b1ade]

you may need to convert your values stored as text to values stored as numbers using the VALUE() function. You can also try this array function (confirmed with CTRL+SHIFT then press ENTER):

=SUM(VALUE(A1:A10))

 

[Domenic]

ADVERTISEMENT

Or, convert the numbers to numerical values...

1) Select an empty cell

2) Edit > Copy

3) Select the column/cells

4) Edit > Paste Special > Add > Ok

 

[Teresa Devlin]

thanks but I've tried that, I've even tried to save the numbers in a different worksheet and I'm still getting the same problem

 

[crimson_b1ade]

ADVERTISEMENT

maybe upload your file using a free online storage site and post the link here. maybe someone can take a look at it.

I don't know what else may be the problem.

 

[crimson_b1ade]

Have you tried Domenic's suggested solution?

 

[Teresa Devlin]

Thanks, I'm pushed for time at the moment, will maybe try to upload later if your suggestions don't work, now going to try =sum(value(a1:a10)

thanks for your help

 

[Jon von der Heyden]

Hi Teresa

The solution provided by Domenic should work, but perhaps try the following alternative.

Highlight column A
Go to Format Cells and change it to number format.
Go to Edit > Text to columns
- choose delimited and click 'Next'
- remove all checks against any delimiters and click 'Finish'

Now try your original SUM.