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]

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.

Thank you again for the explanation, this is really helpful. Ok so this time it works in the mini sheet, however I am still having issue in the actual file. I feel like it is something small that needs to be configured or addressed in some way.

Firstly, since the mini sheet is now aligned to the actual reference sheet, I am able to copy your solution in 1:1 into the operational file, update the tab name for the reference data columns (A:N), update the Charge code (column P) reference and header reference (R3) and we are there. What I get in one case is #VALUE and in another Blank.

Case1 - #VALUE - when I get this error, the reference data columns (A - N) are used as COL:COL (A:A etc), the on sheet Charge code references are set as an absolute range ($P$4:$P$x).
Case2 - Blank - To investigate the error I changed the COL:COL references to an absolute range and I get the Blank return.

I also considered that (shot in the dark) maybe since the charge code range (col P) is a dynamic spilled array that I should reference it as P4# instead of P4:Px. This had no visible effect on the blank result.

Can you think of any sheet settings that I may need to consider? Since I am getting the correct result in the mini sheet there must be something I need to address in the operational file.

 

[PrettyGood_Not Great]

Hi, small update to maybe help your thinking. I clicked on the error message box (yellow box) and selected "update to include cell references" and the blank return changed to a #N/A. Then I traced the error and it points to the first row of the charge code column (col P in the mini sheet). So this makes me think it has something to do with the dynamic spilled array in that column. Thoughts?

Another piece of the puzzle that may or may not be affecting the result. Charge code (col P) is not only a dynamic spilled array but is also a named range. That named range resides on another sheet and contains empty cells at it's bottom. The operational sheet in question is formatted to a range smaller than the named range it is pulling from. I have not had an issue with this for other array formulas just wanted to provide this info, in case it matters.

 

[KRice]

I doubt the spilled range reference is the problem. Can you post the version of the formula you are using (overwrite the sheet name with something generic if it is sensitive), but try to duplicate as closely as possible what your formula looks like.

Some behind-the-scenes diagnostics might help. Go the the cell with the issue, and in the formula bar, use the cursor selection to highlight this part of the formula:

FILTER($P4:$P8,MAP($P$4:$P$8,LAMBDA(r,--(SUBTOTAL(103,r)=1))))

