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

 

[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!

 

[PrettyGood_Not Great]

For clarity,

book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1							192.4864	192.4864	192.4864	192.4864	192.4864
2	Primary Criteria	Charge Code	Hour		Charge Code	Skill/Rate	AA	BB	CC	DD	EE
3	AA	A101	192.5		A101	A1R1	108	120	150	114	114
4	BB	A102	135.9		A102	B1R2	81	120	150	114	114
5	CC	A103	109.2		A103	C1R3	41	45	56	43	43
6	DD	A104	84.5		A104	D1R4	68	75	94	71	71
7	EE	A105	48.7		A105	E1R5	61	68	84	64	64
Sheet1

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

 

[KRice]

Thanks for the clarification. I followed the ISNUMBER error trap implementation just fine, but I was confused when you mentioned in post #5, "...may not be handling blank returns on column J". The ISNUMBER function operates on "b", which is the array passed into MAP---and in this case "b" refers to the data block column K, L, etc. as the formula is copied across. A blank on column J (Skill/Rate code) induces a different type of error (#N/A), not a #VALUE error. But I haven't been able to reproduce the #VALUE error with blanks. However, a text value in the data block (col K and to the right) will induce a #VALUE error, and the ISNUMBER error trap will then handle it correctly. Do you occasionally have text in column K and to the right?

About this adaptation to the SUMIFS formula, I'm confused by the $B:$B,$E$3 portion. Do you want to sum all values in column C where two conditions are met:

1. all date rows in the left-side table that match the subject column heading in the right-side table (e.g., A:A=G2) AND...
2. all Charge Codes in B:B that match any visible charge code in E:E (i.e., a Charge Code that has not been hidden with a filter)? Your example formula includes $E$3, which is leading to this confusion/question.

 

[PrettyGood_Not Great]

Thanks for the clarification. I followed the ISNUMBER error trap implementation just fine, but I was confused when you mentioned in post #5, "...may not be handling blank returns on column J". The ISNUMBER function operates on "b", which is the array passed into MAP---and in this case "b" refers to the data block column K, L, etc. as the formula is copied across. A blank on column J (Skill/Rate code) induces a different type of error (#N/A), not a #VALUE error. But I haven't been able to reproduce the #VALUE error with blanks. However, a text value in the data block (col K and to the right) will induce a #VALUE error, and the ISNUMBER error trap will then handle it correctly. Do you occasionally have text in column K and to the right?

About this adaptation to the SUMIFS formula, I'm confused by the $B:$B,$E$3 portion. Do you want to sum all values in column C where two conditions are met:

1. all date rows in the left-side table that match the subject column heading in the right-side table (e.g., A:A=G2) AND...
2. all Charge Codes in B:B that match any visible charge code in E:E (i.e., a Charge Code that has not been hidden with a filter)? Your example formula includes $E$3, which is leading to this confusion/question.

ok nice, thanks for the breakdown, really helpful. For the col K, when it's dates they are a dynamic spilled range, but on other sheets it can also be text, as in the latest example.

For the adaption to SUMIFS - yes correct. one condition will be the header row of the active sheet, and the other condition will be listed vertically along side to the left.
1. Correct
2. I see your confusion, my apologies. $E$3 was used incorrectly to identify the criteria range, It should read E3:Ex.

 

[KRice]

Okay...I think I understand. Give this a try...and test carefully. You define two ranges in the formula: 1) the range of the Charge Code in the right-hand table (I've called this column ccr), and 2) the range of the lookup table from which the values to be summed come from (I've named this array ary). The MAP(ccr,LAMBDA(r,--(SUBTOTAL(103,r)=1))) component relies on SUBTOTAL's ability to distinguish whether a row has been hidden with a filter. So this creates an array of 1's and 0's indicating which Charge Codes in the right-hand table can be seen. And this array is fed into FILTER to return a consolidated array (no blanks or 0's) of only those charge codes of interest (due to the Date>Filter). That array of charge codes of interest is used by the MATCH function to determine which Charge Codes in the left-hand table (in ary) should be considered, as MATCH used like this will return a number (indicating that array element does match) or an error (indicating that array element does not match). And the MATCH results are processed by the ISERROR function within FILTER to generate a new filtered array (called fary) based on ary, except only the relevant rows of ary will be included (meaning only those rows whose Charge Codes can be found in the trimmed down/filtered list of the right-hand table). So the fary array is still a three-column array (date, charge code, and hour). And fary has already been filtered to eliminate any charge codes that should not be considered, so we then need to identify which rows satisfy the date criterion...and finally sum the corresponding hours. This is done in the last step of the formula by FILTER wrapped by SUM.

