Dynamic Subtotal of column after each row entry has been multiplied by two-way lookup

[PrettyGood_Not Great]

Hi, I have a challenge to produce a column total above the header row, where the total is the sum product of the entire row, after each entry has been multiplied against the rate table. Currently showing in row A of the data table is the double XLOOKUP to find the rate.

Thanks to @Fluff for the solution in column H where this operation is being performed for the rows. The challenge here is to have the same results for the column totals, BUT it needs to produce a subtotal when filtering on the Charge Codes (column I). Anyone know a 365 solution to this?

Note it will also need to handle error cases due to empty lookups.

PGNG.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1										Subtotal-->	192.5	198.3	192.5	192.5	192.5	192.5	192.5	192.5	192.5	192.5	192.5	192.5	198.3	198.3	198.3
2	RateCode	2023	2024	2025	2026	2027		SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-25	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
3	R1	186.9	192.5	198.3	204.2	210.3		270335.5	A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
4	R2	131.9	135.9	139.9	144.1	148.5		187151.2	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
5	R3	106	109.2	112.4	115.8	119.3		73782	A103	C1R3	41	45	56	43	43	52	45	45	54	43	45	45	41	45	28
6	R4	82.1	84.5	87.1	89.7	92.4		90305.8	A104	D1R4	68	75	94	71	71	86	75	75	90	71	75	68	68	75	0
7	R5	47.3	48.7	50.1	51.6	53.2		46930.4	A105	E1R5	61	68	84	64	64	78	68	68	81	64	68	61	61	68	0
Sheet1

Cell Formulas
Range	Formula
K1:Y1	K1	=XLOOKUP(RIGHT($J3,2),$A$3:$A$7,XLOOKUP(YEAR(K$2),$B$2:$F$2,$B$3:$F$7))
H3:H7	H3	=SUM(MAP($K$2:$Y$2,K3:Y3,LAMBDA(a,b,b*XLOOKUP(RIGHT($J3,2),$A$3:$A$7,XLOOKUP(YEAR(a),$B$2:$F$2,$B$3:$F$7)))))

 

[JumpingCrab]

Assuming that the rate code in column A always matches the rate code embedded in column J,
this would work:

Cell K1:
=SUMPRODUCT(--(RIGHT($J3:$J7,2)=$A3:$A7),K$3:K$7,INDEX($B$3:$F$7,0,MATCH(YEAR(K$2),$B$2:$F$2,0)))

You can drag this across the top row.

Best,
JL

 

[KRice]

This resembles an earlier request that evolved over time to accommodate filtering and missing data. To account for those things, and to take advantage of Excel 365's spilling array feature, I would tackle it as shown below. Note that I've shifted the two tables so that a filter can be applied to the column I Charge Codes (which will hide some of the righthand table rows) while still allowing the lefthand table to remain visible. I've also inserted the suggestion from @JumpingCrab, but that doesn't spill and it doesn't account for filtering, as hidden Charge Codes are still considered, as demonstrated in the lower snippet.

MrExcel_20240505.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2	RateCode	2023	2024	2025	2026	2027
3	R1	186.9	192.5	198.3	204.2	210.3
4	R2	131.9	135.9	139.9	144.1	148.5
5	R3	106	109.2	112.4	115.8	119.3
6	R4	82.1	84.5	87.1	89.7	92.4
7	R5	47.3	48.7	50.1	51.6	53.2
8
9											44991.8	55581.3	67409	51249.5	51249.5	62063.2	39193.1	39193.1	47002.5	37128.3	39193.1	38260.7	36584.9	40362.3	19042.6
10										Subtotal-->	44991.8	55581.3	67409	51249.5	51249.5	62063.2	39193.1	39193.1	47002.5	37128.3	39193.1	38260.7	36584.9	40362.3	19042.6
11
12								SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-25	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
13								270335.5	A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
14								187151.2	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
15								73782	A103	C1R3	41	45	56	43	43	52	45	45	54	43	45	45	41	45	28
16								90305.8	A104	D1R4	68	75	94	71	71	86	75	75	90	71	75	68	68	75	0
17								46930.4	A105	E1R5	61	68	84	64	64	78	68	68	81	64	68	61	61	68	0
PGNG_Sheet1

