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]

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.

Hi, the total of 493 in the mini sheet example represents the total of all hours available to be summed, regardless of which charge code they correspond to, and this is the result we want when unfiltered - (this dynamic total is indepenent of what is on the working sheet). The application of this equaion, in this context is to show the total hours, and allow a subtotal of those hours to be shown when filtered on a given charge code. Although there are multiple occurances of each charge code on the working sheet, those listings are dissperate from the source data. The desired behavior is identical to that if there were only one instance of each charge code on the working sheet.

Your reduced equation would also be my preference and it works as you designed it, however that design is not the desired result for this application. The total summed hours cannot be greater than what is available in the source data. Does this help?

 

[KRice]

Does this help?

Unfortunately, no. You provided a mini sheet with the problematic version of formula. The reason the formula is problematic is that it was unable to address the scenario where duplicate charge codes appeared in the filtered list (as opposed to the source data list, which I originally understood would have duplicate charge codes, perhaps with different hours). So the problematic version of the formula was developed with the mistaken understanding that the filtered list would have potentially multiple charge codes, but none of them would be duplicated. To demonstrate the problem, you offered the mini-sheet, which uses one common source data set whose total hours sum to 493, and within that data set, those 493 hours are distributed among 4 different charge codes (A-10, A-23, A-24, and A-50). To demonstrate that the problematic formula works as desired when no duplicate charge codes appear in the filtered charge code list, you created such a list in A11:A23, and within that range, all four of source data charge codes appear just once, along with other charge codes. So the formula computes the correct result in this case, and is shown in the green cell labeled "correct result". The revised formula is shown immediately below in A3 and it also returns the same result of 493 (referencing the "working list" charge codes in A11:A23).

To illustrate the problem, you created a list of charge codes in B11:B88 that does contain multiple instances of duplicate charge codes. The problematic version of the formula, when referencing the B11:B88 list of charge codes, gives a result of 44, which you labeled "incorrect result"...because only the first instance of each duplicate set of charge codes is contributing toward the sum. The revised formula is shown immediately below in B3, with a result of 101.5, as each instance of the charge code in the filtered list contributes the same subtotal to the total sum. This is independently verified here (showing only a small sample to illustrate the idea), where the full list of "filtered" charge codes is listed in J11 and down, and the subtotal for each generated in K11 and down, along with the total sum of 101.5. Contrast that result with the one obtained when using a unique list of charge codes (appear only once) found in the full list of "filtered" charge codes. The unique list of cc is shown in M11:M19, their subtotals in N11:N19, and their total sum is 44.

The total summed hours cannot be greater than what is available in the source data.

So the total summed hours in the source table is still 493, but you could easily exceed it many ways. For example, take the working list that produces the full sum and simply add one additional duplicate charge code that contributes some hours toward the sum...this is why I never anticipated that the filtered list of charge codes would have duplicates. What am I misunderstanding? Could you breakdown what the expected result is when the "problem list" of Charge Codes (B11:B88) is used...the total as well as the contributing subtotals?

MrExcel_20240104.xlsx
	J	K	L	M	N
10		101.5			44
11	A-50	4.5		A-21	0
12	A-50	4.5		A-24	39.5
13	A-50	4.5		A-30	0
14	A-50	4.5		A-32	0
15	A-50	4.5		A-34	0
16	A-21	0		A-50	4.5
17	A-30	0		A-60	0
18	A-32	0		A-65	0
19	A-70	0		A-70	0
20	A-24	39.5
21	A-30	0
22	A-34	0
Test3

Cell Formulas
Range	Formula
K10	K10	=SUM(K11:K88)
J11:J22	J11	=B11
K11:K22	K11	=SUMIF($D$2:$D$99,$J11,$F$2:$F$99)
M11:M19	M11	=SORT(UNIQUE(B11:B88))
N10	N10	=SUM(N11:N19)
N11:N19	N11	=SUMIF($D$2:$D$99,$M11,$F$2:$F$99)
Dynamic array formulas.

 

[PrettyGood_Not Great]

What am I misunderstanding? Could you breakdown what the expected result is when the "problem list" of Charge Codes (B11:B88) is used...the total as well as the contributing subtotals?

