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]

Give the SUBTOTAL function a try. Used like this it ignores filtered cells...assuming you are talking about using the Data>Filter feature.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1										Subtotal-->	68881.5687	82309.67
2	RateCode	2023	2024	2025	2026	2027		SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-24
3	R1	186.88	192.4864	198.261	204.2088	210.3351		269685.2	A101	A1R1	108.043548	120.0484
4	R2	131.9	135.857	139.9327	144.1307	148.4546		186573.3	A102	B1R2	81	120
5	R3	105.99	109.1697	112.4448	115.8181	119.2927		73516.25	A103	C1R3	40.5362903	45.04032
6	R4	82.08	84.5424	87.07867	89.69103	92.38176		90737.37	A104	D1R4	67.5145161	75.01613
7	R5	47.25	48.6675	50.12753	51.63135	53.18029		47005.42	A105	E1R5	60.7572581	67.50806
Sheet1

Cell Formulas
Range	Formula
K1:L1	K1	=XLOOKUP(RIGHT($J3,2),$A$3:$A$7,XLOOKUP(YEAR(K$2),$B$2:$F$2,$B$3:$F$7))*SUBTOTAL(109,(K3:K7))
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)))))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1										Subtotal-->	32491.8596	36102.07
2	RateCode	2023	2024	2025	2026	2027		SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-24
3	R1	186.88	192.4864	198.261	204.2088	210.3351		269685.2	A101	A1R1	108.043548	120.0484
7	R5	47.25	48.6675	50.12753	51.63135	53.18029		47005.42	A105	E1R5	60.7572581	67.50806
Sheet1

Cell Formulas
Range	Formula
K1:L1	K1	=XLOOKUP(RIGHT($J3,2),$A$3:$A$7,XLOOKUP(YEAR(K$2),$B$2:$F$2,$B$3:$F$7))*SUBTOTAL(109,(K3:K7))
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]

I think I missed something. The rate you've shown in the first row above the column headings applies only to the "R1" row (row 3)...so my offering won't work correctly. You'll need a corresponding list is filtered rates for any visible data.

 

[KRice]

Ok...give this a try. We use a MAP function with SUBTOTAL to create an array of 0's and 1's to check whether a row is displayed. The other array is the product of rates and data values...and both arrays are then passed to SUMPRODUCT to get the sum of only those that are visible.

MrExcel_20231231.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										Subtotal-->	34758.2345	42695.97	53369.96
2	RateCode	2023	2024	2025	2026	2027		SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-24	Mar-24
3	R1	186.88	192.4864	198.261	204.2088	210.3351		269685.2	A101	A1R1	108.043548	120.0484	150.0605
4	R2	131.9	135.857	139.9327	144.1307	148.4546		186573.3	A102	B1R2	81	120	150
7	R5	47.25	48.6675	50.12753	51.63135	53.18029		47005.42	A105	E1R5	60.7572581	67.50806	84.38508
Sheet1

Cell Formulas
Range	Formula
K1:M1	K1	=SUMPRODUCT(MAP(K3:K7,LAMBDA(r,--(SUBTOTAL(103,r)=1))),MAP($J$3:$J$7,K3:K7,LAMBDA(a,b,b*XLOOKUP(RIGHT(a,2),$A$3:$A$7,XLOOKUP(YEAR(K$2),$B$2:$F$2,$B$3:$F$7)))))
H3:H4,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)))))

 

[PrettyGood_Not Great]

Ok...give this a try. We use a MAP function with SUBTOTAL to create an array of 0's and 1's to check whether a row is displayed. The other array is the product of rates and data values...and both arrays are then passed to SUMPRODUCT to get the sum of only those that are visible.

MrExcel_20231231.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										Subtotal-->	34758.2345	42695.97	53369.96
2	RateCode	2023	2024	2025	2026	2027		SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-24	Mar-24
3	R1	186.88	192.4864	198.261	204.2088	210.3351		269685.2	A101	A1R1	108.043548	120.0484	150.0605
4	R2	131.9	135.857	139.9327	144.1307	148.4546		186573.3	A102	B1R2	81	120	150
7	R5	47.25	48.6675	50.12753	51.63135	53.18029		47005.42	A105	E1R5	60.7572581	67.50806	84.38508
Sheet1

Cell Formulas
Range	Formula
K1:M1	K1	=SUMPRODUCT(MAP(K3:K7,LAMBDA(r,--(SUBTOTAL(103,r)=1))),MAP($J$3:$J$7,K3:K7,LAMBDA(a,b,b*XLOOKUP(RIGHT(a,2),$A$3:$A$7,XLOOKUP(YEAR(K$2),$B$2:$F$2,$B$3:$F$7)))))
H3:H4,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)))))

