SUM Function not working

Question

Hi, 

I am using Microsoft Excel 2013 and a very simple SUM function isn't working. (see picture below). I have set my calculations to automatic, all cells are in 'General Format' and I have turned on Iterative Calculations. The numbers being added up are from a reference from another sheet in the same workbook, for example ='Sheet1'!D39    ,is this the issue?

Answer 1

Hi,

Looking at you screencap we can see all the numbers are left justified which may suggest that despite the formatting they are text values. Try this:-

Put a 1 in a spare cell and select and copy that number. Select your numbers and then right click | paste special | select multiply and click OK and try your sum formula.

Answer 2

TheBriggsFamily wrote:

I am using Microsoft Excel 2013 and a very simple SUM function isn't working. (see picture below). I have set my calculations to automatic, all cells are in 'General Format' and I have turned on Iterative Calculations. The numbers being added up are from a reference from another sheet in the same workbook, for example ='Sheet1'!D39    ,is this the issue?

I suspect the "numbers" in columns E:G are actually text.  To confirm, what does =ISTEXT(E4) return, for example?

A short-term fix:

=SUMPRODUCT(--E4:Z4)

But it would be better to:  (a) convert existing numeric text to actual numbers; and (b) avoid the problem, in the first place.

For #A, select each column of data (e.g. Sheet1!D1:D100), click Data / Text To Columns, then click Finish and OK.

For #B, that will depend on how you acquired the data initially.  If you copy-and-pasted from a web page, it might suffice to paste into Notepad first, then copy-and-paste from Notepad into Excel.

PS....  I suggest that you disable Iterative Calculation.  It will mask any circular references that you create inadvertently.  And no, the reference to Sheet1!D39 is not an issue.

Answer 3

TheBriggsFamily wrote:

I am using Microsoft Excel 2013 and a very simple SUM function isn't working. (see picture below). I have set my calculations to automatic, all cells are in 'General Format' and I have turned on Iterative Calculations. The numbers being added up are from a reference from another sheet in the same workbook, for example ='Sheet1'!D39    ,is this the issue?

I suspect the "numbers" in columns E:G are actually text.  To confirm, what does =ISTEXT(E4) return, for example?

A short-term fix:

=SUMPRODUCT(--E4:Z4)

But it would be better to:  (a) convert existing numeric text to actual numbers; and (b) avoid the problem, in the first place.

For #A, select each column of data (e.g. Sheet1!D1:D100), click Data / Text To Columns, then click Finish and OK.

For #B, that will depend on how you acquired the data initially.  If you copy-and-pasted from a web page, it might suffice to paste into Notepad first, then copy-and-paste from Notepad into Excel.

PS....  I suggest that you disable Iterative Calculation.  It will mask any circular references that you create inadvertently.  And no, the reference to Sheet1!D39 is not an issue.

Hi,

thanks for the quick reply but neither the short term fix or fix A worked, and I am unable to find a work around. The result is still 0, so do you have any other ideas?

Also the result of the formula, =ISTEXT(E4) is TRUE.

Thanks.

Answer 4

TheBriggsFamily wrote:

thanks for the quick reply but neither the short term fix or fix A worked, and I am unable to find a work around. The result is still 0, so do you have any other ideas?

Also the result of the formula, =ISTEXT(E4) is TRUE.

The SUMPRODUCT should have worked, even if the numeric text is surrounded by (normal) spaces.  There might be nonbreaking spaces (HTML &nbsp).

As an(other) short-term fix, try array-entering the following (press ctrl+shift+Enter instead of just Enter):

=SUM(IFERROR(--SUBSTITUTE(E4:Z4,CHAR(160),""),0))

If that does not suffice, upload an example Excel file that demonstrates the problem to a file-sharing website, and post the share/public URL in a response here.  Here is a list of some free file-sharing websites:

Dropbox: http://dropbox.com

Box.Net: http://www.box.net/files

Windows OneDrive: http://onedrive.live.com

Again, the better long-term solution is to avoid the problem, in the first place.

Answer 5

Hi,

There are two problems in that worksheet

1. The numbers are stored as text; and
2. There is a circular reference

Fore more specific help, upload your workbook to OneDrive and share the link of the workbook here.