The expected results, whether it is actually possible or not, is the same as the working list. Your walk through summary above is accurate as this is what has been provided and it behaves the way we have both described.

The goal, if possible, is to be able to pull the total unfiltered sum of data, 493, whether the active sheet has one occurrance of each charge code, or multiple of any given charge code. maybe this is not possible, or it needs a different approach.

 

[KRice]

The expected results, whether it is actually possible or not, is the same as the working list.

Oh...are you saying that if the application of a filter results in duplicate charge codes being shown (in the filtered list), you want that list trimmed down so that only unique charge codes are shown (i.e., there would be no duplicates). And then you want total sum of hours for all entries in the main data table whose charge code matches one of the displayed/filtered charge codes? But if this is the case, then in your mini sheet, wouldn't the correct answer be 44?...(but you have it notated as "incorrect result"). What is the correct answer for the B11:B88 charge code list?

 

[KRice]

The goal, if possible, is to be able to pull the total unfiltered sum of data, 493, whether the active sheet has one occurrance of each charge code, or multiple of any given charge code. maybe this is not possible, or it needs a different approach.

If this is really what you want, then your description in post #5 is misleading:

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?

Apparently you do not want to filter the sum based on the "charge code in column C as the only criteria"...you actually want to completely ignore the charge code in column C?
I'm not clear on what is needed. If a sum of all hours in the data table is desired, why not just use SUM as shown in B5?

MrExcel_20240104.xlsx
	A	B	C	D	E	F
1	correct result	incorrect result		cc	pc	h
2	493	44		A-24	AB	16
3	493	101.5	subtotals for every cc, including duplicates	A-24	AB	4
4		44	subtotals for unique cc, de-duplicated	A-24	AB	1
5		493	ignores cc filtering altogether	A-24	AB	5
6				A-24	AB	2.5
7				A-24	AB	2
8				A-24	AB	1
9	working list	Problem list		A-24	AB	1.5
10	Charge Code	Charge Code		A-24	AB	1
11	A-10	A-50		A-24	AB	0.5
12	A-21	A-50		A-24	AB	1
13	A-22	A-50		A-24	AB	0.5
14	A-23	A-50		A-24	AB	0.5
15	A-24	A-50		A-24	AB	0.5
16	A-25	A-21		A-24	AB	1
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))))
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) )
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))))
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) )
B4	B4	=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, UNIQUE(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) )
B5	B5	=SUM(F2:F99)
A11:A23	A11	=wl
B11:B88	B11	=pl
Dynamic array formulas.

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

 

[PrettyGood_Not Great]

If this is really what you want, then your description in post #5 is misleading:

Apparently you do not want to filter the sum based on the "charge code in column C as the only criteria"...you actually want to completely ignore the charge code in column C?
I'm not clear on what is needed. If a sum of all hours in the data table is desired, why not just use SUM as shown in B5?

MrExcel_20240104.xlsx
	A	B	C	D	E	F
1	correct result	incorrect result		cc	pc	h
2	493	44		A-24	AB	16
3	493	101.5	subtotals for every cc, including duplicates	A-24	AB	4
4		44	subtotals for unique cc, de-duplicated	A-24	AB	1
5		493	ignores cc filtering altogether	A-24	AB	5
6				A-24	AB	2.5
7				A-24	AB	2
8				A-24	AB	1
9	working list	Problem list		A-24	AB	1.5
10	Charge Code	Charge Code		A-24	AB	1
11	A-10	A-50		A-24	AB	0.5
12	A-21	A-50		A-24	AB	1
13	A-22	A-50		A-24	AB	0.5
14	A-23	A-50		A-24	AB	0.5
15	A-24	A-50		A-24	AB	0.5
16	A-25	A-21		A-24	AB	1
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))))
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) )
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))))
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) )
B4	B4	=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, UNIQUE(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) )
B5	B5	=SUM(F2:F99)
A11:A23	A11	=wl
B11:B88	B11	=pl
Dynamic array formulas.

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

I think reading all this you may be trying to connect the dots between the source data and what's on the active sheet. For the purpose of explanation there is no relationship. Let me try to explain the desired result by proposing a modification (conceptual) to your original solution from an earlier thread.