Cell Formulas
Range	Formula
K9:Y9	L9	=SUMPRODUCT(--(RIGHT($J13:$J17,2)=$A3:$A7),L$13:L$17,INDEX($B$3:$F$7,0,MATCH(YEAR(L$12),$B$2:$F$2,0)))
K10:Y10	K10	=LET( rary, IFERROR(INDEX(B3:F8,XMATCH(RIGHT(J13:J17,2),$A$3:$A$8,0),XMATCH(YEAR(K12:Y12),B2:F2,0)),""), vis, MAP(J13:J17,LAMBDA(r,--(SUBTOTAL(103,r)=1))), res,BYCOL(SEQUENCE(,COLUMNS(K12:Y12)),LAMBDA(x, IF(AND(CHOOSECOLS(rary,x)=""),"",SUMPRODUCT(CHOOSECOLS(K13:Y17,x),CHOOSECOLS(rary,x),vis)))), res)
H13:H17	H13	=IFERROR(BYROW(J13:Y17,LAMBDA(br,SUM(MAP($J$12:$Y$12,br,LAMBDA(a,b,IF(AND(ISNUMBER(a),ISNUMBER(b)),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$3:$A$8,XLOOKUP(YEAR(a),$B$2:$F$2,$B$3:$F$8)),0)))))),"")
Dynamic array formulas.

MrExcel_20240505.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2	RateCode	2023	2024	2025	2026	2027
3	R1	186.9	192.5	198.3	204.2	210.3
4	R2	131.9	135.9	139.9	144.1	148.5
5	R3	106	109.2	112.4	115.8	119.3
6	R4	82.1	84.5	87.1	89.7	92.4
7	R5	47.3	48.7	50.1	51.6	53.2
8
9											44991.8	55581.3	67409	51249.5	51249.5	62063.2	39193.1	39193.1	47002.5	37128.3	39193.1	38260.7	36584.9	40362.3	19042.6
10										Subtotal-->	31797.9	40584	49260	37437.6	37437.6	45319.2	24630	24630	29556	23316.4	24630	24630	22997.6	25365	15895.4
11
12								SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-25	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
13								270335.5	A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
14								187151.2	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
18
PGNG_Sheet1

Cell Formulas
Range	Formula
K9:Y9	L9	=SUMPRODUCT(--(RIGHT($J13:$J17,2)=$A3:$A7),L$13:L$17,INDEX($B$3:$F$7,0,MATCH(YEAR(L$12),$B$2:$F$2,0)))
K10:Y10	K10	=LET( rary, IFERROR(INDEX(B3:F8,XMATCH(RIGHT(J13:J17,2),$A$3:$A$8,0),XMATCH(YEAR(K12:Y12),B2:F2,0)),""), vis, MAP(J13:J17,LAMBDA(r,--(SUBTOTAL(103,r)=1))), res,BYCOL(SEQUENCE(,COLUMNS(K12:Y12)),LAMBDA(x, IF(AND(CHOOSECOLS(rary,x)=""),"",SUMPRODUCT(CHOOSECOLS(K13:Y17,x),CHOOSECOLS(rary,x),vis)))), res)
H13:H17	H13	=IFERROR(BYROW(J13:Y17,LAMBDA(br,SUM(MAP($J$12:$Y$12,br,LAMBDA(a,b,IF(AND(ISNUMBER(a),ISNUMBER(b)),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$3:$A$8,XLOOKUP(YEAR(a),$B$2:$F$2,$B$3:$F$8)),0)))))),"")
Dynamic array formulas.

 

[PrettyGood_Not Great]

This resembles an earlier request that evolved over time to accommodate filtering and missing data. To account for those things, and to take advantage of Excel 365's spilling array feature, I would tackle it as shown below. Note that I've shifted the two tables so that a filter can be applied to the column I Charge Codes (which will hide some of the righthand table rows) while still allowing the lefthand table to remain visible. I've also inserted the suggestion from @JumpingCrab, but that doesn't spill and it doesn't account for filtering, as hidden Charge Codes are still considered, as demonstrated in the lower snippet.

