Forum Discussion
Warning in Excel when trying to use dynamic array
Hi there, I did a search on this before posting, but no luck yet. Consequently, if this has already been answered, please point me there. With that said, I am a "seasoned" user of Excel (aka old), and I am used to the old school CSE array formula. So now I am supposed to use dynamic arrays. OK I like learning new things, so I gave it a spin. I got a message from Excel about spilling an array that read like a warning or otherwise suggesting I did a Bad Thing. Looking that up brought me here.
USE CASE (in case it matters)
What I wanted to do was use a SUMIFS function to sum over a date range that was in another sheet arranged vertically (in a table), but put the result in a grid (i.e., not a table) that had the dates horizontally. The objective is to subtotal the amount for each day over a list that had many entries for each date. So maybe it was the transposition that made Excel throw a warning, but using the CSE formula works fine.
-----/END USE CASE
I can easily resolve this using a Pivot Table, but that's not my question. Really, I want to know why is MS complaining about doing something its own documentation has said is preferrable to the legacy method? I don't know who prevails in this disagreement. Is the documentation or the Excel internal messaging dominant in this case? Either way it undermines the usefulness of these warnings and tips, which I am turning off because they are worse than useless.
Here's a solution (not double-checked) using several Dynamic Array functions.
One seasoned (i.e., "old"--having, in my case, turned 83 last week) user to another, it would help if you could post a working example of the Excel sheet you're working with. If the actual contains confidential info, put together a mockup that doesn't.
If you are reluctant to do either of those, might I suggest that you not begin with SUMIF, but rather some of the other Dynamic Array functions. For example, I've found FILTER (which can handle multiple criteria more robustly than SUMIF) nested within a SUM, as a possible alternative to SUMIF.
Sorry, it is taking me longer to get a sample together than I thought. Got pulled away by other stuff. I do appreciate the link to the dynamic array functions. What I am learning now is stuff that I was doing manually in 2007 via a series of nested IF/INDEX/MATCH fns.
Stick with it. Modern, dynamic array, versions are an order of magnitude improvement on the old spreadsheet junk one knew and loved! I used CSE all the time, but it was painful.
A function that could be useful as a way of creating a range of conditional sums would be one of GROUPBY and PIVOTBY.
- Oops, was going to include link to documentation I referenced. The confusion resides here: https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
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 )
Here's a solution (not double-checked) using several Dynamic Array functions.
Thanks that is very helpful! The UNIQUE() function is also new to me, another experience of, Gee where were you 20 years ago when I needed an excel equivalent to SQL: COUNT(distinct [column_name])!
Kids these days won't know how easy they have it, lol.
