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]

Okay...then can you explain what the problem is with the formula in cell B4 shown back in post #25 about 6 weeks ago? That formula takes only unique values of visible charge codes shown in C11#. And C11# is populated by your CCN named variable, which includes a lot of different charge codes (24 unique codes). If you use the dropdown arrow (autofilter) in cell C10 and select all charge codes, among those 24 cc's are X-0110, X-0120, and XY-1100...as well as another 21 other cc's. In your 'Ref Data' worksheet, which contains the data source that the formula uses for charge code lookups and hours to be summed (see the minisheet for it in my last post #39), only the three cc's mentioned are found in the data table. So you cannot possibly sum any contributions from any other charge codes that do not appear in your data table, and the autofilter in cell C10 is used specify which charge codes to consider for the sum.

I can use the autofilter to select all 24 cc's in C11 and down, but only three of them are actually contributing to the total sum of 225 because the data table on the 'Ref Data' worksheet contains entries for only three unique charge codes. If I use the autofilter to select only X-0110, the total sum is 75... select only X-0120, the total sum is 85... select only XY-1100, the total sum is 65... select all three of those just mentioned, the total sum is 225 (75+85+65...no surprise)... select all 24 cc's, the total sum is still 225 because the data source on 'Ref Data' has no entries with those other charge codes. No special consideration is needed to determine if the list is filtered because the autofilter button serves that function when used in conjunction with the formula that considers only those cc's that are visible in the C11# list.

Here is the formula again, adapted to the 'Ref Data' worksheet shown in my last post. If you want the total sum of hours in 'Ref Data', then the autofilter is simply used to select all charge codes (or just the ones that can be found in 'Ref Data'. From my perspective, I don't think there is a problem with the formula's behavior (other than it being somewhat sluggish), but I think there might be a disconnect between the CCN named variable and the charge codes found in the 'Ref Data' source table.

MrExcel_20240104.xlsx
	A	B	C
3	Charge Codes in 'Ref Data' source
4	X-0110
5	X-0120
6	XY-1100
7
8		Subtotal	140
9
10			Charge Code
11			XY-1100
12			X-0110
35			XY-1100
36
Test4

Cell Formulas
Range	Formula
A4:A6	A4	=SORT(UNIQUE('Ref Data'!B2:B16))
C8	C8	=LET(src,'Ref Data'!$A:$N,CCcol,INDEX(src,,2),lrow,MAX(IF(ISBLANK(CCcol),0,ROW(CCcol))),data,DROP(TAKE(CHOOSECOLS(src,2,11,14),lrow),1), cc,INDEX(data,,1),pc,INDEX(data,,2),h,INDEX(data,,3), fcc, UNIQUE(FILTER(C11#,MAP(C11#,LAMBDA(r,--(SUBTOTAL(103,r)=1))))), res, BYROW(fcc,LAMBDA(r,SUM(h*ISNUMBER(MATCH(cc,r,0))))), SUM(res) )
C11:C35	C11	=CCN
Dynamic array formulas.

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