asked on

Excel 2010 SUM Function not Working Properly

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

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Harry Lee

Jeremy,

It is for sure Error on numbers stored as text.

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

Swapnil Nirmal

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.

Rob Henson

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

Rob Henson

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

Rob Henson

Will Barry's solution not overwrite your reference to the source data with values?