Summarise 2D Dynamic Array?
Using Excel 365 on Windows 11
This should be simple, but I haven’t manged to find a way to do it (and Copilot has just generated a bunch of #REF! and #CALC! errors…)
I’m trying to do some basic forecasting over multiple years, but using dynamic ranges so the start and end dates, and the number of categories forecast can update automatically. I can get to a dynamic forecast by month, but am drawing a blank when I try to summarise that by year.
A simplified version of the sheet currently looks like this:
Data entry in B4:E10
Months dynamic range (pink) calculated in C14
=EOMONTH(D2,SEQUENCE(1,F2,0,1))
and spills C14 to AL14, or wherever the last month is.
Categories dynamic range (pale blue) calculated in B15
=FILTER(B4:.B10,B4:.B10<>""))
and spills B15 to B20 here.
Years dynamic range (peach) is just a helper row =YEAR(C14#). It might not even be necessary other than visually.
The Forecast dynamic range (green) is then
=XLOOKUP($B15#,$B$4:.$B$10,$C$4:.$C$10,0)*(C14#>=XLOOKUP($B15#,$B$4:.$B$10,$D$4:.$D$10,0))*(C14#<=XLOOKUP($B15#,$B$4:.$B$10,$E$4:.$E$10,0))
All I want to do is put one formula in C25 to calculate the blue dynamic range total by year for each category. The years are a dynamic range (UNIQUE of C14 above) and the categories are just B15#.
This it turns out is completely beyond me - I can calculate each row individually using SUMIFS quite easily, but cannot persuade it to calculate one SUMIF for each line using one formula
Anyone got a good way to deal with this? Thanks.
[link] [comments]
Want to read more?
Check out the full article on the original site