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]

The formulas in this thread are structured around determining which rows in the Reference Data match certain criteria, and then summing the hours in those rows. Two formula versions were requested:

1. where the rows have a Primary Criteria (pc) that matches a specific Primary Criteria in the results worksheet AND those same rows also have a Charge Code that matches any that are visible in the results filtering list.
2. where the rows have a Charge Code that matches any that are visible in the results filtering list (no primary criteria).

I am not aware of a request for an "unfiltered" version of the formula. Where was that described? How would it work? How would one know that all Charge Codes in the Reference Data are to be considered; or put differently, that all visible charge codes in the filtered list on the results worksheet should be ignored? I've already suggested this could be done with a simple solution...

=SUM(F2:F99)

Are you expecting something different? If so, you have not explained the conditions that should trigger a complete sum.

In your example in post #15, you include a set of Reference Data whose total sum of hours is 493, but that data set uses only four Charge Codes (A-10, A-23, A-24, A-50). Your "Problem List" of Charge Codes---that presumably represents what someone might create as a visible filtered list of Charge Codes that should be matched to the Reference Data---consists of many duplicates. Among all of those duplicates are nine unique Charge Codes that produce a sum of 44. Those nine unique codes are shown in my post #27, cell H11#. Importantly, 44 is the correct sum because among the nine unique Charge Codes, only two of them (A-24 and A-50) match those in the Reference Data. There is no contribution to the sum from any of the Reference Data whose charge codes are A-10 or A-23 because those do not appear in the "Problem List".

I go back to the original problem. This equation works when filtered on a charge code, and does not work when unfiltered. There is an issue with the Match function. As originally stated, if I set the match type to 1 = greater than, it returns the correct total sum of the reference data in the unfiltered condition. If I leave it as 0 = exact match it only returns correct data when filtered and when unfiltered it returns approx. half of the total data.

Yes, I could use a Sum(), but then I would not be able to filter....The intention here is as stated, to be able to use this equation on a list that has either one occurrence of a cc, or many. regardless of either condition, if the equation can't also pull a simple sum when 'unused' (unfiltered), then it is of no use in solving my problem.

Not sure how else to explain this. Thanks for trying.

 

[KRice]

Not sure how else to explain this

The answer to this…is to simply answer the question I asked. Again, how would one (or a formula) know that you want to ignore all filters? You’ve never explained that. If you specify something in the Charge Code listing, you will get a sum based on using those visible charge codes in a filter. What would you do differently to tell the formula to not apply the filter?

Do you want a separate cell, that if checked, means to ignore the filter and return the entire sum? I’m only guessing, but you haven’t explained those details.

 

[KRice]

Here is what the last idea would look like, but I have no idea how you want to turn on/off a filter:
For this data set...

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

Here is the visible Charge Code filtering turned on/off with the orange cell:

MrExcel_20240104.xlsx
	C									L	M	N	O	P
6	Enter "X" to Ignore All Filtering ->									X
7
8										Subtotal	225
9
10	Charge Code
11	XY-1100										33	44	55	66
35	XY-1100										33	44	55	66
36
37
Test2

Cell Formulas
Range	Formula
M8	M8	=LET(filtertrigger, $L$6, 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), IF(filtertrigger="X",SUM(h), SUM(h*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))) )
C11:C35	C11	=CCN
M11:P11,M35:P35	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

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

MrExcel_20240104.xlsx
	C									L	M	N	O	P
6	Enter "X" to Ignore All Filtering ->
7
8										Subtotal	65
9
10	Charge Code
11	XY-1100										33	44	55	66
35	XY-1100										33	44	55	66
36
37
Test2

Cell Formulas
Range	Formula
M8	M8	=LET(filtertrigger, $L$6, 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), IF(filtertrigger="X",SUM(h), SUM(h*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))) )
C11:C35	C11	=CCN
M11:P11,M35:P35	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

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

 

[PrettyGood_Not Great]

Here is what the last idea would look like, but I have no idea how you want to turn on/off a filter:
For this data set...

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

Here is the visible Charge Code filtering turned on/off with the orange cell:

MrExcel_20240104.xlsx
	C									L	M	N	O	P
6	Enter "X" to Ignore All Filtering ->									X
7
8										Subtotal	225
9
10	Charge Code
11	XY-1100										33	44	55	66
35	XY-1100										33	44	55	66
36
37
Test2

Cell Formulas
Range	Formula
M8	M8	=LET(filtertrigger, $L$6, 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), IF(filtertrigger="X",SUM(h), SUM(h*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))) )
C11:C35	C11	=CCN
M11:P11,M35:P35	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

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

MrExcel_20240104.xlsx
	C									L	M	N	O	P
6	Enter "X" to Ignore All Filtering ->
7
8										Subtotal	65
9
10	Charge Code
11	XY-1100										33	44	55	66
35	XY-1100										33	44	55	66
36
37
Test2

