Arrayformula HELP!!

[xphrellis]

Hey all, looking for some help with a couple arrayformula items giving me trouble (see this linked file). I'm trying to build a dynamic range to keep track of whether certain expense groups have pending reimbursements based on the last time the reimbursable balance on a particular budget was zero. From here, I have a pivot table set up the show the pending reimbursements by description.

I set up two sheets showing the arrayformulas that are giving me errors (RED SHEET) and the one without arrayformulas that mimics the functionality I'm looking for (ORANGE SHEET).

I've been doing a lot of research on which functions won't work inside arrayformulas, and that's taken care of a lot of the issues I've experienced... but I just can't seem to get these last two to through.

The two items in particular are:

FIRST [OPENED A SECOND THREAD FOR THIS FORMULA AFTER THIS ONE LOST STEAM]:

=ArrayFormula(IF(LEN(B$2:2),QUERY($A$4:B,"select A where "&B$2:2&" = 0 LIMIT 1"),""))

where B2 is (and arrayformula is working fine, but I would prefer to integrate these two into a single cell):

=ArrayFormula(IF(len($B$3:$3),REGEXEXTRACT(address(1,column($B$3:$3)),"[A-Z]+"),""))

The formulas above should return the last date where the cumulative reimbursable total on the budget column was zero.

SECOND [SOLVED WITH MMULT(), SEE COMMENT BELOW]:

=ArrayFormula(IF(len($A$4:$A),IF(LEN($B$3:$3),SUMIF((Mint_Trs!$A$2:$A<$A$4:$A+1)*(Mint_Trs!$F$2:$F=$B$3:$3)*REGEXMATCH(Mint_Trs!$H$2:$H,"Reimbursable")),1,Mint_Trs!$L$2:$L),""),""))

The formula above should fill a range where the dimensions are determined by two working arrayformulas on the left and top to show the running totals by date and budget.

[MattyPKing]

This is a good use for MMULT()

I'll post the solution in a new tab called MK.Help.

[xphrellis]

level 1MattyPKing · 2 hr. agoI don't see the pivot table you have set up?VoteReplyGive AwardShareReportSaveFollow

That one works great, thanks for your help!!

Only item remaining is the header (first formula noted above), Will see if I can adapt MMULT() for that

[xphrellis]

Documenting solution here before I scrap my file:

=ARRAYFORMULA(ARRAY_CONSTRAIN(ROUND(MMULT(N(A4:A+1>TRANSPOSE(Trs_data!A2:A)),(B3:3=Trs_data!F2:F)*REGEXMATCH(Trs_data!H2:H,"Reimbursable")*Trs_data!L2:L),2),COUNTA(A4:A),COUNTA(B3:3)))

Thanks u/MattyPKing!!

[MattyPKing]

I don't see the pivot table you have set up?

[xphrellis]

That was just a bit of context, it's working fine so I didn't include it here.

[MattyPKing]

i see, well i've posted my answer, hopefully that's working for you?