In Microsoft Excel, I have a named (2D) range. For simplicity, let's assume it looks like this:
| 1 | 2 | 3 | 4 | 5 | 5 | 4 | 3 | 2 | 1 |
This represent a time series growth curve, where N number of these could kick off in any point in time. I'm looking for an efficient way to calculate what the cumulative sum of these at any point in time would be, given that N start at that point in time.
So for example, if one starts at time 0, and one at time 3, and two at time 7:
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 0 | 0 | 1 | 0 | 0 | 0 | 2 | 0 | 0 |
Then the cumulative total would be:
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 5 | 4 | 3 | 2 | 1 |
| 1 | 2 | 3 | 4 | 5 | 5 | 4 | |||
| 2 | 4 | 6 | |||||||
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 1 | 2 | 3 | 5 | 7 | 8 | 8 | 10 | 11 | 11 |
I'd like to write a formula that gets to that total without having to use those extra rows to sum over, but can't figure out how.
CodePudding user response：
Use SUMPRODUCT and INDEX:
=SUMPRODUCT(INDEX($M$1:$V$1,(COLUMN()-COLUMN($A$1:A1) 1)),$A$2:A2)
The ranges are dynamic and increase as it is pulled over.
with versions that are not Office 365 we need to trick INDEX into accepting an array:
=SUMPRODUCT(INDEX($M$1:$V$1,N(IF({1},(COLUMN()-COLUMN($A$1:A1) 1)))),$A$2:A2)
This would then be confirmed with Ctrl-Shift-Enter to make it an array formula.