MrExcel_20231231.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1							626.1	135.9	358.3	84.5
2	Date	Charge Code	Hour		Charge Code	Skill/Rate	Jan-24	Feb-24	Mar-24	Apr-24	May-24
3	Jan-24	A101	192.5		A101	A1R1	108	120	150	114	114
4	Feb-24	A102	135.9		A102	B1R2	81	120	150	114	114
5	Mar-24	A103	109.2		A103	C1R3	41	45	56	43	43
6	Apr-24	A104	84.5		A104	D1R4	68	75	94	71	71
7	Jan-24	A105	48.7		A105	E1R5	61	68	84	64	64
8	Jan-24	A101	192.5
9	Mar-24	A103	109.2
10	Jan-24	A101	192.5
11	Mar-24	A104	140.0
Sheet6

Cell Formulas
Range	Formula
G1:K1	G1	=LET(ccr,$E$3:$E$7,ary,$A$3:$C$11,fary,FILTER(ary,ISERROR(MATCH(INDEX(ary,,2),FILTER(ccr,MAP(ccr,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))=FALSE),IFERROR(SUM(FILTER(INDEX(fary,,3),(INDEX(fary,,1)=G$2))),""))

 

[KRice]

I had another idea...have a look at the shorter row 1 formula, where there are three main terms in the SUM formula. The first specifies the C column (hours) to be added subject to certain conditions, the second requires the date criterion to be met, and the third establishes a TRUE/FALSE array indicating whether the Charge Code in column B is found in the visible filtered column E of the other table.

MrExcel_20231231.xlsx
	E	F	G	H	I	J	K
1			626.1	0.0	140.0	84.5	0.0
2			626.1		140.0	84.5
3	Charge Code	Skill/Rate	Jan-24	Feb-24	Mar-24	Apr-24	May-24
4	A101	A1R1	108	120	150	114	114
7	A104	D1R4	68	75	94	71	71
8	A105	E1R5	61	68	84	64	64
Sheet6

Cell Formulas
Range	Formula
G1:K1	G1	=SUM(($C$11:$C$19)*($A$11:$A$19=G$3)*ISNUMBER(MATCH($B$11:$B$19,FILTER($E4:$E8,MAP(G4:G8,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))
G2:K2	G2	=LET(ccr,$E$4:$E$8,ary,$A$11:$C$19,fary,FILTER(ary,ISERROR(MATCH(INDEX(ary,,2),FILTER(ccr,MAP(ccr,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))=FALSE),IFERROR(SUM(FILTER(INDEX(fary,,3),(INDEX(fary,,1)=G$3))),""))

I moved the left-hand table for testing purposes so that all of its rows would be visible when the right-hand table filter is applied.

MrExcel_20231231.xlsx
	A	B	C
10	Date	Charge Code	Hour
11	Jan-24	A101	192.5
12	Feb-24	A102	135.9
13	Mar-24	A103	109.2
14	Apr-24	A104	84.5
15	Jan-24	A105	48.7
16	Jan-24	A101	192.5
17	Mar-24	A103	109.2
18	Jan-24	A101	192.5
19	Mar-24	A104	140.0
Sheet6

 

[PrettyGood_Not Great]

Okay...I think I understand. Give this a try...and test carefully. You define two ranges in the formula: 1) the range of the Charge Code in the right-hand table (I've called this column ccr), and 2) the range of the lookup table from which the values to be summed come from (I've named this array ary). The MAP(ccr,LAMBDA(r,--(SUBTOTAL(103,r)=1))) component relies on SUBTOTAL's ability to distinguish whether a row has been hidden with a filter. So this creates an array of 1's and 0's indicating which Charge Codes in the right-hand table can be seen. And this array is fed into FILTER to return a consolidated array (no blanks or 0's) of only those charge codes of interest (due to the Date>Filter). That array of charge codes of interest is used by the MATCH function to determine which Charge Codes in the left-hand table (in ary) should be considered, as MATCH used like this will return a number (indicating that array element does match) or an error (indicating that array element does not match). And the MATCH results are processed by the ISERROR function within FILTER to generate a new filtered array (called fary) based on ary, except only the relevant rows of ary will be included (meaning only those rows whose Charge Codes can be found in the trimmed down/filtered list of the right-hand table). So the fary array is still a three-column array (date, charge code, and hour). And fary has already been filtered to eliminate any charge codes that should not be considered, so we then need to identify which rows satisfy the date criterion...and finally sum the corresponding hours. This is done in the last step of the formula by FILTER wrapped by SUM.

MrExcel_20231231.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1							626.1	135.9	358.3	84.5
2	Date	Charge Code	Hour		Charge Code	Skill/Rate	Jan-24	Feb-24	Mar-24	Apr-24	May-24
3	Jan-24	A101	192.5		A101	A1R1	108	120	150	114	114
4	Feb-24	A102	135.9		A102	B1R2	81	120	150	114	114
5	Mar-24	A103	109.2		A103	C1R3	41	45	56	43	43
6	Apr-24	A104	84.5		A104	D1R4	68	75	94	71	71
7	Jan-24	A105	48.7		A105	E1R5	61	68	84	64	64
8	Jan-24	A101	192.5
9	Mar-24	A103	109.2
10	Jan-24	A101	192.5
11	Mar-24	A104	140.0
Sheet6

Cell Formulas
Range	Formula
G1:K1	G1	=LET(ccr,$E$3:$E$7,ary,$A$3:$C$11,fary,FILTER(ary,ISERROR(MATCH(INDEX(ary,,2),FILTER(ccr,MAP(ccr,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))=FALSE),IFERROR(SUM(FILTER(INDEX(fary,,3),(INDEX(fary,,1)=G$2))),""))

Hey, so you defiantly understand correctly! The first solution presented works as I intended when I applied it to the mini sheet example. When I reference the actual data sheet (ary), I get an error (blank), this is also true if I change the order of any of the three columns in ary (per mini sheet). I can see that there are some column references indicated by numbers (1,2,3) for the reference data, and I tried adjusting these to match the new column positions of ary, but it didn't solve the issue. Below I have included the same mini sheet, but this time with the actual relative positions of our reference data as it resides on the data sheet (didn't think about that). The data sheet starts at column A and goes to S, with the last data column of interest being the hours in column N. An although it doesn't seem to be the issue for the mini sheet, when referencing the data sheet we need to do a full column reference (N:N) as this sheet grows in size every month.

For the second solution - I was not able to get it to work. In this mini sheet example, or the previous example (exact same column references you used). It is returning a "0". When I applied it to the operational sheet it returned a #VALUE. This second solution I feel is going to be the winner as all the referencing to the data sheet are clear and of a conventional manner to what most excel users will be familiar with.

Hopefully I am not wasting your time with incorrect reference, but I tried a couple times. Also, I inspected all the number formatting to make sure it was all set to General incase that could be a factor.

Note on the #VALUE for the second equation. The operational sheet uses a spilled array for charge codes (column P in mini sheet), and a spilled TRANSPOSE for the header column (which is now "Primary Criteria" in mini sheet). Not sure if this makes a difference.

Canadarm3 Labour_Planning_O365_v1.4.5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1																		0	0	0	0	0
2
3	x1	Charge Code	x2	x3	x4	x5	x6	x7	x8	x9	Primary Criteria	x10	x11	Hour		Charge Code	Skill/Rate	AA	BB	CC	DD	EE
4		A101									AA			192.5		A101	A1R1	108	120	150	114	114
5		A102									BB			135.9		A102	B1R2	81	120	150	114	114
6		A103									CC			109.2		A103	C1R3	41	45	56	43	43
7		A104									DD			84.5		A104	D1R4	68	75	94	71	71
8		A105									EE			48.7		A105	E1R5	61	68	84	64	64
Sheet1

Cell Formulas
Range	Formula
R1:V1	R1	=SUM(($N$4:$N$8)*($K$4:$K$8=R$3)*ISNUMBER(MATCH($B$4:$B$8,FILTER($F4:$F8,MAP(H4:H8,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))
R2:V2	R2	=LET(ccr,$P$4:$P$8,ary,$B$4:$N$8,fary,FILTER(ary,ISERROR(MATCH(INDEX(ary,,2),FILTER(ccr,MAP(ccr,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))=FALSE),IFERROR(SUM(FILTER(INDEX(fary,,3),(INDEX(fary,,1)=R$3))),""))

 

[KRice]

For the top row version, which I think is the simplest and probably preferred, give this a try:

=SUM(($N$4:$N$8)*($K$4:$K$8=R$3)*ISNUMBER(MATCH($B$4:$B$8,FILTER($P4:$P8,MAP($P$4:$P$8,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))

The issue when you adapted the formula to your file involved the FILTER/MAP part of the formula. Recall the MAP portion needs to look down some column to "count" which rows are present...so it needs to see content to be counted, and it relies on the SUBTOTAL function, which will ignore rows that are hidden (including those that are not visible due to a filter). So MAP in this revision is looking at your righthand table's Charge Code column. Depending on whether cells in the range P4:P8 have something in them (1's) or not (0's) dictates what the array looks like from the MAP function...and that array is then used to create a trimmed down list (an array) of the valid Charge Codes (in column P) that need to be considered when looking at the lefthand table...so the FILTER function needs to specify the P4:P8 range. Note that it is not critical that the MAP function refer to the P4:P8 range--this is somewhat arbitrary. Previously, I had MAP looking straight below and referencing cells in the righthand table in that same column, but it might be easier to follow the formula by always pointing to the Charge Code column.

 

[KRice]

And for the row 2 option, which is unnecessarily complicated:

=LET(ccr,$P$4:$P$8,ary,$A$4:$N$8,fary,FILTER(ary,ISERROR(MATCH(INDEX(ary,,2),FILTER(ccr,MAP(ccr,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))=FALSE),IFERROR(SUM(FILTER(INDEX(fary,,14),(INDEX(fary,,11)=R$3))),""))

Note that I changed the ary reference to cover columns A:N (somewhat arbitrary, but it makes it convenient to count columns from the leftmost column A when setting the other column index numbers). Then the column index numbers were adjusted to point to the correct columns in the main and filtered arrays---in ary and fary---so you;ll see columns 2, 11, and 14 (charge code, primary criteria, and hour, respectively).