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]

So that array is the lookup array used by MATCH. Broaden that formula bar selection to include the entire MATCH function to see if you get numbers and errors (where matches exist and do not exist). And are those 0’s at the end expected?…are those cells blank or 0?

Tell me about the K and R contents…what do they look like?

 

[PrettyGood_Not Great]

So that array is the lookup array used by MATCH. Broaden that formula bar selection to include the entire MATCH function to see if you get numbers and errors (where matches exist and do not exist). And are those 0’s at the end expected?…are those cells blank or 0?

Tell me about the K and R contents…what do they look like?

Slight update. Something went astray and the file crashed. working from the backup, same starting point, same equations and ranges, but now this is before I started messing with excels suggested error corrections. I now get the following after inspection:

Filter - all FALSE
ISNUMBER - returns a single #VALUE
K4:K8=R3 - all FALSE

Entire MATCH - returns all `VALUE plus this odd symbol at the end

Here is the equation out of the file in it's current state for reference. Note: M = R, C=P.

=SUM(('Ref Data'!$N2:$N1300)*('Ref Data'!$K2:$K1300=M$10)*ISNUMBER(MATCH('Ref Data'!$B2:$B1300,FILTER($C$11#,MAP(C11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))

What I can say about the header row (R) is that it is pulling named ranged that contains text, and is doing so via TRANSPOSE (p.s. I did test this out on the mini sheet were your equation is working to validate that the named range and array formula are not impacting).

For K, it is all text. 90% of column is populated, with the last 10% or so being empty.

 

[PrettyGood_Not Great]

Further update - I misspoke

I think the issue might be have something to do with the range the header data is being pulled from: When I test the TRANSPOSE(named range) on the mini sheet header row, it causes the equation to go to zeros. If I use TRANSPOSE() to pull the list of headers from an on sheet table it works. closer?

 

[PrettyGood_Not Great]

Further update - I misspoke

I think the issue might be have something to do with the range the header data is being pulled from: When I test the TRANSPOSE(named range) on the mini sheet header row, it causes the equation to go to zeros. If I use TRANSPOSE() to pull the list of headers from an on sheet table it works. closer?

And it's not even that. replaced everything with text and it continues to work on the mini sheet, but not in the file. stumped

 

[KRice]

This is difficult to debug without being able to examine the file. I'll try to mock up something that's structured closer to your workbook. Something is tying up Excel, as my version is hanging after pasting your minisheet from post #22. I'll have to force-stop and construct step-by-step. So each row is grabbing information from a separate worksheet using INDIRECT? And the Ref Data worksheet, columns B, K,and N...is there anything unusual about the contents of those columns?

 

[KRice]

One thing I notice...the FILTER/MAP references are supposed to be fixed references. The spilling range you've entered in one place is not fixed. Change that to look like the other...$C$11#, otherwise as that formula is copied across the row, the range will change.

 

[PrettyGood_Not Great]

Hi KRice, ok so you got it. it works! Turns out there were two issues. One was as you identified, the C11# not being fixed. The other is that I was using COL:COL referencing as I mentioned much earlier. When I change the column referencing to a fixed size array it works! It's only slightly unfortunate as the user will need to be careful that the range for this equation is always => the reference data. Mind you a small price to pay for the functionality your solution provides.

Can't thank you enough for all your work on this!

 

[PrettyGood_Not Great]

Scrap my first reply, we got it. I used the technique provided by @Fluff for this error handling IF(ISNUMBER(b), to fix this. You guys are too good! Thanks so much!

Canadarm3 Labour_Planning_O365_v1.4.1..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											44891.41	53955.05	67443.82	51257.3	51257.3	62048.31	39179.6	39179.6	47015.52	37220.62	39179.6	39179.6	36319.49	40354.99	19035.26
2	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
3	R1	186.9	192.5	198.3	204.2	210.3		260379.7	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		180013.9	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		70348.1	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		90735.6	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		47004.9	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	=SUMPRODUCT(MAP(K3:K7,LAMBDA(r,--(SUBTOTAL(103,r)=1))),MAP($J$3:$J$7,K3:K7,LAMBDA(a,b,IF(ISNUMBER(b),b*XLOOKUP(RIGHT(a,2),$A$3:$A$7,XLOOKUP(YEAR(K$2),$B$2:$F$2,$B$3:$F$7))))))
H3:H7	H3	=BYROW(J3:X7,LAMBDA(br,SUM(MAP($J$2:$X$2,br,LAMBDA(a,b,IF(ISNUMBER(b),b*XLOOKUP(RIGHT(INDEX(br,,1),2),$A$3:$A$7,XLOOKUP(YEAR(a),$B$2:$F$2,$B$3:$F$7)),0))))))
Dynamic array formulas.

Hi KRice, what would it take to have the K1 equation spill to the right? I tried wrapping it in a BYCOL, but the numbers don't quite add up. Seems like the mapping to the Year lookup may not be returned correctly.

 

[KRice]

I suppose you could think about this as having three components:

1. a rate array that maps entirely to the main table (rary),
2. a visibility column array that determines which rows should be retained or zeroed (vis),
3. a final BYCOL calculation that steps sequentially over column indexes of the main table (K4:Y8), passing the vis array and the ith columns of rary and the main table to SUMPRODUCT.

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											44891.4	53955.1	67443.8	51257.3	51257.3	62048.3	39179.6	39179.6	47015.5	37220.6	39179.6	39179.6	36319.5	40355.0	19035.3
2
3								SUM Result		Skill/Rate	45292	45323	45352	45383	45413	45444	45474	45505	45536	45566	45597	45627	45658	45689	45717
4								260379.7	A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
5								180013.9	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
6								70348.07	A103	C1R3	41	45	56	43	43	52	45	45	54	43	45	45	41	45	28
7								90735.62	A104	D1R4	68	75	94	71	71	86	75	75	90	71	75	75	68	75	0
8								47004.91	A105	E1R5	61	68	84	64	64	78	68	68	81	64	68	68	61	68	0
9
10	RateCode	2023	2024	2025	2026	2027
11	R1	186.9	192.5	198.3	204.2	210.3
12	R2	131.9	135.9	139.9	144.1	148.5
13	R3	106.0	109.2	112.4	115.8	119.3
14	R4	82.1	84.5	87.1	89.7	92.4
15	R5	47.3	48.7	50.1	51.6	53.2
Sheet3 (2)

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

 

[PrettyGood_Not Great]

I suppose you could think about this as having three components:

1. a rate array that maps entirely to the main table (rary),
2. a visibility column array that determines which rows should be retained or zeroed (vis),
3. a final BYCOL calculation that steps sequentially over column indexes of the main table (K4:Y8), passing the vis array and the ith columns of rary and the main table to SUMPRODUCT.

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											44891.4	53955.1	67443.8	51257.3	51257.3	62048.3	39179.6	39179.6	47015.5	37220.6	39179.6	39179.6	36319.5	40355.0	19035.3
2
3								SUM Result		Skill/Rate	45292	45323	45352	45383	45413	45444	45474	45505	45536	45566	45597	45627	45658	45689	45717
4								260379.7	A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
5								180013.9	A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
6								70348.07	A103	C1R3	41	45	56	43	43	52	45	45	54	43	45	45	41	45	28
7								90735.62	A104	D1R4	68	75	94	71	71	86	75	75	90	71	75	75	68	75	0
8								47004.91	A105	E1R5	61	68	84	64	64	78	68	68	81	64	68	68	61	68	0
9
10	RateCode	2023	2024	2025	2026	2027
11	R1	186.9	192.5	198.3	204.2	210.3
12	R2	131.9	135.9	139.9	144.1	148.5
13	R3	106.0	109.2	112.4	115.8	119.3
14	R4	82.1	84.5	87.1	89.7	92.4
15	R5	47.3	48.7	50.1	51.6	53.2
Sheet3 (2)

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

Hey, as always thank you for the clear insights. I have the equation in place but an getting #N/A. Do we need to add back the ISNUMBER to handle the empty cells that can occur in both the header row and column J?