How to make a variable (dynamic) range sum?
I have a B3: Bn range with dates and a C2: Y2 range with a different date range. I am trying to find a date from the range B3: Bn in the range C2: Y2 and then starting from that cell to the sum of the values. For this I use:
=SUM(OFFSET(C3;0;MATCH(B3;$C$2:$Y$2;0)):Y3)
But instead of Y3, I would like to say: the sum values only start from the cell offset value up to + 7 of the other columns.
Maybe someone can handle this? Thank!
source to share
In the table in the picture, cell B6 contains
=SUM(INDEX(A2:P2,1,B4):INDEX(A2:P2,1,B4+B5-1))
You can use the adapted formula to accomplish your task. It indirectly sets (s INDEX
) the start and end cells to execute the sum. I defined it as a start cell (column 3 of range A2: P2) and multiple cells (4).
Questions to consider:
- You may need an absolute reference for some column / row references.
- You can define your range to sum up in several different ways.
source to share
You can use the INDIRECT function. It allows you to dynamically create a range of cells in a formula. This way you can have a single forum cell that creates your range of cells as text for example.
=B1&":"&B2 // in Cell C1, assuming B1 is "A1" and B21 is "A2" this would result in "A1:A2"
And then you can dynamically create a range of cells from what the indirect uses, which then the SUM function can use.
=SUM(INDIRECT(C1)) // would result the SUM(A1:A2) in our example
source to share