2 criteria Filtered subtotal where the source data is on a different sheet than the criteria

[PrettyGood_Not Great]

Hi, I have a high octane challenge. I want to have a dynamic subtotal at the top of this range, the caveat however is that the data comprising the subtotal is from another data sheet. The only connection is that both sheets use the same two criteria.

In the subtotal row I am returning the SUM of a given type (A, B, C, D, E) from the source sheet using SUMIF. The source sheet also lists the charge codes for each type. The challenge is to dynamically subtotal the SUMIF result for the types, but only for the results of the filtered charge codes.

Any 365 users out there solved this one?

Note for clarity - The data for the desired subtotal is not what would appear in the array shown below. It is connected to this array only by type and charge code and will only be displayed above the array as a single result above the header cell.

 

[PrettyGood_Not Great]

I don't see any problems. There should be no issues with the subtotal formula...at least down to the last component in the LET formula (you could eliminate anything earlier involving the Primary Criteria---and probably should---but that's not critical). The only critical thing that needs to be revised is to remove the *(pc=M$10) part, as that determines which array positions in the "pc" array correspond to the specified Primary Criteria shown in M10 and to the right (those values being either A, B, D, or G). But the "pc" array does contain values other than A, B, D, or G. Some array elements have values of E or F, and those will be included (if the Primary Criteria really doesn't matter). By inclusion, I mean those rows will be considered, and the hours summed on that row if the Charge Code satisfies the matching condition.

I double checked the part of the formula that determines which array elements in the "cc" array (the Charge Code array formed from column B on the 'Ref Data' worksheet) match any of the Charge Codes shown in the filtered list shown in C12 and down:

ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))

I don't see any issues with it...nothing here is affected by the Primary Criteria. I'll refer to the array generated by this formula as the Primary Criteria Row Array, or "pcra" for short.
So the last formula is simply SUM( h * pcra), where "h" is the hours array from the 'Ref Data' worksheet and "pcra" is an array of TRUE/FALSE indicating whether the value shown in the Charge Code column of the 'Ref Data' worksheet matches any of those shown in the filtered list shown in C12 and down.

If you do trim down the formula to eliminate the pc components, remember to redefine the h array, as it is formed from the data array, and "data" has only two columns, not three as it originally did...so the "h" array will be created from the 2nd column of "data":

MrExcel_20240104.xlsx
	C									L	M	N	O	P
8										Subtotal	160
9
10	Charge Code
12	X-0110										133	144	155	166
13	X-0120										233	244	255	266
Test2

