Forum Discussion
Another tricky Dynamic Array problem that I can't solve.
With help from this forum I have managed to fix some issues that, to me were very tricky, and have learned a lot of new techniques along the way.
But this latest one is again beyond me. Earlier I learnt some ways to do a sumifs equivalent across rows and columns in a single go. My aim is to build a fully dynamic model with both columns and rows sections dynamic. The issue I have now is similar to the previous one but with one added element.
I need to do a sumifs across columns too. I’ve managed to add multiple criteria in a single dimension ie the rows but to sum in 2 directions is just beyond me.
The background: I have a table of Trial Balances for multiple months. These need to go in on a monthly basis. But, the output sheet needs to have dynamic periods – ie the number of months in each period can change; typically they will be 1,3,6 or 12 months in a period going from monthly to annual. These are driven elsewhere and the output sheet can have several ‘zones’ of different time periods. i.e. Early actuals may be annual, later ones may be 3 monthly or monthly, current year will be monthly and probably the early forecast years will be monthly and the later forecast years will be 3 monthly. The actuals have to be in fixed time periods otherwise a change in configuration will result in them being in the wrong periods.
So I need to be able to do a sumif across the accounts by Entity (by rows) and also sum by columns based on the number of months in the periods. I have attached a sample sheet showing what I am trying to achieve. Ideally, I can avoid having and interim calculations sheet. ALSO, the inputs and outputs will be on different worksheets in case that makes a difference
Any help would be much appreciated.
James_Buist An MMULT approach could be something like this:
=MMULT(--(TOROW(C6:C19)=C27:C29),MMULT(--E6:M19,(TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25)))Although, the input and output ranges should both include a start and end date for each period (see attached)...
James_Buist Give a try to the following formula-
=SUM(CHOOSECOLS(FILTER($E$6:$M$19,$C$6:$C$19=$C27),SEQUENCE(1,XMATCH(E$25,$E$4:$M$4)-XMATCH(E$24,$E$3:$M$3)+1,XMATCH(E$24,$E$3:$M$3))))And dynamic spill array using MAKEARRAY().
=MAKEARRAY(ROWS(C27:C29),COLUMNS(E24:J25),LAMBDA(r,c, SUM(CHOOSECOLS(FILTER(E6:M19,C6:C19=INDEX(C27:C29,r)), SEQUENCE(1,XMATCH(INDEX(E24:J25,2,c),E4:M4)-XMATCH(INDEX(E24:J25,1,c),E3:M3)+1,XMATCH(INDEX(E24:J25,1,c),E3:M3))))))James_Buist An MMULT approach could be something like this:
=MMULT(--(TOROW(C6:C19)=C27:C29),MMULT(--E6:M19,(TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25)))Although, the input and output ranges should both include a start and end date for each period (see attached)...
Brilliant answer as usual. Love the MMULT but just can't quite get my head around creating my own masterpieces with it. Though I’m getting better at tweaking and modifying them. As seen in the reply to the original post, both solutions worked but the MMULT is noticeably faster and much more concise
I have uploaded the xlsx file with the mods I made to bring in a second filter
You should be offering masterclasses on this
- Brilliant. Both answers work but the second with MMULT is quite a bit faster – and shorter but harder to follow as one has to understand matrix multiplication.
I realised, however, when I read these that I’d actually missed out one element. The vertical (row) filter needs another element added.
Each of the GL Accounts should have a code and some of the accounts will have the same codes so will be aggregated. So this does it by entity but I needed it by entity and GL code. I have added these to the spreadsheet.
With Harun 24Hr’s method I could easily add the extra filter to the filter function which is easy to use and follow. But Same with djclements, after a bit of thinking, I managed to add the second filter to the MMULT resulting in this
=MMULT(--((TOROW(C6:C19)=C27:C29)*(TOROW(A6:A19)=C39)),MMULT(--E6:M19,(TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25)))
Thanks to both of you. I am building a collection of different ways to achieve things but keep getting bogged down with things I feel I should be able to do. However, each time I get closer and have solved white a few though I really need to master this MMULT usage as it is quite awesome.
I have put a copy of the modified version in a reply to djclements as it uses his MMULT solution
