Sumproduct of arrays using lookups between tables-CodePudding

Using O365

In F2, find sum for Type "Revenue" and Group "A" using the Amount & Allocation % Tables.

F2 = $10,000 * 10 % $15,000 * 20% = $4,000

10% = ACCT A

20% = BRRT A

I'm trying to avoid adding helper columns to the Amount Table such as:

Using =XLOOKUP(Table1[@[Dept]:[Dept]]&Table1[[#Headers],[A]],Table2[[Dept]:[Dept]]&Table2[[Group]:[Group]],Table2[[Alloc%]:[Alloc%]],0)*Table1[@[Amount]:[Amount]]

So far, I am stumped about how to proceed without the use of Helper Columns and a SUMIFS in J2. This method would use too many resources given my dataset size (200k rows).

Any ideas? SUMPRODUCT with SUMIFS? Power BI table links and DAX? TIA

CodePudding user response：

you are on the right tract with SUMPRODUCT and SUMIFS:

=SUMPRODUCT(SUMIFS(Table1[[Amount]:[Amount]],Table1[[Type]:[Type]],Table4[@[Type]:[Type]],Table1[[Dept]:[Dept]],Table2[[Dept]:[Dept]])*Table2[[Alloc %]:[Alloc %]]*(Table2[[Group]:[Group]]=Table4[[#Headers],[A]]))

Table1 = A1:C5
Table2 = A7:15
Table4 = E1:I3