I have two tabs with input.
The first sheet shows, for each type of object, an evolution of a value over its first 24 months. Consider it a measured evolution.
The second sheet sets, for each type of object, how many of those objects "start" in each calendar month.
For each calendar month, for each type of object, I need to get the sum of those evolution values, for all objects (of that type) that have started by that time.
As an example, for 2021-05 and Type C, the value is (edit: corrected after first answer)
= 'Starting objects'!B6 * 'Evolutions'!F6 + 'Starting objects'!C6 * 'Evolutions'!E6 + 'Starting objects'!D6 * 'Evolutions'!D6 + 'Starting objects'!E6 * 'Evolutions'!C6 + 'Starting objects'!F6 * 'Evolutions'!B6
So the formula keeps getting longer as I move to later columns. This is because each single value in the 'Starting objects' tab has contributions in 24 columns (because the Evolution is 24 months long).
This calls for a SUMPRODUCT, but the issue is that the two ranges to be multiplicated need to be read in opposite directions. The 'oldest' object needs the 'newest' evolution value in the observed period. Excel can't revert a range easily, so I tried with a temporary tab with the Evolution in reverse order, but still could not find a formula I could easily drag/copy.
How might I achieve this in a better way?