» Creating a Summing Formula that Automatically Adjusts to a Dynamic Range

Problem:

List1 (column B) initially consisted of the numbers from cell B2 to cell B7.
In order to calculate their sum the following formula was entered in cell B8:
=SUM(B2:B7)
After performing the above calculation, three more numbers were added to the list, and the formula had to be altered to include those numbers as well.
We want to avoid having to continually make this update by creating a formula that will automatically sum the entire range whenever new values are added.

Solution:

Use the SUM, IF, COUNT, and OFFSET functions as shown in the following formula:
=SUM(B2:B7,(IF(COUNT(B9:B990)>0,OFFSET(B9,0,0,COUNT(B9:B990)),0)))