Alternate to SUMIFS array assistance

Hello, everyone. I apologize for asking yet another SUMIFS question, but I haven't been able to find a thread anywhere (so far) that offers a sample similar to what I'm trying to do. I have a...

RGB Sample.xlsx16 KB

excel

Formulas and Functions

djclements

Mar 15, 2025

A few more array options to experiment with:

=MMULT(--(TOROW(Table2[Date]) = Table3[Date]), (XLOOKUP(Table2[Item Code], Table1[Item Code], Table1[Category], "") = Table3[[#Headers],[Blue]:[Red]]) * Table2[Units])

Complete with column headers and row labels:

=LET(
   criteria, DROP(Table3[#Headers],, 1),
   category, XLOOKUP(Table2[Item Code], Table1[Item Code], Table1[Category], ""),
   result,   MMULT(--(TOROW(Table2[Date]) = Table3[Date]), (category = criteria) * Table2[Units]),
   HSTACK(Table3[[#All],[Date]], VSTACK(criteria, result))
)

Alternatively, using SUM-SUMIFS with "thunks" (LAMBDA(x)) and broadcasting:

=LET(
   grϑup,  TRANSPOSE(GROUPBY(Table1[Category], Table1[Item Code], LAMBDA(x, LAMBDA(x)), 0, 0)),
   bcλst,  LAMBDA(b, IF(b, Table3[Date], TAKE(grϑup, -1))),
   result, MAP(bcλst({0}), bcλst({1}), LAMBDA(c,d, SUM(SUMIFS(Table2[Units], Table2[Date], d, Table2[Item Code], c())))),
   HSTACK(Table3[[#All],[Date]], VSTACK(TAKE(grϑup, 1), result))
)

There's also a basic SUMIFS example in the attached file (on the Production worksheet), using a helper column for "Category"...

RGB Sample.xlsx18 KB

SergeiBaklan

Diamond Contributor

Mar 13, 2025

If you are on Windows, you may add both table to data model, create relationship

and generate PivotTable

Or you may generate Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    DeclareType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    MergeTable1 = Table.NestedJoin(DeclareType, {"Item Code"}, Table1, {"Item Code"}, "Table1", JoinKind.LeftOuter),
    ExpandTable1 = Table.ExpandTableColumn(MergeTable1, "Table1", {"Category"}, {"Category"}),
    SelectColumns = Table.SelectColumns(ExpandTable1,{"Date", "Category", "Units"}),
    PivotColumn = Table.Pivot(SelectColumns, List.Distinct(SelectColumns[Category]), "Category", "Units", List.Sum)
in
    PivotColumn

which returns

RGB Sample.xlsx196 KB

Eng_Noah

Brass Contributor

Mar 17, 2025

We used to use PowerQuery, but the person that was in charge of this kept forgetting to update it. I know we can automate the tasks, but we rather reduce the number of potential failure points. That's why we're doing a dynamic array. The only necessary part is the manual data entry.

If something breaks, it's also much easier to track down. I'm not working with tech savvy people :/.

SergeiBaklan
Diamond Contributor
Mar 20, 2025
I see. Yes, with PowerQuery is headache with refreshing. But with dynamic arrays is headache with formatting.

Forum Discussion

Alternate to SUMIFS array assistance

Resources