Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Oct 05, 2024
Solved

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 le...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Oct 06, 2024

    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)...

Harun24HR's avatar
Harun24HR
Bronze Contributor
Oct 06, 2024

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))))))

 

 

Resources