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.

​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.  Read More

​