When we had only one occurrence of each charge code on the active sheet we have an equation that will look at what cc's are visible on the filtered list, then go to the source data and sum any and all hours that correspond to what's visible on the active sheet. What if that same equation instead of just accepting its search criteria as what is visible on the active sheet it had a step to look for the first occurrence of each charge code and ignored the rest. The result of this equation would then be the same for an active sheet that had only one occurrence of each cc as it would for an active sheet that had multiple occurrences of each cc.

The application I have for this modification is to hold a sum total of the source data on an active sheet that also contains the charge codes - the data related to those charge codes on the active sheet is not the same type of data as the source sheet. There is a relationship between these data sources when filtered however (intended at least). Imagine for a moment that the charge codes were dates instead. When unfiltered I want the sum total of all data from the source sheet, but when I filter for a date on the active sheet I want just that data that corresponds to that date, regardless if there is one row that holds the filtered date or many. I still only want just the sum of data that corresponds to the filtered date, from the source sheet.

 

[KRice]

Thanks for this clarification. For the purposes of your question, I'm considering the source data as a relatively static data set from which a "subtotal" is needed, and that subtotal depends on some conditions that the user wishes to control using filtering options. Regarding this sentence:

What if that same equation instead of just accepting its search criteria as what is visible on the active sheet it had a step to look for the first occurrence of each charge code and ignored the rest.

...I'd like to confirm something that is somewhat ambiguous. I'm interpreting this to mean that the formula should look only at the first occurrence of each Charge Code in the filtered list. For example, take the B11 list in your test mini sheet where the first five entries are cc A-50...we would only consider the first of those and ignore all other A-50's in the filtered list. Then the formula would return a subtotal for that A-50 cc representing the sum of all source data hours satisfying the cc A-50 matching criteria. Your last paragraph in the previous post seems to confirm this interpretation, but please clarify otherwise.

the data related to those charge codes on the active sheet is not the same type of data as the source sheet. There is a relationship between these data sources when filtered however

I don't really understand this point, and perhaps this is what prompted your "connect the dots" comment, but I'm not sure its relevant, is it? You want to use the Charge Codes in the filtered list as a matching criterion for identifying which rows in your source data should be used for the subtotals.

Still, I do not understand why 44 is an incorrect answer in your test mini sheet when the B11 "problem list" of charge codes is used. The formula that delivers a result of 44 is considering only unique and visible charge codes in the filtered list. At the risk of making this post extra long, I'll repost the sheet in question and show the unique codes that appear in the "problem list" of charge codes (see H11# in yellow), as well as the subtotal of hours in the main source table for each of those unique charge codes (see I11# in yellow)...and the total of those is 44. The formula presented earlier, and shown in B4 produces this result. How do you obtain a result of 493 for this data set?

MrExcel_20240104.xlsx
	A	B	C	D	E	F	G	H	I