Hi KRice, this is excellent, thank you. One Caveat. Your solution is working perfectly in the mini sheet (and I must say this is impressive), however in the operational file I`m using I am getting a #VALUE. The only thing I can see here is this equation may not be handling blank returns on column J. In my file this column is now a spilled array and reads into the formula as $J$2# instead of $J2:$J$7, and the column range for K:Z goes a fair number of rows deeper than results are being returned for ColumnJ. (i.e. the range for column K is approx. K2:K1000)

Is there a way we can add an IF="",0 error handling, or something equivalent into this solution?

 

[PrettyGood_Not Great]

Ok...give this a try. We use a MAP function with SUBTOTAL to create an array of 0's and 1's to check whether a row is displayed. The other array is the product of rates and data values...and both arrays are then passed to SUMPRODUCT to get the sum of only those that are visible.

MrExcel_20231231.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										Subtotal-->	34758.2345	42695.97	53369.96
2	RateCode	2023	2024	2025	2026	2027		SUM Result	Charge Code	Skill/Rate	Jan-24	Feb-24	Mar-24
3	R1	186.88	192.4864	198.261	204.2088	210.3351		269685.2	A101	A1R1	108.043548	120.0484	150.0605
4	R2	131.9	135.857	139.9327	144.1307	148.4546		186573.3	A102	B1R2	81	120	150
7	R5	47.25	48.6675	50.12753	51.63135	53.18029		47005.42	A105	E1R5	60.7572581	67.50806	84.38508
Sheet1

Cell Formulas
Range	Formula
K1:M1	K1	=SUMPRODUCT(MAP(K3:K7,LAMBDA(r,--(SUBTOTAL(103,r)=1))),MAP($J$3:$J$7,K3:K7,LAMBDA(a,b,b*XLOOKUP(RIGHT(a,2),$A$3:$A$7,XLOOKUP(YEAR(K$2),$B$2:$F$2,$B$3:$F$7)))))
H3:H4,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)))))

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!

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											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.

 

[PrettyGood_Not Great]

Could this solution be adapted to perform this same dynamic subtotaling, but instead of it`s sum results being a product of the column contents, multiplied by a lookup, the result would be a much simpler SUMIFS to a data table. This SUMIFS would use the date header as one criteria and the Charge Code (column I) as another criteria. Would only want to see the results in a single cell like in the above example, but when filtering on column J, we would obtain subtotal of the SUMIFS which is pulling from another sheet. Thoughts on this @KRice?

 

[KRice]

Happy to help...and glad you got it working. Can you tell me more about the Skill/Rate column (col J)? You mentioned in practice that it is a spilled range, and referred to as $J$2#, yet your actual data needed for the formulas is in J3 and down. Does the formula delivering spilled J column results generate heading text (or a blank) in J2 and then the actual skill/rate codes in J3 and down, or is there is difference in table location between the worksheet snippet posted and your actual file? I'm trying to understand the #VALUE error you mentioned. Does your data table (columns K and to the right) sometimes have text in it?

Regarding your follow-on question, yes, I believe the same approach can be taken to deliver dynamic subtotals using other arrays; however, SUMIFS may not play well with this scheme, depending on how and where SUMIFS is used. Different functions might be necessary. What are the details?...I don't quite follow this:

...the result would be a much simpler SUMIFS to a data table. This SUMIFS would use the date header as one criteria and the Charge Code (column I) as another criteria.

What does this lookup table on the other sheet look like... where the date header and charge code would be used to find some other value(s). Are you saying that only those values in the other table---corresponding to where the matching conditions are met---would be summed?

 

[PrettyGood_Not Great]

Happy to help...and glad you got it working. Can you tell me more about the Skill/Rate column (col J)? You mentioned in practice that it is a spilled range, and referred to as $J$2#, yet your actual data needed for the formulas is in J3 and down. Does the formula delivering spilled J column results generate heading text (or a blank) in J2 and then the actual skill/rate codes in J3 and down, or is there is difference in table location between the worksheet snippet posted and your actual file? I'm trying to understand the #VALUE error you mentioned. Does your data table (columns K and to the right) sometimes have text in it?

Regarding your follow-on question, yes, I believe the same approach can be taken to deliver dynamic subtotals using other arrays; however, SUMIFS may not play well with this scheme, depending on how and where SUMIFS is used. Different functions might be necessary. What are the details?...I don't quite follow this:



What does this lookup table on the other sheet look like... where the date header and charge code would be used to find some other value(s). Are you saying that only those values in the other table---corresponding to where the matching conditions are met---would be summed?

Hi KRice, to your first question I did a typo in my comment, it is a tezt header in J2 and a spilled range J3# down. The issue with the #VALUE results from your solution looking down but finding no return values after a certain point in column J (still within the set ranges within your equation). What Fluff`s ISNUMBER(b) is doing is effectively an IF(J="","") type of handling.

For the current problem - the data table is a series of columns containing a set of information, that when looked at across a given row forms a Unique ID and would look something like below. I have added the intended/concept SUMIFS above the date header row. Instead of one matching vertical criteria (charge code) however, it would of course be the entire list of Charge Code. In the operational file, and without consideration to the dynamic filtering we are trying to achieve, the only SUM criteria is the dates and as such I am currently using a SUMIF to pull the data. Now what we want to do is take the same SUMIF by date, but be able to filter by charge code.

book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1							192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864	192.4864
2	Date	Charge Code	Hour		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	Jan-24	A101	192.5		A101	A1R1	108	120	150	114	114	138	75	75	90	71	75	75	68	75	47
4	Feb-24	A102	135.9		A102	B1R2	81	120	150	114	114	138	75	75	90	71	75	75	68	75	47
5	Mar-24	A103	109.2		A103	C1R3	41	45	56	43	43	52	45	45	54	43	45	45	41	45	28
6	Apr-24	A104	84.5		A104	D1R4	68	75	94	71	71	86	75	75	90	71	75	75	68	75	0
7	May-24	A105	48.7		A105	E1R5	61	68	84	64	64	78	68	68	81	64	68	68	61	68	0
Sheet1

Cell Formulas
Range	Formula
G1:U1	G1	=SUMIFS($C:$C,$A:$A,$G$2,$B:$B,$E$3)

 

[PrettyGood_Not Great]

Hi KRice, I was editing the post to add clarity but it timed out.

I want to add that in the operational file the date header row is another set of criteria (AA,BB,CC,DD etc), this will not change the solution we are after I just wanted to mention this so you don`t start wondering why we are not just pulling the data onto the example sheet 1:1. I used dates as it was consistent with the fist soltuion.

I am trying to create a dynamic matrix where a user can filter on charge code and obtain a set of filtered rollup data based on the header criteria. Let me know if you have further questions, thank you for you help!