Fixed Rows Variable Column in SUMPRODUCT formula

[Mokk817]

I've been searching for a long time and can't seem to come up with the correct adaptation to my formula so any help is much appreciated.

Basically I'm using sumproduct to multiply data in columns on two different sheets.

=SUMPRODUCT(($N$2:$N$244)*('GAMES MATRIX'!B$2:B$244))

The formula works fine but my issue is I need to drag this formula down and get the columns to advance, essentially looking like this:

=SUMPRODUCT(($N$2:$N$244)*('GAMES MATRIX'!C$2:C$244))
=SUMPRODUCT(($N$2:$N$244)*('GAMES MATRIX'!D$2:D$244))
=SUMPRODUCT(($N$2:$N$244)*('GAMES MATRIX'!E$2:E$244))

I have tried different variations of INDEX and ROW formulas but can't get anything to work correctly.

Again any advice is much appreciated.

 

[Marcelo Branco]

Assuming the first formula in A2 (adjust to suit) maybe something like this

A2
=SUMPRODUCT(($N$2:$N$244)*(INDEX('GAMES MATRIX'!$C$2:$Z$244,0,ROWS(A$2:A2))))

copy down as needed

Hope this helps

M.

 

[Mokk817]

Absolutely brilliant!!!

Thanks so much Marcelo!!

 

[Marcelo Branco]

Absolutely brilliant!!!

Thanks so much Marcelo!!

You are very welcome. Thanks for the feedback.

M.