# Sum multiplications of horizontal ranges of cells, starting in different columns in Excel

## The input

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.

## The output

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?

``````=SUMPRODUCT('Evolutions'!\$B6:F6,'Starting Objects'!\$B6:F6)
``````
• Thank you for your answer! I have experimented a bit, but then realized my example formula was wrong... Sorry about that. The main difficulty is that I indeed need a `SUMPRODUCT`, but one of the to ranges needs to be read 'in reverse order'. See my updated question, thanks. – Sygmoral May 28 at 9:14

I eventually found the following solution (with no need for a hidden / temporary sheet):

``````=SUMPRODUCT(
'Starting Objects'!\$B4:F4;
INDEX(
'Evolutions'!\$B4:F4;
1;
COUNTA('Evolutions'!\$B4:F4) - COLUMN('Evolutions'!\$B4:F4) + 1
)
)
``````

The INDEX construction reverses the range in the Evolutions sheet.