Thanks for sharing, I haven’t considered adding a Split column!
I found another column that could use a custom array for the VLOOKUP so that it can handle column inserts: AU3 (1st Occurrence) should be changed to:
={"1st Occurance";MAP(
AF4:AF,
LAMBDA(
description,
IFERROR(IF(
ISTEXT(description),
IF(
VLOOKUP(description,{B4:B,F4:F},2,FALSE)<>"",
IF(VLOOKUP(description,{B4:B,F4:F},2,FALSE)>=M2,VLOOKUP(description,{B4:B,F4:F},2,FALSE),EDATE(VLOOKUP(description,{B4:B,F4:F},2,FALSE),12)),
$M$2
),
IFERROR(1/0)
),"")
)
)}
I haven’t been able to figure out a more resilient formula for AV3 (Occurrence). I thought I was close with the following, but I couldn’t figure out how to turn it into an ARRAYFORMULA 
:
=IF(AU4<> OFFSET(AU4,-1,0),1,offset(AU4,-1,1)+1)
Nice catch @brettanicus , I’m also not sure about what an arrayformula would look like there.
Another idea to help speed up processing is using a single array formula on the Categories sheet rather than pasting a formula in each of the cells, e.g.:
=LET(
categories_,OFFSET(A1,1,MATCH("Category",A1:1,0)-1,200,1),
months_,OFFSET(A1,0,COLUMN()-1,1,COLUMNS(A1:1)-COLUMN()+1),
MAP(categories_,LAMBDA(cat_,
IF(ISBLANK(cat_),IFERROR(1/0),
MAP(months_,LAMBDA(month_,
IFERROR(SUMIF('Budget Plan'!E4:E203,cat_,OFFSET('Budget Plan'!L4:L203,0,MATCH(DATEVALUE(month_),'Budget Plan'!M3:X3,0))),0)))))))
The instructions would be to delete all the $ values in your Categories sheet and paste the above in row 2 below the first month.
I just tried it and it works great. I may update the documentation for all versions of Budget Plan to use this as it’s easier to setup, and if the performance is better, bonus!
While setting up my Budget Plan, I used blank rows to separate my budget items into groups and to leave space for additional items. This resulted in $0 totals in rows M-X for the last # budget items, where # was the number of blank rows. The problem is the formula in cell AF3, which generates the raw list of budget item descriptions. The height of the array of descriptions (in the OFFSET formula) is determined by COUNTA(B4:B). Since COUNTA doesn’t count blanks, the height of the array will be short by the number of blank cells in B4:B. Switching from OFFSET + COUNTA to FILTER ought to fix it:
={"Description"; ARRAYFORMULA(
TRIM(
TRANSPOSE(
SPLIT(
QUERY(
IFERROR(REPT(
FILTER(B4:B,B4:B<>"")&"♥",
FILTER(J4:J,B4:B<>"")
)),
,
999^99
),
"♥"
)
)
)
)}
Two caveats, which may be dealbreakers:
- If the categories sheet is sorted, the array formula won’t work anymore because it will move with its row.
- I think this formula may end up being slower! Still testing, but it seems the nested
MAPs are significantly slower than filling the array with individual formulas.
Apologies for the wrong turn!
I appreciate the update, I won’t rush to make any changes to the template! Glad to hear anything you find out. This template is a performance hog, so any opportunities to speed it up would benefit everyone!
Is there a max number of rows that can be added? I seem to be getting all zeros as budgets on my categories sheet starting with row 95 of 163. All budgets above this row seem to be pulling in correctly. I’m asking because maybe some of the formulas have a limit or something. It occurs regardless of if I imput the formula on each cell or the formula above that you just put under the first month. I do have a historical category sheet that goes 2 years back but those budgets are static and grouped & collapsed.
Looking at the original Budget Plan template, I have 198 rows that are filled with the required formulas. If you added any rows beyond that, you’d need to copy the formulas from L198:AA198 down across all the new rows. On the Categories sheet, you’d need to copy the formulas in the Jan-Dec columns down across all your rows.
Just to make sure I am doing this correctly. I should be highlighting the cells copied (L198:AA198) in the screenshot then dragging/copying them down the remaining cells on the Budget Plan Sheet, right? I did exactly that as well as dragging the cell for Jan 2024 (Al 2) on my categories sheet to the Dec 2024 (AT2) then dragging it all the way down my categories sheet. I am still producing zeros from cell AL95 to AT95 down. Also used the formula below on just cell Al2. Neither are working. All the budgets on column L on the Budget Plan are populating though. I am using version 2.0. I don’t believe there has been any version updates. Please let me know if that is not the case. Thank you.
Btw I don’t actually see formulas in the white spaces such as O8. I am a noob when it comes to spreadsheets but are the formulas hidden?
=LET(
categories_,OFFSET(A1,1,MATCH(“Category”,A1:1,0)-1,200,1),
months_,OFFSET(A1,0,COLUMN()-1,1,COLUMNS(A1:1)-COLUMN()+1),
MAP(categories_,LAMBDA(cat_,
IF(ISBLANK(cat_),IFERROR(1/0),
MAP(months_,LAMBDA(month_,
IFERROR(SUMIF(‘Budget Plan’!E4:E203,cat_,OFFSET(‘Budget Plan’!L4:L203,0,MATCH(DATEVALUE(month_),‘Budget Plan’!M3:X3,0))),0)))))))
Sorry, if you have version 2, that doesn’t uses formulas in each cell, it uses formulas just at the top of the column that fill down into the empty cells. The problem would then be in the formula you showed. It’s been a while since I did that, but I think you need to change the 200 in the second line you pasted, the 203 in the 7th line, and the 203 in the 8th line. Bump them up to a larger number (400 & 403?).
Thank you that worked. I ended up changing it 600 and 603 since apparently, I have a lot of budgets ha. Can you also tell me what would be the fix if I was to use the original formula for the 2.0 version in case I need to revert to using it instead of the formula that fills in the empty cells? I see it mentioned above that the new formula is resource intensive so if it slows my sheet considerably then I want to have a fix ready. Would I just change the 249? I am using version 2.0 for both formulas. I just have the older formula on my back up sheet in case I need to copy and paste. Thank you.
EDIT: That seemed to work on the second formula. I switched it from $E$4:$E$249 & $L$4:$L$249 to 549 and the budgets populated correctly.
I do want to know though if I could change the formula to $E$4:$E or would that make the formula even more resource intensive even though there would just be blank rows towards the bottom of my sheet. And if that is fine to do then how could I emulate the same thing on the first formula? The one that fills down the empty cells.
The formula:
=IF(ISBLANK($A2),“”,IFERROR(SUMIF(‘Budget Plan’!$E$4:$E$249,$A2,OFFSET(‘Budget Plan’!$L$4:$L$249,0,MATCH(DATEVALUE(AI$1),‘Budget Plan’!$M$3:$X$3,0))),0))
It would likely affect performance, but you can try it to see if it’s acceptable or not. I don’t have the sheet setup to test right now, but you could try this:
=LET(
categories_,OFFSET(A1,1,MATCH(“Category”,A1:1,0)-1, ArrayFormula(max(if(len(A:A),row(A:A),))),1),
months_,OFFSET(A1,0,COLUMN()-1,1,COLUMNS(A1:1)-COLUMN()+1),
MAP(categories_,LAMBDA(cat_,
IF(ISBLANK(cat_),IFERROR(1/0),
MAP(months_,LAMBDA(month_,
IFERROR(SUMIF(‘Budget Plan’!E4:E,cat_,OFFSET(‘Budget Plan’!L4:L,0,MATCH(DATEVALUE(month_),‘Budget Plan’!M3:X3,0))),0)))))))
Leaving the 203 out of the last two shouldn’t be a problem, it’s just the 200 towards the top for the Offset command that I’m unsure about. I put in code that is supposed to find the number of the last row in the column instead of the 200, see if that works!
I get a formula parse error when I use the above formula in cell AI2
woops, some characters pasted in that shouldn’t have. Hopefully it works now?
Negative. Still getting the same formula parse error
OK, I just re-installed the template and tried @rhowell’s code and got this to work:
=LET(
categories_,OFFSET(A1,1,MATCH("Category",A1:1,0)-1,600,1),
months_,OFFSET(A1,0,COLUMN()-1,1,COLUMNS(A1:1)-COLUMN()+1),
MAP(
categories_,
LAMBDA(
cat_,
IF(
ISBLANK(cat_),
IFERROR(1/0),
MAP(
months_,
LAMBDA(
month_,
IFERROR(SUMIF(
'Budget Plan'!E4:E,
cat_,
OFFSET(
'Budget Plan'!L4:L,
0,
MATCH(
DATEVALUE(month_),
'Budget Plan'!M3:X3,
0
)
)
),0)
)
)
)
)
)
)
Delete everything in H2:S (the cells below the monthly headings) on the Categories sheet, and paste the above formula into cell H2 (below your January heading). Change the 600 in the second column if you have more than 600 categories on that sheet.
Yes, this formula works. Thank you for taking the time to help.
You mentioned that you had this sheet uninstalled. I am curious about other solutions you currently drive daily since you don’t use this one.
Also, for future reference, I am curious as to how you input your formula like the above which allows one to scroll for long formulas.