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