Dynamically sum a row after each row entry has been multiplied by a two way lookup.

[PrettyGood_Not Great]

My challenge is to take the sum of each row in the data table (large table on right), after each row entry has been multiplied by a two-way lookup to the rate table (small table on left). I have tried working with SUMPRODUCT without success. I have the two way lookup working with a double XLOOKUP but cannot get it to span the range of the entire row to work within the SUMPRODUCT. I need the sum of the entire row in one cell (SUM Result), and the ability to pull it down. Note that none of these ranges have the same dimensions. The data table with the Sum Result is approx. 1000 rows deep. A 365 solution would be ideal, however 2016 techniques are still welcomed.

 

[PrettyGood_Not Great]

As this is a totally different question it needs a new thread. Thanks

ok will do thank you. A question regarding this solution. In trying to make it spill down and I discovered something about the LAMBDA I don`t fully understand. What does the "b" parameter represent in this equation? I can see clearly that the "a" parameter is the date header row.

 

[Fluff]

The b is value of the 2nd row as the function loops through the range.

 

[PrettyGood_Not Great]

The b is value of the 2nd row as the function loops through the range.

Got it, thanks again.

 

[PrettyGood_Not Great]

The b is value of the 2nd row as the function loops through the range.

Hi Fluff, I am still unable to get this equation to spill down, what is the trick here? I tried wrapping it in a BYROW without success.

 

[Fluff]

Try

=BYROW(I2:X6,LAMBDA(br,SUM(MAP($I$1:$X$1,br,LAMBDA(a,b,IF(ISNUMBER(b),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$2:$A$6,XLOOKUP(YEAR(a),$B$1:$F$1,$B$2:$F$6)),0))))))

 

[PrettyGood_Not Great]

=BYROW(I2:X6,LAMBDA(br,SUM(MAP($I$1:$X$1,br,LAMBDA(a,b,IF(ISNUMBER(b),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$2:$A$6,XLOOKUP(YEAR(a),$B$1:$F$1,$B$2:$F$6)),0))))))

Stone Cold Fluff. Works perfect. Can you explain the "br" please? I see some indications online that this is a line break? totally lost on how this one is working.

 

[Fluff]

It's just a variable & can be renamed if you want.