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)))

 

For more information refer to the following link:
https://www.exceltip.com/summing/summing-values-at-the-intersection-of-two-or-more-ranges.html

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube