Using Excell, Autosum not working

Question

I am trying to get a column total on a spreadsheet using Autosum. The formula created is "=SUM(I330:I440)" and although every second row, apart from a couple of blanks rows, has a value I keep getting a zero answer. I get a positive answer if I shorten the column to 10, 30 or 40 rows but not the whole column.

Answer 1

Move the total formula outside rows 330–440—such as to I441—because placing =SUM(I330:I440) anywhere within that range creates a circular reference that can display zero.

1. Check for self-inclusion

1. Select the cell containing =SUM(I330:I440).
2. Look at its address in the Name Box, left of the formula bar.
3. If its address is between I330 and I440, either:
   • move the formula to I441, retaining:

          =SUM(I330:I440)
     

   • or adjust the range so it ends immediately above the total cell.

A formula cannot include its own cell in the range it calculates. To locate any additional loops, select Formulas → Error Checking → Circular References, open each listed cell, and correct its formula. Repeat until the status bar no longer reports a circular reference.^1^

2. If the total cell is already outside the range

Check whether the later rows offset the earlier positive total:

1. In unused cells, enter:

      =SUM(I330:I369)
      =SUM(I370:I409)
      =SUM(I410:I440)
   

2. If these subtotals include negative amounts that add up to the positive amounts, the full-range result of zero is mathematically correct.

Blank cells do not prevent SUM from working.

3. Check whether values are stored as text

If the subtotals unexpectedly ignore displayed values:

1. Select I330:I440.
2. Look for green triangles in the upper-left of the populated cells.
3. Select the warning icon and choose Convert to Number.

Numbers stored as text can produce unexpected calculations; Microsoft documents Convert to Number as the direct correction.^2^

If no warning icon appears, insert a temporary adjacent column and test:

=VALUE(I330)

Fill it down, then sum the converted column. Do not overwrite the original data until the conversion results have been checked.

4. Check calculation mode only if edits are not recalculating

Go to File → Options → Formulas → Calculation options → Workbook Calculation, select Automatic, and then recalculate. Manual calculation can leave formulas displaying an earlier result.^3^

---

References

1. Remove or allow a circular reference in Excel | Microsoft Support
2. Convert numbers stored as text to numbers in Excel
3. How to avoid broken formulas in Excel | Microsoft Support