How do you apply the sum to a certain number of cells according to another input cell?
The SUM
function works amazingly well when the range sought for its total value is static, and one way of making a SUM
function somewhat dynamic is to make the cells it references change, but what if you need the SUM
function to total a range according to an input cell?
Something that would look a little like this in words:
=SUM(Starting at A1, or A2, add the next 5 cells below, or maybe 10)
INDIRECT
Function
One way to create a dynamic range is by using the INDIRECT
formula. The INDIRECT
formula has two parameters, with the first being the reference needed as a string, and the second determining if the type of reference entered is in A1
notation.
For example, if I wanted to sum the values from cell A1
to a dynamic number of rows, then I could use the following formula:
A  B  

1  12 
22=SUM(A1:INDIRECT("R"&B2&"C1", FALSE))

2  10  2 
3  15 
INDIRECT
in the SUM
function allows the value in cell B2
to dictate the dynamic calculation performedIn the above example, there is one INDIRECT
formula that produces a reference to "A2"
being the second row in the first column. I could have written the formula without using an R1C1
notation, and it would have produced the same result:
A  B  

1  12 
37=SUM(A1:INDIRECT("A"&B2))

2  10  3 
3  15 
INDIRECT
in the SUM
function allows the value in cell B2
to dictate the dynamic calculation performedThe second example is a little more succinct than the previous, but both examples help to show the possibility of how to use the INDIRECT
function.
What If The Entire Range Is Dynamic?
In our examples above the starting range was fixed and static at cell A1
, but what if the starting range also needs to be dynamic?
You can use two INDIRECT
formulas in the SUM
function if the starting range is also dynamic.
For example, to set the starting cell reference dynamically you could apply the same principles as seen above, just using the concept twice:
A  B  

1  12 
22=SUM(INDIRECT("A"&B2):INDIRECT("A"&B3))

2  10  1 
3  15  2 
INDIRECT
twice in the SUM
function allows the value in cells B2
and B3
to dictate the dynamic calculation performedAs seen from the above example, cells B2
and B3
set the dimension of how to perform the dynamic sum on the range.
This could also be transposed to using R1C1
type references, as seen here:
A  B  

1  12 
37=SUM(INDIRECT("R"&B2&"C1", FALSE):INDIRECT("R"&B3&"C1", FALSE))

2  10  1 
3  15  3 
INDIRECT
twice in the SUM
function using relative references allows the value in cells B2
and B3
to dictate the dynamic calculation performedWhat If The Range Is Not Joined?
So far I’ve assumed the range being dynamically totalled is defined easily in one area. Can the INDIRECT
formula be used multiple times in the SUM
function when ranges are not adjacent to one another?
A  B  

1  12 
27=SUM(INDIRECT("A"&B2), INDIRECT("A"&B3))

2  10  1 
3  15  3 
SUM
function to multiple ranges the INDIRECT
formula helps for nonadjacent cells to be addedBy changing the colon :
to a comma ,
in the SUM
function we can effectively sum multiple ranges using the INDIRECT
formula.
Furthermore, conditions could be placed on the INDIRECT
formula so that its reference is not used, for example if the dynamic cell is empty then swap for the value of zero:
A  B  

1  12 
12=SUM(IF(LEN(B2),INDIRECT("A"&B2),0), IF(LEN(B3),INDIRECT("A"&B3), 0))

2  10  1 
3  15 
INDIRECT
formula can help minimise #REF!
errorsBy wrapping the INDIRECT
formula in an IF
statement to check if the dynamic value exists can help reduce the #REF!
errors that would occur.
OFFSET
Function
Another handy function that can help dynamically sum your required ranges, is to use the offset function.
The OFFSET
function requires 3 parameters, and has an additional 2 more. It looks like this:
=OFFSET(cell_reference, offset_rows, offset_columns, height, width)
Using the common example applied so far, this would look something like this:
A  B  

1  12 
37=SUM(OFFSET(A1, 0, 0, B2, 1))

2  10  3 
3  15 
OFFSET
to define the range can be an easier alternative to dynamically SUM
a rangeIn the example above, starting with a known fixed cell A1
I then define the area needed to sum by using the dynamic value and setting this as the height
parameter. As the column remains the same the width
is set to 1
, but can be left out (default value is 1
).
If the starting value is also dynamic then the same principle as used above can be used: incorporating the INDIRECT
formula to help declare the starting cell of the range.
A  B  

1  12 
25=SUM(OFFSET(INDIRECT("A"&B2), 0, 0, B3, 1))

2  10  1 
3  15  2 
OFFSET
with INDIRECT
to help define the starting range for a range to dynamically SUM
The OFFSET
function can be neater than using multiple INDIRECT
formulas, provided the range can be defined easily in one area.
Summary
Dynamic ranges in spreadsheets can be defined using 2 popular functions: INDIRECT
and OFFSET
. The OFFSET
function is a handy function where the area being worked on is defined in an area, whereas INDIRECT
should be able to handle most use cases.