A Dynamic Sum


The Sum() function will total the values in a defined range, and the only way to adjust the range is to change one of the function’s arguments. However, there is a workaround using the Choose() function. Here’s a quick example:

First, enter the values 1 through 5 in cells A1:A5. Then, enter the formula

=Sum(A1:Choose(B1,A1,A2,A3,A4,A5))

in cell B2.

Now, enter any value between 1 and 5 in cell B1, and the formula in cell B2 will return the sum of the values in cell A1 and the cell that contains the value you entered in cell B1. For instance, if you enter the value 2 in cell B1, the formula in B2 will return the value 3 (1+2). If you enter the value 4, the formula will return 10 (1+2+3+4), and so on.

Tags:

About The Author