MrExcel_20240505.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2	RateCode	2023	2024	2025	2026	2027
3	R1	186.9	192.5	198.3	204.2	210.3
4	R2	131.9	135.9	139.9	144.1	148.5
5	R3	106	109.2	112.4	115.8	119.3
6	R4	82.1	84.5	87.1	89.7	92.4
7	R5	47.3	48.7	50.1	51.6	53.2
8
9											44991.8	55581.3	67409	51249.5	51249.5	62063.2	39193.1	39193.1	47002.5	37128.3	39193.1	38260.7	36584.9	40362.3	19042.6
10										Subtotal-->	44991.8	55581.3	67409	51249.5	51249.5	62063.2	39193.1	39193.1	47002.5	37128.3	39193.1	38260.7	36584.9	40362.3	19042.6
11
12								SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-25	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
13								270335.5	A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
14								187151.2	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
15								73782	A103	C1R3	41	45	56	43	43	52	45	45	54	43	45	45	41	45	28
16								90305.8	A104	D1R4	68	75	94	71	71	86	75	75	90	71	75	68	68	75	0
17								46930.4	A105	E1R5	61	68	84	64	64	78	68	68	81	64	68	61	61	68	0
PGNG_Sheet1

Cell Formulas
Range	Formula
K9:Y9	L9	=SUMPRODUCT(--(RIGHT($J13:$J17,2)=$A3:$A7),L$13:L$17,INDEX($B$3:$F$7,0,MATCH(YEAR(L$12),$B$2:$F$2,0)))
K10:Y10	K10	=LET( rary, IFERROR(INDEX(B3:F8,XMATCH(RIGHT(J13:J17,2),$A$3:$A$8,0),XMATCH(YEAR(K12:Y12),B2:F2,0)),""), vis, MAP(J13:J17,LAMBDA(r,--(SUBTOTAL(103,r)=1))), res,BYCOL(SEQUENCE(,COLUMNS(K12:Y12)),LAMBDA(x, IF(AND(CHOOSECOLS(rary,x)=""),"",SUMPRODUCT(CHOOSECOLS(K13:Y17,x),CHOOSECOLS(rary,x),vis)))), res)
H13:H17	H13	=IFERROR(BYROW(J13:Y17,LAMBDA(br,SUM(MAP($J$12:$Y$12,br,LAMBDA(a,b,IF(AND(ISNUMBER(a),ISNUMBER(b)),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$3:$A$8,XLOOKUP(YEAR(a),$B$2:$F$2,$B$3:$F$8)),0)))))),"")
Dynamic array formulas.

MrExcel_20240505.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2	RateCode	2023	2024	2025	2026	2027
3	R1	186.9	192.5	198.3	204.2	210.3
4	R2	131.9	135.9	139.9	144.1	148.5
5	R3	106	109.2	112.4	115.8	119.3
6	R4	82.1	84.5	87.1	89.7	92.4
7	R5	47.3	48.7	50.1	51.6	53.2
8
9											44991.8	55581.3	67409	51249.5	51249.5	62063.2	39193.1	39193.1	47002.5	37128.3	39193.1	38260.7	36584.9	40362.3	19042.6
10										Subtotal-->	31797.9	40584	49260	37437.6	37437.6	45319.2	24630	24630	29556	23316.4	24630	24630	22997.6	25365	15895.4
11
12								SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-25	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
13								270335.5	A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
14								187151.2	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
18
PGNG_Sheet1

Cell Formulas
Range	Formula
K9:Y9	L9	=SUMPRODUCT(--(RIGHT($J13:$J17,2)=$A3:$A7),L$13:L$17,INDEX($B$3:$F$7,0,MATCH(YEAR(L$12),$B$2:$F$2,0)))
K10:Y10	K10	=LET( rary, IFERROR(INDEX(B3:F8,XMATCH(RIGHT(J13:J17,2),$A$3:$A$8,0),XMATCH(YEAR(K12:Y12),B2:F2,0)),""), vis, MAP(J13:J17,LAMBDA(r,--(SUBTOTAL(103,r)=1))), res,BYCOL(SEQUENCE(,COLUMNS(K12:Y12)),LAMBDA(x, IF(AND(CHOOSECOLS(rary,x)=""),"",SUMPRODUCT(CHOOSECOLS(K13:Y17,x),CHOOSECOLS(rary,x),vis)))), res)
H13:H17	H13	=IFERROR(BYROW(J13:Y17,LAMBDA(br,SUM(MAP($J$12:$Y$12,br,LAMBDA(a,b,IF(AND(ISNUMBER(a),ISNUMBER(b)),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$3:$A$8,XLOOKUP(YEAR(a),$B$2:$F$2,$B$3:$F$8)),0)))))),"")
Dynamic array formulas.

@KRice, wow. Amazing! the K10 formula is works perfectly, catches the error handling as well. Thanks so much!

 

[KRice]

You're welcome...happy to help.