Forum Discussion
Warning in Excel when trying to use dynamic array
Here's a solution (not double-checked) using several Dynamic Array functions.
OK mocked up an example using SUMIFS()... Interestingly did not get the message again, so I believe I misinterpreted it. Looks like it was just informational rather than a warning. If I could morph the question, I would update it to be more along the lines of, "am I doing this right???"
Note: I do realize the use case is awkward, and a pivot table would be more efficient here. Unfortunately, I also have users who are less than facile with pivot tables and feel uncomfortable when they see one (or they interact with it and do silly things like take averages of averages). Based on user preference, I form things into "just a table of numbers," and it is also an interesting exercise for me to see if I can replicate any given pivot table using only SUMIFS and similar functions.
If you don't have the new function PIVOTBY then SUMIFS will do the job perfectly well, and to spill range can be made to broadcast across and down.
= LET(
distinctProduct, SORT(UNIQUE(order_summary[ProductKey])),
monthNumber, TOROW(SORT(UNIQUE(order_summary[order_month]))),
subtotals, SUMIFS(order_summary[SalesSubTotal], order_summary[ProductKey], distinctProduct, order_summary[order_month], monthNumber),
grandTotal, HSTACK("Total", BYCOL(subtotals, SUM)),
VSTACK(HSTACK("productKey", monthNumber), HSTACK(distinctProduct, subtotals), grandTotal)
)More effort goes into stacking the result than setting up the calculation! With PIVOTBY the formula reduces to
=PIVOTBY(
order_summary[ProductKey],
order_summary[order_month],
order_summary[SalesSubTotal],
SUM, , , , 0
)