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.

 

[Fluff]

Maybe like

=REDUCE(0,I2:Z2,LAMBDA(a,b,a+b*your lookup formula))

 

[PrettyGood_Not Great]

Maybe like

=REDUCE(0,I2:Z2,LAMBDA(a,b,a+b*your lookup formula))[/CO
[/QUOTE]

 

[PrettyGood_Not Great]

Thanks Fluff, I just migrated out of the stone ages and this is all new, let me get back to you.

 

[PrettyGood_Not Great]

Maybe like

=REDUCE(0,I2:Z2,LAMBDA(a,b,a+b*your lookup formula))

Hi Fluff, I am having trouble with the LAMBDA. I am new to it an not quite sure what you had in mind. I have added a mini sheet that contains the double XLOOKUP to the rate table into columnH. I have also attempted your formula in H2. Could you help further please?

book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	RateCode	2023	2024	2025	2026	2027		SUM Result	Skill/Rate	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25
2	R1	186.9	192.5	198.3	204.2	210.3		#VALUE!	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
3	R2	131.9	135.9	139.9	144.1	148.5		135.9	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
4	R3	106.0	109.2	112.4	115.8	119.3		109.2	C1R3	41	45	56	43	43	52	45	45	54	43	45	45	41	45	28
5	R4	82.1	84.5	87.1	89.7	92.4		84.5	D1R4	68	75	94	71	71	86	75	75	90	71	75	75	68	75	0
6	R5	47.3	48.7	50.1	51.6	53.2		48.7	E1R5	61	68	84	64	64	78	68	68	81	64	68	68	61	68	0
Sheet1

Cell Formulas
Range	Formula
H2	H2	=REDUCE(0,I2:X2,LAMBDA(XLOOKUP(RIGHT($I2,2),$A$2:$A$6,XLOOKUP(YEAR(J$1),$B$1:$F$1,$B$2:$F$6))))
H3:H6	H3	=XLOOKUP(RIGHT($I3,2),$A$2:$A$6,XLOOKUP(YEAR(J$1),$B$1:$F$1,$B$2:$F$6))

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	RateCode	2023	2024	2025	2026	2027		SUM Result	Skill/Rate	01/01/2024	01/02/2024	01/03/2024	01/04/2024	01/05/2024	01/06/2024	01/07/2024	01/08/2024	01/09/2024	01/10/2024	01/11/2024	01/12/2024	01/01/2025	01/02/2025	01/03/2025
2	R1	186.88	192.4864	198.261	204.2088	210.3351		269685.154	A1R1	108.04355	120.04839	150.06048	114.04597	114.04597	138.05565	75.048387	75.048387	90.058065	71.295968	75.048387	75.048387	67.543548	75.048387	46.935484
3	R2	131.9	135.857	139.9327	144.1307	148.4546		186573.267	B1R2	81	120	150	114	114	138	75	75	90	71.25	75	75	67.5	75	46.875
4	R3	105.99	109.1697	112.4448	115.8181	119.2927		73516.2472	C1R3	40.53629	45.040323	56.300403	42.788306	42.788306	51.796371	45.040323	45.040323	54.048387	42.788306	45.040323	45.040323	40.53629	45.040323	28.175403
5	R4	82.08	84.5424	87.07867	89.69103	92.38176		90737.3732	D1R4	67.514516	75.016129	93.770161	71.265323	71.265323	86.268548	75.016129	75.016129	90.019355	71.265323	75.016129	75.016129	67.514516	75.016129	0.0201613
6	R5	47.25	48.6675	50.12753	51.63135	53.18029		47005.4203	E1R5	60.757258	67.508065	84.385081	64.132661	64.132661	77.634274	67.508065	67.508065	81.009677	64.132661	67.508065	67.508065	60.757258	67.508065	0.0100806
Data

Cell Formulas
Range	Formula
H2:H6	H2	=SUM(MAP($J$1:$X$1,J2:X2,LAMBDA(a,b,b*XLOOKUP(RIGHT($I2,2),$A$2:$A$6,XLOOKUP(YEAR(a),$B$1:$F$1,$B$2:$F$6)))))

 

[PrettyGood_Not Great]

=SUM(MAP($J$1:$X$1,J2:X2,LAMBDA(a,b,b*XLOOKUP(RIGHT($I2,2),$A$2:$A$6,XLOOKUP(YEAR(a),$B$1:$F$1,$B$2:$F$6)))))

ok Nice, this is excellent thank you! I am having one issue with it though. In the real workbook the last cell in the row header for the dates (X1) is blank due to it not having data to return. This equation, along with all the other attempts I`ve made returns #VALUE if the equation references the full row (J1:X1). If I back off the row arrays to stop at the visible data (J1:W1) then they all work. How can I handle this error? Note that the Date Header is a spilled array formula, maybe that is the issue? It is important to have this error handling as the duration of the project can change at any time, thus requiring some extra columns in the header row to handle the increased duration.

 

[Fluff]

How about

=SUM(MAP($J$1:$X$1,J2:X2,LAMBDA(a,b,IF(a="",0,b*XLOOKUP(RIGHT($I2,2),$A$2:$A$6,XLOOKUP(YEAR(a),$B$1:$F$1,$B$2:$F$6))))))

 

[PrettyGood_Not Great]

How about

=SUM(MAP($J$1:$X$1,J2:X2,LAMBDA(a,b,IF(a="",0,b*XLOOKUP(RIGHT($I2,2),$A$2:$A$6,XLOOKUP(YEAR(a),$B$1:$F$1,$B$2:$F$6))))))

Nice! Thank you Fluff!

I have another related challenge, and that would be to do the same for the columns (sum of column after application of rate lookup, located above date header row), but this time I`d like it to be a dynamic subtotal when I filter on Charge codes (column H). Would you be interested in this? or shall I start another post?

 

[Fluff]

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