PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
2 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel 2010 SUM Function not Working Properly
Posted on 2013-11-08
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
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
- +1
6 Comments
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
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
Jeremy,
It is for sure Error on numbers stored as text.
As Barry said, Text To Columns is the quickest way to fix that.
It is for sure Error on numbers stored as text.
As Barry said, Text To Columns is the quickest way to fix that.
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.
Just select the entire range, click on that error popup and then click on convert to number. I think this will solve your problem.
Active today
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
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
Active today
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
=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
Featured Post
For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month2 days, 11 hours left to enroll
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.