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