How to Prevent Excel's SUM Function from Missing an Inserted Row

Ordinarily, when you insert a row at the end of a column of values in Excel directly above the cell containing a SUM formula, the SUM function does not update to include the newly inserted row's value.  Here are a few ways to deal with this issue:
  1. Change Excel's settings to make Excel pick up a newly inserted last row while using SUM.   Go to Tools and then Options and then select the Edit tab and check the checkbox "Extend data range formats and formulas."  This is not a foolproof tactic, though. First, the range must include at least three cells.  Second, this only works if the SUM formula is at the bottom of a column or to the right of a row. Third, the formula must be within 20 rows of the newly added row.
  2. Always remember to leave an empty row at the bottom of your column and insert the new row of data to be included in the SUM function above that empty row.
  3. Insert the row to be added somewhere within the existing range rather than at the end.  Excel will automatically update the SUM function.
  4. Use the OFFSET function within the SUM function.  For example, say you have the range shown in Image 1 below, in which a range of values spanning from B1 through B9 is totaled in cell B10 with the SUM function =SUM(B1:B9).   Edit the formula in B10 by adding the OFFSET function to the SUM formula so that it instead says  =SUM(B1:OFFSET(B10,-1,0)). Now you can select the row 10 header and insert a row and a new value.  That value will automatically be included in the Excel formula (see Image 2 below).  
Image 1 - Before Offset



Image 2 - After Offset






No comments:

Post a Comment

Note: Only a member of this blog may post a comment.