Search code examples
sqlstringexcelexcel-formulaexcel-2007

AutoSum and Sum return '0' when trying to add numbers imported by SQL

Excel 2007 - anytime I click AutoSum it shows 0, or anytime I use the formula to Sum it still gives me a 0.

This is a SQL Query that is imported by using Data-Connection-SQL Server and saved SQL Query in the workbook.

What gives that is keeping me from totalling?

Solution

  • It seems to me the most likely explanation is that what you are trying to add are strings. If so, with Error Checking activated (Formulas tab) little triangles (by default green) should be clear evidence.

    These also provide an easy way to convert strings that look like numbers into numbers that Excel can add. Select a contiguous range starting with a cell requiring conversion and a warning sign should appear:

    SO29175401 example

    (not always top left). Click on the exclamation mark and on Convert to Number and hopefully the format of all cells in your selection will be converted so your SUM function behave as you would like.

    Much less likely (because I don't see how you would have done) is that somehow you have acquired leading spaces and when entering data it was into a cell formatted as Text. The triangle warning would be the same but Number Stored as Text does no conversion. A formula such as:

    =TRIM(A1)

    should do however, though it does depend on the type of space (eg would not work for NBSP - though you should not acquire one of those from an SQL import).

    There may be other possible causes but it seems best to try the above before alternatives.