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?

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	Y
1										Subtotal-->	192.5	192.5	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-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
3	R1	186.9	192.5	198.3	204.2	210.3		269685.2	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		186573.3	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
5	R3	106.0	109.2	112.4	115.8	119.3		73516.2	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		90737.4	A104	D1R4	68	75	94	71	71	86	75	75	90	71	75	75	68	75	0
7	R5	47.3	48.7	50.1	51.6	53.2		47005.4	A105	E1R5	61	68	84	64	64	78	68	68	81	64	68	68	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)))))

 

[KRice]

Where are the blanks (or perhaps text)?...Are they in the main table?...columns K:Y in the example I posted?

And to which header row are you referring? Is it the main table...K2:Y2 in the example I posted? Are those sometimes blank?

Does the Skill/Rate column ever have blanks?

 

[PrettyGood_Not Great]

Where are the blanks (or perhaps text)?...Are they in the main table?...columns K:Y in the example I posted?

And to which header row are you referring? Is it the main table...K2:Y2 in the example I posted? Are those sometimes blank?

Does the Skill/Rate column ever have blanks?

Yes to all. In the working file the J:Y range is templated for a large set of data, and associated structure, which will almost always have empty cells. For example, K2:Y2 is formatted for 5 years worth of months, but usually won't be populated for the full duration. J:J also will almost never be fully populated. and the data range K3:Y8 will be populated randomly.

 

[KRice]

I added error traps in both formulas to handle cases where blanks might appear, or text in the main table (see orange cells).

MrExcel_20231231.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											30724	27945	13916	27852		39279	25605	25605	30726	24174	25605	25605	23575	26415	12244
2
3								SUM Result		Skill/Rate	45292	45323	45352	45383		45444	45474	45505	45536	45566	45597	45627	45658	45689	45717
4								217896	A101	A1R1	108	120	text	114	114	138	75	75	90	71	75	75	67	75	46
5									A102			120	150	114	114	138	75	75	90	71	75	75	67	75	46
6								63668	A103	C1R3	40	45	56		42	51	45	45	54	42	45	45	40	45	28
7								77706	A104	D1R4	67		93	71	71	86	75	75	90	71	75	75	67	75	0
8									A105	E1R7	60	67	84	64	64	77	67	67	81	64	67	67	60	67	0
9
10	RateCode	2023	2024	2025	2026	2027
11	R1	186	192	198	204	210
12	R2	132	136	140	144	148
13	R3	106	109	112	116	119
14	R4	82	84	87	90	92
15	R5	47	49	50	52	53
16	R6	38	40	43	47	49
Sheet3 (2)

Cell Formulas
Range	Formula
K1:Y1	K1	=LET( rary, IFERROR(INDEX(B11:F16,XMATCH(RIGHT(J4:J8,2),$A$11:$A$16,0),XMATCH(YEAR(K3:Y3),B10:F10,0)),""), vis, MAP(J4:J8,LAMBDA(r,--(SUBTOTAL(103,r)=1))), res,BYCOL(SEQUENCE(,COLUMNS(K3:Y3)),LAMBDA(x, IF(AND(CHOOSECOLS(rary,x)=""),"",SUMPRODUCT(CHOOSECOLS(K4:Y8,x),CHOOSECOLS(rary,x),vis)))), res)
H4:H8	H4	=IFERROR(BYROW(J4:Y8,LAMBDA(br,SUM(MAP($J$3:$Y$3,br,LAMBDA(a,b,IF(AND(ISNUMBER(a),ISNUMBER(b)),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$11:$A$16,XLOOKUP(YEAR(a),$B$10:$F$10,$B$11:$F$16)),0)))))),"")
Dynamic array formulas.

 

[PrettyGood_Not Great]

I added error traps in both formulas to handle cases where blanks might appear, or text in the main table (see orange cells).

MrExcel_20231231.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											30724	27945	13916	27852		39279	25605	25605	30726	24174	25605	25605	23575	26415	12244
2
3								SUM Result		Skill/Rate	45292	45323	45352	45383		45444	45474	45505	45536	45566	45597	45627	45658	45689	45717
4								217896	A101	A1R1	108	120	text	114	114	138	75	75	90	71	75	75	67	75	46
5									A102			120	150	114	114	138	75	75	90	71	75	75	67	75	46
6								63668	A103	C1R3	40	45	56		42	51	45	45	54	42	45	45	40	45	28
7								77706	A104	D1R4	67		93	71	71	86	75	75	90	71	75	75	67	75	0
8									A105	E1R7	60	67	84	64	64	77	67	67	81	64	67	67	60	67	0
9
10	RateCode	2023	2024	2025	2026	2027
11	R1	186	192	198	204	210
12	R2	132	136	140	144	148
13	R3	106	109	112	116	119
14	R4	82	84	87	90	92
15	R5	47	49	50	52	53
16	R6	38	40	43	47	49
Sheet3 (2)

Cell Formulas
Range	Formula
K1:Y1	K1	=LET( rary, IFERROR(INDEX(B11:F16,XMATCH(RIGHT(J4:J8,2),$A$11:$A$16,0),XMATCH(YEAR(K3:Y3),B10:F10,0)),""), vis, MAP(J4:J8,LAMBDA(r,--(SUBTOTAL(103,r)=1))), res,BYCOL(SEQUENCE(,COLUMNS(K3:Y3)),LAMBDA(x, IF(AND(CHOOSECOLS(rary,x)=""),"",SUMPRODUCT(CHOOSECOLS(K4:Y8,x),CHOOSECOLS(rary,x),vis)))), res)
H4:H8	H4	=IFERROR(BYROW(J4:Y8,LAMBDA(br,SUM(MAP($J$3:$Y$3,br,LAMBDA(a,b,IF(AND(ISNUMBER(a),ISNUMBER(b)),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$11:$A$16,XLOOKUP(YEAR(a),$B$10:$F$10,$B$11:$F$16)),0)))))),"")
Dynamic array formulas.

Nice! I only had to use the first IFERROR() for it to run correctly. You are too good, thank you!

 

[KRice]

You're welcome...glad to hear it's working okay.

 

[Clif McIrvin]

You're welcome...glad to hear it's working okay.

@KRice Not sure if I'm doing this correctly or not, but I want to thank you for guiding me to a solution for my problem. So, I liked one of your posts, and tagged you here. I certainly do not wish to hijack this thread.
In a QC application I need to average the last 3 test results. Straightforward, until the data becomes a filtered list so the the last three results are potentially separated by dozens if not hundreds of rows. Studying your solutions in this and other threads I now have a formula to extract the last three non-zero results:

=TAKE(FILTER(R$16:R1115,MAP(R$16:R1115,LAMBDA(r,--(SUBTOTAL(103,r)=1)))=1),-3)

or

=TAKE(FILTER(R16C[-4]:RC[-4],MAP(R16C[-4]:RC[-4],LAMBDA(r,--(SUBTOTAL(103,r)=1)))=1),-3)

From here it is trivial to wrap it all in AVERAGE(). And, easily modified to get the n value from a worksheet cell.
And- you also introduced me to LET which I had not yet stumbled across. That will be very useful.
So - Thank You!

 

[KRice]

@Clif McIrvin, you made my day!...Yes, the FILTER function that relies on an array generated by MAP with an embedded SUBTOTAL(103,r)=1 condition is a clever way to work with visible cells in a filtered list. I'm glad to hear you were able to adapt the approach described here to your problem (you had a lot to read through to see the evolution of the idea)! Thank you for the update.