Solved

Excel 2010 SUM Function not Working Properly

Posted on 2013-11-08
6
408 Views
Last Modified: 2013-11-11
I am totaling some US Census data when I use the SUM function for the range of cells "SUM(C4:C19)" the total in the cell is the number in cell B16; however, if I use the formula "B4+B5+B6+B7+B8+B9+B10+B11+B12+B13+B14+B15+B16+B17+B18+B19" the correct total appears in the cell.   I'm using referenced data from another workbook.

All source cells are formatted as numbers as are cell that are in the SUM RANGE.

The same problem occurs if I attempt to sum columns.

What's causing this and how can I fix it?

Thanks,

Jeremy


Charlestown Township            5,671
East Goshen Township          18,026
East Whiteland Township          10,650
Easttown Township                  10,477
Edgemont Township            3,987
Malvern Borough                    2,998
Marple Township                  23,428
Newtown Township                  12,216
Radnor Township                  31,531
Schuylkill Township                   8,516
Tredyffrin Township                 29,332
Upper Merion Township         28,395
West Goshen Township         21,866
West Whiteland Township 18,274
Westtown Township         10,827
Willistown Township         10,497
      
Total                                        28,395
0
Comment
Question by:Jeremy-M
6 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39634665
Hello Jeremy

It sounds like your numbers are mostly formatted as text - text formatted numbers are ignored by SUM function but using + will "co-erce" the text to numbers (and therefore include them in the result)

Test by using

=ISNUMBER(B4)

FALSE means B4 isn't a number

You can't change text values to numbers by changing the formatting, try using "text to columns"

Select the range of "numbers" then use

Data > Text to columns > Finish

now SUM should work

regards, barry
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39635060
Jeremy,

It is for sure Error on numbers stored as text.

As Barry said, Text To Columns is the quickest way to fix that.
0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 39635238
Do u find a green error sign in top left corner of cell? that means the number is entered as text.

Just select the entire range, click on that error popup and then click on convert to number. I think this will solve your problem.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39638585
As you are using data referenced from another workbook, it would seem that the data in the source workbook is text so n_swapnil's suggestion may not be applicable as you would end up overwriting the reference formulas.

I suspect you will have to chnage the source data or somehow force the sum to recognise the values as values, maybe using SUMPRODUCT.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39638593
Actually turns out to be simpler than that:

=SUM(C4:C19*1)

Confirm with Shift + Ctrl + Enter. In the formula bar it will then look like:

{=SUM(C4:C19*1)}

Copnverting to an array formula tells it to multiply each of the range by 1 before summing, thus converting to a number.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39638776
Will Barry's solution not overwrite your reference to the source data with values?
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question