Microsoft KB Archive/117127

= Microsoft Knowledge Base =

XL: AutoSum Button Doesn't Sum Values Separated By Blanks
Last reviewed: June 3, 1997

Article ID: Q117127

5.00 5.00c 7.00 | 5.00 5.00a

WINDOWS        | MACINTOSH kbusage

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows NT, version 5.0
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel 97 for Windows

SUMMARY
In Microsoft Excel, if you use the AutoSum button to generate grand totals when subtotals are separated by blank rows or columns, the grand totals will be the same as the last sum in the range (rather than the sum of all of the subtotals). This is by design in Microsoft Excel.

MORE INFORMATION
In Microsoft Excel, you can use the AutoSum button to sum subtotal rows and columns into grand total rows and columns. Microsoft Excel assumes that you want to add the subtotals and ignore the values.

If the range in which you are trying to generate a grand total contains blank rows or columns between the subtotals, then the grand totals for a particular range will be the same as the last sum or the last subtotal in that range.

WORKAROUND
If the range contains blanks after each subtotal, then you must select the entire range to display the expected result. For example, if you entered the following into a worksheet:

A1: 1 A2: 2 A3: 3 A4: =SUM(A1:A3) A5: A6: 1 A7: 2 A8: 3 A9: =SUM(A6:A8) A10: A11: 1 A12: 2 A13: 3 A14: =SUM(A11:A13) Select range A1:A14 then click the AutoSum button. The grand total is placed in cell A15. The formula placed in cell A15 by the AutoSum function is =SUM(A14,A9,A4).