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.

 

[KRice]

Suppose the other sheet mentioned is called 'Ref Data' and it looks similar to this:

MrExcel_20240104.xlsx
	B									K			N
1	Charge Code									Primary Criteria			Hours
2	XY-1100									A			1
3	X-0110									B			3
4	X-0120									C			5
5	XY-1100									D			7
6	X-0110									G			9
7	X-0120									F			11
8	XY-1100									G			13
9	X-0110									A			15
10	X-0120									B			17
11	XY-1100									C			19
12	X-0110									D			21
13	X-0120									E			23
14	XY-1100									F			25
15	X-0110									D			27
16	X-0120									A			29
17
18	X-0110
19
20
Ref Data

Note that I've inserted a blank row---I'm not sure whether this might be an issue, but the solution offered here addresses it. There are three columns of interest: Charge Code, some Primary Criteria used for column matching in the main sheet, and something to sum, such as Hours (columns B, K, and N here). If these data were in a formal Excel table, we could use structured references and always be assured that we would get the entire column of data. But if the data are not in a formal Excel table and exist only in a range to be referenced, it would be beneficial to know that the entire range of data were obtained. One option is to specify an overly large range such that we are certain to cover the data plus some extra blank rows. Another option is to create dynamically formed ranges, which is what will be done below.

The first part of the formula allows the user to specify a sufficiently large range on the 'Ref Data' sheet to cover the data of interest (given the variable name src). Then, I'm assuming column B can be used to determine the lowest extent of the data, so column B is defined as bcol for convenience and then used in a formula to determine the last row number where data can be found...and this value is called lrow. Then we can extract certain columns of interest in src and trim unnecessary rows to create a smaller data array...this is done in a formula assigned to the variable "data", and it takes column indexes 2, 11, and 14 (from the original A:N), then takes the upper lrow rows, and finally drops the top 1 row where the column headings are found. This leaves us with only the data of interest in a three column array. Then each column of "data" is separated and assigned to different named variables: cc (charge code) from the 1st column, pc (primary criteria) from the 2nd column, and h (hours from the 3rd column).

Finally, the subtotal sought can be found by constructing three arrays:

1. the hours array (h)
2. a logical array indicating which elements of pc match the Primary Criteria code column headings in the summary table
3. a logical array indicating which elements of cc match any of the filtered Charge Codes in the summary table. This is the messiest part because a list of visible charge codes is necessary, and to do that, the SUBTOTAL function is used with the COUNTA option (1st argument) inside a MAP function to build an array of Charge Codes that are visibly displayed. This array is then used in a MATCH function to determine whether each element in cc matches any of the display-filtered Charge Codes...and the MATCH results are wrapped by ISNUMBER to create the logical TRUE/FALSE array.

These three arrays are multiplied together and the results summed to obtain the final subtotal. Note that this solution also uses some named ranges found on another sheet called 'Lists' (not shown),

MrExcel_20240104.xlsx
	C									L	M	N	O	P
8										Subtotal	22	20	45	55
9
10	Charge Code										G	B	A	D
11	XY-1100										33	44	55	66
12	X-0110										133	144	155	166
13	X-0120										233	244	255	266
14	X-0130
15	X-0140
16	XY-2100
Test

Cell Formulas
Range	Formula
M8:P8	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,11,14),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), SUM(h*(pc=M$10)*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
M10:P10	M10	=TRANSPOSE(PriCode)
C11:C34	C11	=CCN
M11:P14	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CCN	=Lists!$A$3:$A$26	C11
PriCode	=Lists!$E$3:$E$6	M10

 

[PrettyGood_Not Great]

Suppose the other sheet mentioned is called 'Ref Data' and it looks similar to this:

MrExcel_20240104.xlsx
	B									K			N
1	Charge Code									Primary Criteria			Hours
2	XY-1100									A			1
3	X-0110									B			3
4	X-0120									C			5
5	XY-1100									D			7
6	X-0110									G			9
7	X-0120									F			11
8	XY-1100									G			13
9	X-0110									A			15
10	X-0120									B			17
11	XY-1100									C			19
12	X-0110									D			21
13	X-0120									E			23
14	XY-1100									F			25
15	X-0110									D			27
16	X-0120									A			29
17
18	X-0110
19
20
Ref Data

Note that I've inserted a blank row---I'm not sure whether this might be an issue, but the solution offered here addresses it. There are three columns of interest: Charge Code, some Primary Criteria used for column matching in the main sheet, and something to sum, such as Hours (columns B, K, and N here). If these data were in a formal Excel table, we could use structured references and always be assured that we would get the entire column of data. But if the data are not in a formal Excel table and exist only in a range to be referenced, it would be beneficial to know that the entire range of data were obtained. One option is to specify an overly large range such that we are certain to cover the data plus some extra blank rows. Another option is to create dynamically formed ranges, which is what will be done below.

The first part of the formula allows the user to specify a sufficiently large range on the 'Ref Data' sheet to cover the data of interest (given the variable name src). Then, I'm assuming column B can be used to determine the lowest extent of the data, so column B is defined as bcol for convenience and then used in a formula to determine the last row number where data can be found...and this value is called lrow. Then we can extract certain columns of interest in src and trim unnecessary rows to create a smaller data array...this is done in a formula assigned to the variable "data", and it takes column indexes 2, 11, and 14 (from the original A:N), then takes the upper lrow rows, and finally drops the top 1 row where the column headings are found. This leaves us with only the data of interest in a three column array. Then each column of "data" is separated and assigned to different named variables: cc (charge code) from the 1st column, pc (primary criteria) from the 2nd column, and h (hours from the 3rd column).

Finally, the subtotal sought can be found by constructing three arrays:

1. the hours array (h)
2. a logical array indicating which elements of pc match the Primary Criteria code column headings in the summary table
3. a logical array indicating which elements of cc match any of the filtered Charge Codes in the summary table. This is the messiest part because a list of visible charge codes is necessary, and to do that, the SUBTOTAL function is used with the COUNTA option (1st argument) inside a MAP function to build an array of Charge Codes that are visibly displayed. This array is then used in a MATCH function to determine whether each element in cc matches any of the display-filtered Charge Codes...and the MATCH results are wrapped by ISNUMBER to create the logical TRUE/FALSE array.

These three arrays are multiplied together and the results summed to obtain the final subtotal. Note that this solution also uses some named ranges found on another sheet called 'Lists' (not shown),

MrExcel_20240104.xlsx
	C									L	M	N	O	P
8										Subtotal	22	20	45	55
9
10	Charge Code										G	B	A	D
11	XY-1100										33	44	55	66
12	X-0110										133	144	155	166
13	X-0120										233	244	255	266
14	X-0130
15	X-0140
16	XY-2100
Test

Cell Formulas
Range	Formula
M8:P8	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,11,14),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), SUM(h*(pc=M$10)*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0))))
M10:P10	M10	=TRANSPOSE(PriCode)
C11:C34	C11	=CCN
M11:P14	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CCN	=Lists!$A$3:$A$26	C11
PriCode	=Lists!$E$3:$E$6	M10