...and then hover the cursor over that highlighting to see a preview of what the returned array looks like, or alternatively, hit F9 to insert that array directly into your formula for closer examination (But do not hit Enter because you don't want to permanently commit/hardwire this array into the formula). Does it appear to be correct? You should see a trimmed down array showing only the visible Charge Codes in the P columns...those that are to be considered in the lookup table. Then hit Esc to revert back to your original formula.

Next, repeat the highlight/selection steps to investigate the array returned by:

ISNUMBER(MATCH($B$4:$B$8,FILTER($P4:$P8,MAP($P$4:$P$8,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))

You should see an array of TRUE/FALSE and it should reflect which rows in the lookup table have Charge Codes of interest...and any FALSE's should correspond to Charge Codes that are filtered out.

Repeat the examination of:

($K$4:$K$8=R$3)

...does the Primary Criteria array appear to be correct? Any FALSE's should correspond to rows that do not meet the match criteria.

And does

($N$4:$N$8)

have all numeric values, or is there text or something else non-numeric somewhere in that range?

 

[PrettyGood_Not Great]

I doubt the spilled range reference is the problem. Can you post the version of the formula you are using (overwrite the sheet name with something generic if it is sensitive), but try to duplicate as closely as possible what your formula looks like.

Some behind-the-scenes diagnostics might help. Go the the cell with the issue, and in the formula bar, use the cursor selection to highlight this part of the formula:

FILTER($P4:$P8,MAP($P$4:$P$8,LAMBDA(r,--(SUBTOTAL(103,r)=1))))

...and then hover the cursor over that highlighting to see a preview of what the returned array looks like, or alternatively, hit F9 to insert that array directly into your formula for closer examination (But do not hit Enter because you don't want to permanently commit/hardwire this array into the formula). Does it appear to be correct? You should see a trimmed down array showing only the visible Charge Codes in the P columns...those that are to be considered in the lookup table. Then hit Esc to revert back to your original formula.

Next, repeat the highlight/selection steps to investigate the array returned by:

ISNUMBER(MATCH($B$4:$B$8,FILTER($P4:$P8,MAP($P$4:$P$8,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))

You should see an array of TRUE/FALSE and it should reflect which rows in the lookup table have Charge Codes of interest...and any FALSE's should correspond to Charge Codes that are filtered out.

Repeat the examination of:

($K$4:$K$8=R$3)

...does the Primary Criteria array appear to be correct? Any FALSE's should correspond to rows that do not meet the match criteria.

And does

($N$4:$N$8)

have all numeric values, or is there text or something else non-numeric somewhere in that range?

Here is the actual sheet in question. The equation is in cell M8. I am looking into the details as well
EDIT
Removed as contain sensitive data.

 

[KRice]

Note that the three major terms (arrays to be multiplied) in the SUM function should have the same dimensions…yours do not.

 

[PrettyGood_Not Great]

I doubt the spilled range reference is the problem. Can you post the version of the formula you are using (overwrite the sheet name with something generic if it is sensitive), but try to duplicate as closely as possible what your formula looks like.

Some behind-the-scenes diagnostics might help. Go the the cell with the issue, and in the formula bar, use the cursor selection to highlight this part of the formula:

FILTER($P4:$P8,MAP($P$4:$P$8,LAMBDA(r,--(SUBTOTAL(103,r)=1))))

...and then hover the cursor over that highlighting to see a preview of what the returned array looks like, or alternatively, hit F9 to insert that array directly into your formula for closer examination (But do not hit Enter because you don't want to permanently commit/hardwire this array into the formula). Does it appear to be correct? You should see a trimmed down array showing only the visible Charge Codes in the P columns...those that are to be considered in the lookup table. Then hit Esc to revert back to your original formula.

Next, repeat the highlight/selection steps to investigate the array returned by:

ISNUMBER(MATCH($B$4:$B$8,FILTER($P4:$P8,MAP($P$4:$P$8,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))

You should see an array of TRUE/FALSE and it should reflect which rows in the lookup table have Charge Codes of interest...and any FALSE's should correspond to Charge Codes that are filtered out.

Repeat the examination of:

($K$4:$K$8=R$3)

...does the Primary Criteria array appear to be correct? Any FALSE's should correspond to rows that do not meet the match criteria.

And does

($N$4:$N$8)

have all numeric values, or is there text or something else non-numeric somewhere in that range?

So for some reason after using cursor to select the portions of equations via formula bar, when I hover over the selection nothing appears. So I used the error checking to show calculation steps. For the SUM it's all long string of zeros followed by a long string of #N/As. For the ISNUBMER(MATCH() all of the charge codes are being returned.

N4:N8 is either a positive value or zeros. I can't get a read on K4:K8.

 

[PrettyGood_Not Great]

Note that the three major terms (arrays to be multiplied) in the SUM function should have the same dimensions…yours do not.

They did when I started, excel changed this when I selected Include Cell references. I have changed it back a couple times and no effect

 

[KRice]

The #N/A’s are probably due to the dimensional mismatch I mentioned. You’re multiplying a 13000+ row array by other arrays that are only about 1300 rows long.

 

[KRice]

It can be tricky sometimes to highlight the correct part of a formula. Usually, for me, the problem occurs when I don’t get the matched parentheses selections just right. And hovering sometimes doesn’t work if the cursor strays too far from the selection. Hit F9 in those cases…but be sure to hit Esc when you’re done looking.

 

[PrettyGood_Not Great]

The #N/A’s are probably due to the dimensional mismatch I mentioned. You’re multiplying a 13000+ row array by other arrays that are only about 1300 rows long.

I think your right about that, but i can't confirm. once I changed the range back to 1300 the "show calculation step" option disappeared. This is what the error box is providing for options, if this is of any help.