Cell Formulas
Range	Formula
M8	M8	=LET(filtertrigger, $L$6, 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), IF(filtertrigger="X",SUM(h), SUM(h*ISNUMBER(MATCH(cc,FILTER($C$11#,MAP($C$11#,LAMBDA(r,--(SUBTOTAL(103,r)=1)))),0)))) )
C11:C35	C11	=CCN
M11:P11,M35:P35	M11	=IFERROR(INDIRECT("'"&$C11&"'!"&CELL("address",H$13)),"")
Dynamic array formulas.

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

This is really clever! I will give some thought to how I could integrate the trigger within my sheet such that the operation is implicit. Thanks for another cool solution!

 

[PrettyGood_Not Great]

This is really clever! I will give some thought to how I could integrate the trigger within my sheet such that the operation is implicit. Thanks for another cool solution!

Here's a thought. could a recursive lambda be used to check if the list is filtered, and if so apply the trigger? havn't learned recursive yet so just a shot in the dark

 

[KRice]

I think we may have a disconnect somewhere. Can you explain to me how we would know whether the list of Charge Codes is filtered? Go back to your post #15 where two different sets of Charge Codes are shown: one labeled "Working List", the other labeled "Problem List". I don't understand how these lists are first created. And without knowing that, I can't tell whether the presented list is a subset of something (therefore it is "filtered") or if the list itself is the original source. I don't know if this description is clear enough. In the "Working List", no charge code appears more than once, but many of the charge codes shown are not reflected in the main data table (column D in post #15). Is that only because the main data table is abbreviated for posting purposes, and if the full table were present, we would find those other charge codes in it? Put differently, if a list of unique charge codes found in the main data table were constructed, would that be considered a complete list? The we could compare the unique charge codes in the main data table to the unique charge codes shown the query area (cells A9 and B9 and down in the same post #15) to determine whether the query area is filtered?

 

[PrettyGood_Not Great]

I think we may have a disconnect somewhere. Can you explain to me how we would know whether the list of Charge Codes is filtered? Go back to your post #15 where two different sets of Charge Codes are shown: one labeled "Working List", the other labeled "Problem List". I don't understand how these lists are first created. And without knowing that, I can't tell whether the presented list is a subset of something (therefore it is "filtered") or if the list itself is the original source. I don't know if this description is clear enough. In the "Working List", no charge code appears more than once, but many of the charge codes shown are not reflected in the main data table (column D in post #15). Is that only because the main data table is abbreviated for posting purposes, and if the full table were present, we would find those other charge codes in it? Put differently, if a list of unique charge codes found in the main data table were constructed, would that be considered a complete list? The we could compare the unique charge codes in the main data table to the unique charge codes shown the query area (cells A9 and B9 and down in the same post #15) to determine whether the query area is filtered?

Hi, This is exactly correct and I've suspected that the presentation of the abbreviated list has been our disconnect. The Mini sheet wouldn't take all data so I squished it.

I go back to your solution from the other thread. There was only one occurrence of each charge code on the working sheet so we never saw this issue. That behavior was such that when the list was unfiltered it produced the total sum of data from the source, and when filtered by CCN it produced the respective subtotal.

Then we apply this same solution to another sheet, where there are multiple occurrences of each charge code. The behavior is that when unfiltered we get a false return wrt to the sum of source data (for some reasons approx. half the total value in my working file), but when filtered we again get the correct respective subtotal from the source data.

For my limited inputs here: I guess I can see why the unfiltered list produces a false sum from the source data (why it's less and not greater however, i'm not sure) as there are multiple CC occurances. Why though it continues to produces the correct subtotal when filtered is less obvious to me. Reason being that on this sheet, when filtered, there are still multiple occurrences of the CC.

 

[KRice]

In post 36, I asked:

Put differently, if a list of unique charge codes found in the main data table were constructed, would that be considered a complete list?

Is the answer to that question “yes”…or “no”?

 

[KRice]

I think my disconnect is captured somewhere in these statements:

The Mini sheet wouldn't take all data so I squished it.

Then we apply this same solution to another sheet, where there are multiple occurrences of each charge code. The behavior is that when unfiltered we get a false return wrt to the sum of source data (for some reasons approx. half the total value in my working file), but when filtered we again get the correct respective subtotal from the source data.

Do you have a small working example that can show both conditions: filtered and unfiltered charge codes using the same table structure and same data? Please make the examples as complicated as they need to be to illustrate the difference, and importantly, show what the desired answers should be, even if they are manually calculated. The examples do not need to be long, just complete enough to illustrate the issue. I've been working with this source data on the 'Ref Data' worksheet:

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
Ref Data

...15 rows showing some combination of charge code (CC), primary criteria (PC), and hours. The total hours is 225. Only 3 unique charge codes are represented in this source data table.

On a separate worksheet, which I'll call the Test worksheet, you have a list of charge codes, perhaps some are even repeated more than once (is this correct?), and this list can be filtered (is this correct?).
Then you want to consider only the unique, visible CC's on Test..."unique" meaning that if a CC appears more than once on Test, we consider only one instance and ignore the other(s)...is this correct?
Then for each of the unique CC's on Test, we examine the source data on 'Ref Data' and sum all hours where the CC matches...is this correct?

The formula shown earlier does this, yet you are describing some type of issue regarding filtered/unfiltered charge codes, but I'm not seeing it...so two examples drawing from the same setup may help to illustrate the issue.

 

[PrettyGood_Not Great]

In post 36, I asked:

Is the answer to that question “yes”…or “no”?

yes