ok wow! This is excellent, you've really outdone yourself @KRice! And the explanation,wow thank you, super clear. This is a huge helping in getting to know the power of 365. Thank you.

 

[KRice]

You're welcome...happy to help.

 

[PrettyGood_Not Great]

You're welcome...happy to help.

Hi, I have a follow on to this. The solution works perfectly so I tried to apply it to a similar problem but find I can't clean it up properly. I am using this equation on another sheet and pulling the exact same data, however this time the criteria in M10 is no longer required. All data to be summed into one cell and filterable on the charge code in column C as the only criteria. Could your solution be trimmed down to accommodate this?

 

[KRice]

I think the only place where the M10 criterion applied was in the SUM function found in the M8 formula. There we have a condition for *(pc=M$10)...which creates an array of TRUE/FALSE values showing whether the Primary Criteria (column K on the Ref Data worksheet) equals the value found in cell M10. That array is multiplied by other arrays to establish which values should be summed. Try deleting the *(pc=M$10) portion of that equation and tell me if that delivers desired results...so it would read as:

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

 

[PrettyGood_Not Great]

Hi, I have a follow on to this. The solution works perfectly so I tried to apply it to a similar problem but find I can't clean it up properly. I am using this equation on another sheet and pulling the exact same data, however this time the criteria in M10 is no longer required. All data to be summed into one cell and filterable on the charge code in column C as the only criteria. Could your solution be trimmed down to accommodate this?

Hi, correction. I now have the equation working in place however it is not returning perfect results the way it does in the original scenario you explained. i.e. having the pc criteria = M10.

I was able to removed the pc,INDEX(data,,2) term and update CHOOSECOLS(src,2,11,14) to remove the second term leaving us with CHOOSECOLS(src,2,14) and it almost works. the issue I have seems to be with the match function. when using 0 for exact match the equation pulls accurate filtered results, however when the sheet is unfiltered the total is returning a sum that is slightly off. If I change the match type to -1 for less than, it returns the correct unfiltered total, but incorrect totals when filtered.

I tried to fix this by putting back the pc criteria terms and in the SUM(h*(pc=M$10) term I changed it to <>"" (or <>=0 same result) and the behavior described doesn't change.

Any thoughts on why the match type 0 works accurately in all cases when it is searching for a given pc criteria, but not when it is only looking for cc and h?

 

[PrettyGood_Not Great]

I think the only place where the M10 criterion applied was in the SUM function found in the M8 formula. There we have a condition for *(pc=M$10)...which creates an array of TRUE/FALSE values showing whether the Primary Criteria (column K on the Ref Data worksheet) equals the value found in cell M10. That array is multiplied by other arrays to establish which values should be summed. Try deleting the *(pc=M$10) portion of that equation and tell me if that delivers desired results...so it would read as:

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

Hi, this was actually my first attempt and now that I have better understanding of what's going wrong (see my corrected post), I can say that solved it. The issue is I describe in my corrected still applies here

 

[PrettyGood_Not Great]

Hi, correction. I now have the equation working in place however it is not returning perfect results the way it does in the original scenario you explained. i.e. having the pc criteria = M10.

I was able to removed the pc,INDEX(data,,2) term and update CHOOSECOLS(src,2,11,14) to remove the second term leaving us with CHOOSECOLS(src,2,14) and it almost works. the issue I have seems to be with the match function. when using 0 for exact match the equation pulls accurate filtered results, however when the sheet is unfiltered the total is returning a sum that is slightly off. If I change the match type to -1 for less than, it returns the correct unfiltered total, but incorrect totals when filtered.

I tried to fix this by putting back the pc criteria terms and in the SUM(h*(pc=M$10) term I changed it to <>"" (or <>=0 same result) and the behavior described doesn't change.

Any thoughts on why the match type 0 works accurately in all cases when it is searching for a given pc criteria, but not when it is only looking for cc and h?

p.s. I tried your other solution from an earlier thread, where you start with the SUM function and use set ranges for the three data sets. That equation has the same behavior in this regard. i.e. it works perfectly when searching for a pc criteria, but when only looking at cc and h, the results are the same as described above.

p.p.s. also typo in original description, I referred to accurate unfiltered results when using match type -1, it should read accurate for type 1, greater than.

 

[KRice]

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