How To Sum A Dynamic Range: Google Sheets (Example)

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
Using INDIRECT in the SUM function allows the value in cell B2 to dictate the dynamic calculation performed

In 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
Using INDIRECT in the SUM function allows the value in cell B2 to dictate the dynamic calculation performed

The 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
Using INDIRECT twice in the SUM function allows the value in cells B2 and B3 to dictate the dynamic calculation performed

As 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
Using INDIRECT twice in the SUM function using relative references allows the value in cells B2 and B3 to dictate the dynamic calculation performed

What 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
By changing the scope operator in the SUM function to multiple ranges the INDIRECT formula helps for non-adjacent cells to be added

By 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
By applying checks on whether values have been entered for the dynamic range for the INDIRECT formula can help minimise #REF! errors

By 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
Using OFFSET to define the range can be an easier alternative to dynamically SUM a range

In 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
Using 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 two 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.

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.