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