Cell Formulas
Range	Formula
M8	M8	=LET(src,'Ref Data'!$A:$N,bcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(bcol),0,ROW(bcol))),data,DROP(TAKE(CHOOSECOLS(src,2,14),lrow),1), cc,INDEX(data,,1),h,INDEX(data,,2), SUM(h*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
M12:P13	M12	=IFERROR(INDIRECT("'"&$C12&"'!"&CELL("address",H$13)),"")

Hi KRice, I don't see the what the issue here is either. You are correct that there appears to be nothing wrong with the subtotal, that part of the equation works perfectly. It is the unfiltered result that is truly bizarre. Since the Src has a lot of noise in the sheet where some of our target reference columns contain data other than what we want, I tried using pc on other columns that represent the results wanted, no change. I then cleaned up the src data so that only what we want is available to begin with, no change. The SUM it pulls when unfiltered is approx. only half of the actual total. But again, as soon as I filter for a charge code I get accurate results. strange.

 

[KRice]

I then cleaned up the src data so that only what we want is available to begin with, no change. The SUM it pulls when unfiltered is approx. only half of the actual total. But again, as soon as I filter for a charge code I get accurate results.

Do you have an example of this? I can't duplicate it. When I use the "pc"-free version of the formula, everything in src data is summed, provided the Charge Code appears in the filtered list...and the result is correct. I'm not sure what you mean by cleaning up src data and Src having a lot of noise. Are there rows in src where the Charge Codes match those in the filtered list, but you do not want to sum them?

 

[PrettyGood_Not Great]

Do you have an example of this? I can't duplicate it. When I use the "pc"-free version of the formula, everything in src data is summed, provided the Charge Code appears in the filtered list...and the result is correct. I'm not sure what you mean by cleaning up src data and Src having a lot of noise. Are there rows in src where the Charge Codes match those in the filtered list, but you do not want to sum them?

ok so I think I found the problem. I created a test workbook with two sheets to show you, how to I take the whole workbook as a XLBB? not having luck with this.

In short, what is happening is this: the charge code column (C11#) has multiple instances of any given cc. When I take the ref data sheet to a new workbook and create a list of cc on another sheet that has only one occurance of each cc I get the correct result, both filtered and unfiltered. Then using the same example, if I reference another list of cc that has multiple occurances of each cc then I get my error condition. It in fact has nothing to do with the src data.

 

[KRice]

Interesting...you can't really upload the entire workbook via XL2BB...it it designed for taking only relatively small samples from a worksheet. If sharing an entire workbook is necessary, then the user can upload it to a reputable file-sharing site (often Dropbox or Google Drive are used) and then share the link to the file...but be sure to not include any sensitive information in the file. If you don't mind sharing the example you've described (where another list of cc with multiple occurrences of each cc is referenced), I'd like to look at it.

 

[PrettyGood_Not Great]

Interesting...you can't really upload the entire workbook via XL2BB...it it designed for taking only relatively small samples from a worksheet. If sharing an entire workbook is necessary, then the user can upload it to a reputable file-sharing site (often Dropbox or Google Drive are used) and then share the link to the file...but be sure to not include any sensitive information in the file. If you don't mind sharing the example you've described (where another list of cc with multiple occurrences of each cc is referenced), I'd like to look at it.

ok so I jammed it into one sheet and hacked down the data to get under 3000 cells. This should illustrate well though. p.s. this issue explains why we didn't see this in the original problem solution as that sheet had only on occurrence of each charge code.

dynamic filter debug.xlsx
	A	B	C	D	E	F	G	H	I
1	correct result	incorrect result		cc	pc	h		Problem list	working list
2	493	44		A-24	AB	16		A-50	A-10
3				A-24	AB	4		A-50	A-21
4				A-24	AB	1		A-50	A-22
5				A-24	AB	5		A-50	A-23
6				A-24	AB	2.5		A-50	A-24
7				A-24	AB	2		A-21	A-25
8				A-24	AB	1		A-30	A-30
9	working list	Problem list		A-24	AB	1.5		A-32	A-32
10	Charge Code	Charge Code		A-24	AB	1		A-70	A-34
11	A-10	A-50		A-24	AB	0.5		A-24	A-50
12	A-21	A-50		A-24	AB	1		A-30	A-60
13	A-22	A-50		A-24	AB	0.5		A-34	A-65
14	A-23	A-50		A-24	AB	0.5		A-32	A-70
15	A-24	A-50		A-24	AB	0.5		A-60
16	A-25	A-21		A-24	AB	1		A-21
17	A-30	A-30		A-24	AB	0.5		A-70
18	A-32	A-32		A-24	AB	1		A-70
19	A-34	A-70		A-10	AG	1.5		A-65
20	A-50	A-24		A-23	AG	2.5		A-24
21	A-60	A-30		A-23	AG	1.5		A-30
22	A-65	A-34		A-10	AG	3.5		A-34
23	A-70	A-32		A-23	AG	36.75		A-30
24		A-60		A-23	AG	77.25		A-21
25		A-21		A-23	AG	1.75		A-21
26		A-70		A-23	AG	8		A-30
27		A-70		A-23	AG	1		A-30
28		A-65		A-23	AG	19		A-30
29		A-24		A-23	AG	0.25		A-30
30		A-30		A-23	AG	1		A-30
31		A-34		A-23	AG	0.5		A-30
32		A-30		A-23	AG	7.5		A-30
33		A-21		A-23	AG	1		A-30
34		A-21		A-23	AG	8.75		A-30
35		A-30		A-23	AG	0.5		A-32
36		A-30		A-23	AG	1		A-32
37		A-30		A-23	AG	2.5		A-32
38		A-30		A-23	AG	0.5		A-32
39		A-30		A-23	AG	7.5		A-32
40		A-30		A-23	AG	1.5		A-32
41		A-30		A-23	AG	3		A-32
42		A-30		A-23	AG	1.5		A-32
43		A-30		A-23	AG	5		A-32
44		A-32		A-23	AG	4		A-32
45		A-32		A-23	AG	6.5		A-32
46		A-32		A-23	AG	2		A-32
47		A-32		A-23	AG	3		A-32
48		A-32		A-23	AG	5.5		A-32
49		A-32		A-23	AG	1		A-32
50		A-32		A-23	AG	0.5		A-34
51		A-32		A-10	AG	24		A-34
52		A-32		A-10	AG	2.5		A-34
53		A-32		A-23	AG	23.75		A-34
54		A-32		A-23	AG	8		A-34
55		A-32		A-23	AG	7		A-34
56		A-32		A-10	AK	1		A-34
57		A-32		A-10	AK	3.5		A-34
58		A-32		A-10	AK	4		A-34
59		A-34		A-10	AK	0.5		A-34
60		A-34		A-10	AK	1		A-34
61		A-34		A-10	AK	1		A-34
62		A-34		A-10	AK	1.5		A-34
63		A-34		A-10	AK	0.5		A-34
64		A-34		A-10	AK	0.5		A-60
65		A-34		A-50	AK	1		A-60
66		A-34		A-50	AK	1		A-60
67		A-34		A-50	AK	0.75		A-60
68		A-34		A-10	AK	1		A-60
69		A-34		A-10	AK	1.25		A-60
70		A-34		A-10	AK	1.5		A-60
71		A-34		A-10	AK	0.25		A-60
72		A-34		A-10	AK	1		A-60
73		A-60		A-10	AK	7.5		A-60
74		A-60		A-10	AK	4.25		A-60
75		A-60		A-10	AK	13		A-60
76		A-60		A-10	AK	4.5		A-60
77		A-60		A-10	AK	3		A-60
78		A-60		A-10	AK	10		A-60
79		A-60		A-10	AK	14.5		A-70
80		A-60		A-10	AK	3.25
81		A-60		A-10	AK	13.5
82		A-60		A-10	AK	3.25
83		A-60		A-10	AK	8.5
84		A-60		A-10	AK	5.75
85		A-60		A-10	AK	12.5
86		A-60		A-10	AK	0.25
87		A-60		A-10	AK	0.75
88		A-70		A-10	AK	5.5
89				A-10	AK	10
90				A-10	AK	0.5
91				A-10	AK	11.5
92				A-10	AK	8
93				A-10	AK	0.75
94				A-10	AK	7
95				A-10	AK	0.25
96				A-50	AK	0.5
97				A-50	AK	0.5
98				A-50	AK	0.25
99				A-50	AK	0.5
Sheet2

Cell Formulas
Range	Formula
A2	A2	=LET(src,$D:$F,bcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(bcol),0,ROW(bcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1),cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3),SUM(h*ISNUMBER(MATCH(cc,FILTER($A11#,MAP($A11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
B2	B2	=LET(src,$D:$F,bcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(bcol),0,ROW(bcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1),cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3),SUM(h*ISNUMBER(MATCH(cc,FILTER($B11#,MAP($B11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
A11:A23	A11	=wl
B11:B88	B11	=pl
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
pl	=Sheet2!$H$2:$H$79	B11
wl	=Sheet2!$I$2:$I$14	A11

 

[KRice]

Thanks for the example. I see the issue. I hadn't anticipated that the filtered list of Charge Codes might contain duplicates. For that scenario, the problem is caused by the MATCH(cc,FILTER($C$11#... construction, as MATCH finds only the first match. So the list of Charge Codes in the data table can only ever match to the first of any duplicates in the filtered list, and the duplicates are never considered. Here is an idea to address that: We use the same approach as before to determine the list of filtered charge codes, but rather than operating on that list right away, we establish it as a variable called "fcc" (for filtered charge codes). Then we use the BYROW LAMBDA function to process fcc one row at a time...each charge code in the "fcc" array is passed as an argument to the same SUM function we had before. This generates a subtotal for each element in the "fcc" array, which is found in the "res" array. Then all of the subtotals in "res" are summed for the final result.

Here is the original version (sans the pc component) and the revised formula using the approach just described:

MrExcel_20240104.xlsx
	C									L	M	N	O	P
8										Subtotal	65	130
9
10	Charge Code
11	XY-1100										33	44	55	66
35	XY-1100
Test2

Cell Formulas
Range	Formula
M8	M8	=LET(src,'Ref Data'!$A:$N,bcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(bcol),0,ROW(bcol))),data,DROP(TAKE(CHOOSECOLS(src,2,14),lrow),1), cc,INDEX(data,,1),h,INDEX(data,,2), SUM(h*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
N8	N8	=LET(src,'Ref Data'!$A:$N,CCcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,2,14),lrow),1), cc,INDEX(data,,1),h,INDEX(data,,2), fcc, FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )
C11:C35	C11	=CCN
M11:P11	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CCN	=Lists!$A$3:$A$27	C11

And when adapted to your last example, see the yellow-shaded cells:

MrExcel_20240104.xlsx
	A	B
1	correct result	incorrect result
2	493	44
3	493	101.5
Test3

Cell Formulas
Range	Formula
A2	A2	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1),cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3),SUM(h*ISNUMBER(MATCH(cc,FILTER($A11#,MAP($A11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
B2	B2	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1),cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3),SUM(h*ISNUMBER(MATCH(cc,FILTER($B11#,MAP($B11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
A3	A3	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), fcc, FILTER($A$11#,MAP($A$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )
B3	B3	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), fcc, FILTER($B$11#,MAP($B$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )

 

[PrettyGood_Not Great]

=LET(src,'Ref Data'!$A:$N,CCcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,2,14),lrow),1), cc,INDEX(data,,1),h,INDEX(data,,2), fcc, FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )

When I apply this equation I get an unfiltered result many time higher than the sum of the src data.

 

[PrettyGood_Not Great]

=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), fcc, FILTER($B$11#,MAP($B$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )

When I apply this equation I get a #VALUE. scr range, choosecols(), filter(), and map() updated to fit my working sheet.

 

[PrettyGood_Not Great]

Thanks for the example. I see the issue. I hadn't anticipated that the filtered list of Charge Codes might contain duplicates. For that scenario, the problem is caused by the MATCH(cc,FILTER($C$11#... construction, as MATCH finds only the first match. So the list of Charge Codes in the data table can only ever match to the first of any duplicates in the filtered list, and the duplicates are never considered. Here is an idea to address that: We use the same approach as before to determine the list of filtered charge codes, but rather than operating on that list right away, we establish it as a variable called "fcc" (for filtered charge codes). Then we use the BYROW LAMBDA function to process fcc one row at a time...each charge code in the "fcc" array is passed as an argument to the same SUM function we had before. This generates a subtotal for each element in the "fcc" array, which is found in the "res" array. Then all of the subtotals in "res" are summed for the final result.

Here is the original version (sans the pc component) and the revised formula using the approach just described:

MrExcel_20240104.xlsx
	C									L	M	N	O	P
8										Subtotal	65	130
9
10	Charge Code
11	XY-1100										33	44	55	66
35	XY-1100
Test2

Cell Formulas
Range	Formula
M8	M8	=LET(src,'Ref Data'!$A:$N,bcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(bcol),0,ROW(bcol))),data,DROP(TAKE(CHOOSECOLS(src,2,14),lrow),1), cc,INDEX(data,,1),h,INDEX(data,,2), SUM(h*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
N8	N8	=LET(src,'Ref Data'!$A:$N,CCcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,2,14),lrow),1), cc,INDEX(data,,1),h,INDEX(data,,2), fcc, FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )
C11:C35	C11	=CCN
M11:P11	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CCN	=Lists!$A$3:$A$27	C11

And when adapted to your last example, see the yellow-shaded cells:

MrExcel_20240104.xlsx
	A	B
1	correct result	incorrect result
2	493	44
3	493	101.5
Test3

Cell Formulas
Range	Formula
A2	A2	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1),cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3),SUM(h*ISNUMBER(MATCH(cc,FILTER($A11#,MAP($A11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
B2	B2	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1),cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3),SUM(h*ISNUMBER(MATCH(cc,FILTER($B11#,MAP($B11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
A3	A3	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), fcc, FILTER($A$11#,MAP($A$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )
B3	B3	=LET(src,$D:$F,CCcol,INDEX(src,,1),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,1,2,3),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), fcc, FILTER($B$11#,MAP($B$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )

Hi! nice attempt but something is off. the result you have in the YELLOW B3 cell needs to be the same as A2 & A3.

 

[KRice]

the result you have in the YELLOW B3 cell needs to be the same as A2 & A3

Why is that? Every charge code appearing in B11 and down contributes a sum. So if we look at just cc A-50, each of those contributes 4.5, and if we sum all of the contributions for all of the charge codes, the sum is 101.5. The earlier formula did not account for the possibility that there would be more than one of the same Charge Code in the B11 (and down) range...so it only computes a subtotal for the 1st cc A-50 (4.5) and the 1st cc A-24 (39.5)...and the sum of those is 44, which you said was incorrect...right? The revision I provided contributes a subtotal for each instance of a charge code in the B11 (and down) range. Am I missing something?

I'm not sure what to say about posts 17 and 18, as I cannot see your actual worksheet. I would recommend using just one of those formulas. If you don't want the pc criteria, then avoid that version. But you will need to update the indexes and column choices to reflect your actual data. If you can, post the 1st row or first two rows of actual data to show the column locations...sanitize data as necessary.