1	correct result	incorrect result		cc	pc	h
2	493	44		A-24	AB	16
3	493	101.5	subtotals for every cc, including duplicates	A-24	AB	4
4		44	subtotals for unique cc, de-duplicated	A-24	AB	1
5		493	ignores cc filtering altogether	A-24	AB	5
6				A-24	AB	2.5
7				A-24	AB	2
8				A-24	AB	1
9	working list	Problem list		A-24	AB	1.5
10	Charge Code	Charge Code		A-24	AB	1			44
11	A-10	A-50		A-24	AB	0.5		A-21	0
12	A-21	A-50		A-24	AB	1		A-24	39.5
13	A-22	A-50		A-24	AB	0.5		A-30	0
14	A-23	A-50		A-24	AB	0.5		A-32	0
15	A-24	A-50		A-24	AB	0.5		A-34	0
16	A-25	A-21		A-24	AB	1		A-50	4.5
17	A-30	A-30		A-24	AB	0.5		A-60	0
18	A-32	A-32		A-24	AB	1		A-65	0
19	A-34	A-70		A-10	AG	1.5		A-70	0
20	A-50	A-24		A-23	AG	2.5
21	A-60	A-30		A-23	AG	1.5
22	A-65	A-34		A-10	AG	3.5
23	A-70	A-32		A-23	AG	36.75
24		A-60		A-23	AG	77.25
25		A-21		A-23	AG	1.75
26		A-70		A-23	AG	8
27		A-70		A-23	AG	1
28		A-65		A-23	AG	19
29		A-24		A-23	AG	0.25
30		A-30		A-23	AG	1
31		A-34		A-23	AG	0.5
32		A-30		A-23	AG	7.5
33		A-21		A-23	AG	1
34		A-21		A-23	AG	8.75
35		A-30		A-23	AG	0.5
36		A-30		A-23	AG	1
37		A-30		A-23	AG	2.5
38		A-30		A-23	AG	0.5
39		A-30		A-23	AG	7.5
40		A-30		A-23	AG	1.5
41		A-30		A-23	AG	3
42		A-30		A-23	AG	1.5
43		A-30		A-23	AG	5
44		A-32		A-23	AG	4
45		A-32		A-23	AG	6.5
46		A-32		A-23	AG	2
47		A-32		A-23	AG	3
48		A-32		A-23	AG	5.5
49		A-32		A-23	AG	1
50		A-32		A-23	AG	0.5
51		A-32		A-10	AG	24
52		A-32		A-10	AG	2.5
53		A-32		A-23	AG	23.75
54		A-32		A-23	AG	8
55		A-32		A-23	AG	7
56		A-32		A-10	AK	1
57		A-32		A-10	AK	3.5
58		A-32		A-10	AK	4
59		A-34		A-10	AK	0.5
60		A-34		A-10	AK	1
61		A-34		A-10	AK	1
62		A-34		A-10	AK	1.5
63		A-34		A-10	AK	0.5
64		A-34		A-10	AK	0.5
65		A-34		A-50	AK	1
66		A-34		A-50	AK	1
67		A-34		A-50	AK	0.75
68		A-34		A-10	AK	1
69		A-34		A-10	AK	1.25
70		A-34		A-10	AK	1.5
71		A-34		A-10	AK	0.25
72		A-34		A-10	AK	1
73		A-60		A-10	AK	7.5
74		A-60		A-10	AK	4.25
75		A-60		A-10	AK	13
76		A-60		A-10	AK	4.5
77		A-60		A-10	AK	3
78		A-60		A-10	AK	10
79		A-60		A-10	AK	14.5
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
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))))
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) )
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))))
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) )
B4	B4	=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, UNIQUE(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) )
B5	B5	=SUM(F2:F99)
A11:A23	A11	=wl
B11:B88	B11	=pl
H11:H19	H11	=SORT(UNIQUE(B11:B88))
I10	I10	=SUM(I11:I19)
I11:I19	I11	=SUMIF($D$2:$D$99,$H11,$F$2:$F$99)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
pl	=Test3!$K$2:$K$79	B11
wl	=Test3!$L$2:$L$14	A11

 

[PrettyGood_Not Great]

...I'd like to confirm something that is somewhat ambiguous. I'm interpreting this to mean that the formula should look only at the first occurrence of each Charge Code in the filtered list. For example, take the B11 list in your test mini sheet where the first five entries are cc A-50...we would only consider the first of those and ignore all other A-50's in the filtered list. Then the formula would return a subtotal for that A-50 cc representing the sum of all source data hours satisfying the cc A-50 matching criteria. Your last paragraph in the previous post seems to confirm this interpretation, but please clarify otherwise.

Hi, correct on this

 

[PrettyGood_Not Great]

Still, I do not understand why 44 is an incorrect answer in your test mini sheet when the B11 "problem list" of charge codes is used. The formula that delivers a result of 44 is considering only unique and visible charge codes in the filtered list. At the risk of making this post extra long, I'll repost the sheet in question and show the unique codes that appear in the "problem list" of charge codes (see H11# in yellow), as well as the subtotal of hours in the main source table for each of those unique charge codes (see I11# in yellow)...and the total of those is 44. The formula presented earlier, and shown in B4 produces this result. How do you obtain a result of 493 for this data set?

The sum total of the data is 493 regardless of which list we reference, so when unfiltered both equations should produce the same answer of 493.

 

[KRice]

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".