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.

    Evolution of a newly started object

  • The second sheet sets, for each type of object, how many of those objects "start" in each calendar month.

    Number of starting objects per 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):

  'Starting Objects'!$B4:F4;
    COUNTA('Evolutions'!$B4:F4) - COLUMN('Evolutions'!$B4:F4) + 1

The INDEX construction reverses the range in the Evolutions sheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.