In Excel, is there an efficient way to sum overlapping Named Rangess?

user_1575497 0
Viewed 43

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.

excel
1 Answers

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.

enter image description here

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.

enter image description here

user_48515900
Related