Forum Discussion
Warning in Excel when trying to use dynamic array
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.
