Excel 2016 Spreadsheet - Sum of numbers in a column is incorrecly calculated to be zero

Hi Experts,

I've got a problem with the attached Excel 2016 Spreadsheet.

I'm trying to do some calculations on the 2nd Sheet, "csn".

I want to Sum the numbers in Column B.

My formula is in cell B52 =  SUM(B2:B51).

Clearly it should be non-zero, but I am getting 0.0.

What am I overlooking?

Regards,

Leigh


AGPT02-17_AppE_TLD_VIC.XLSX
LVL 1
LeighWardleAsked:
Who is Participating?
 
Ares KurkluSoftware EngineerCommented:
Hi Leigh,

The numbers in the column are stored in text format therefore stopping the calculation, you would see green arrows in the corner, if you select the whole column without the heading you should be able to see an exclamation mark next to the cell, you can click into that and select 'Convert to Number'. This should solve your issue.

Ares
c1.PNG
0
 
Ryan ChongCommented:
you got to format the cells' value to Number first before the summation.

use formula like:

=VALUE(B2)

Open in new window


to do the conversion.
AGPT02-17_AppE_TLD_VIC_b.XLSX
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or you can try...
=SUMPRODUCT(VALUE(B2:B51))

Open in new window

0
 
LeighWardleAuthor Commented:
Thanks, Ares, that is the easiest solution.

Regards,
Leigh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.