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