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
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