Formula for Budget Sum by date range

Google Sheets
,
1

Hello!

First time poster and newbie spreadsheet editor here. I am hoping someone can assist me.

I am trying to modify the Community Solutions “Waterfall” sheet to add a budget total based on the date range selected. I’d like it to pull the data from the Categories sheet with the budget figures, add each month’s number that falls into the date range selected on the Waterfall sheet and display as an arrayformula next to the transaction data. I know the dates might be clunky with partial months, but would like it to add the whole month if any of the month is selected, EG: if the date range ends up 1/1/2024 - 3/10/2024 then add the 3 months budget figures and ignore the fact that it’s a partial month.

It would be great if I could have a formula for both the categories and the groups figures.

Is this easy enough for one of the amazing coders on here?

Note: The transaction data is working, I just hid it for the screenshot.

Absolutely loving Tiller Money and it’s changing mine and my Wife’s lives. Thank you for all the hard work! :slight_smile:

Cheers,

Ryan.

image
image860×1078 68.8 KB

2

Hi @CRO - Welcome to the community!

I’ll take a shot at two formulas to help you accomplish what you’re looking to do.

For the Category budget sums, the following formula would be placed in your Cell D10:

=IFERROR(LET(start_mth, MATCH(DATE(YEAR(B5),MONTH(B5),1),Categories!$1:$1,0), end_mth, MATCH(DATE(YEAR(B6),MONTH(B6),1),Categories!$1:$1,0), first_budget_col, MATCH(TRUE,MAP(Categories!1:1,LAMBDA(headers,ISDATE(headers))),0),
cat_transpose, TRANSPOSE(INDIRECT("Categories!A1:"&REGEXEXTRACT(ADDRESS(1,COLUMNS(Categories!1:1),4),"[A-Z]+"))),
MAP(A10:A,LAMBDA(cat, IF(cat<>"",SUM(XLOOKUP(cat,CHOOSEROWS(cat_transpose,1),CHOOSEROWS(cat_transpose,SEQUENCE(1,end_mth-start_mth+1,start_mth,1)))),"")))),"Check for missing budget month")

It’s perhaps a little more complicated than necessary, but I tried to accommodate the fact that your Categories sheet may look different than the default and different than mine. Note that if you make a Period selection that results in a date range of a month that you do not have a budgeted column in the Categories sheet (ex. multi-year selections like “All” or “Last 12 months”), the solution will error since the underlying data is missing. It will prompt you to check for a missing budget month.

For the Group totals, in your Cell I10, you could use:

=LET(groups, ARRAYFORMULA(IF(A10:A<>"",VLOOKUP(A10:A,Categories!$A$2:$B,2),"")), MAP(F10:F,LAMBDA(grp, IF(grp<>"",SUMIF(groups,grp,D10:D),""))))

This one does assume that your Categories sheet first two columns are Category and Group. If not, you’d have to adjust the VLOOKUP().

Feel free to try it out and let me know if there are any issues. Hope it helps!

3

Hi @KyleT - Thank you so much!

The formulas are mostly working. It’s just the date is not working correctly possibly? I have 2023 - 2025 budgeted right through, but it’s only adding Jan-Dec 23.
Eg: Electricity and Gas is only adding 1 year from the drop down .
image

The Waterfall date drop down is kind of clunky. Maybe can you help me get a better system for doing the date range?

Thank you again for your help, I really appreciate it.

1 Like
4

image
image2390×116 19 KB

5

Hi @CRO - Glad to hear that it seems to be working. I think the first formula should work even if you’ve budgeted multiple years out, so I’ll give a suggestion on the date inputs and then if you are still seeing any issues, just let me know.

Maybe one of the quickest and easiest ideas for the dates is to enter your own dates in Cells E5 and E6. You can do this while preserving the existing dropdown using the following steps:

  1. Start by going to cell Z22 in the Waterfall template and enter a new dropdown value like “Override” or “My Dates”. This should then appear in the Period dropdown in Cell B4.

  2. In B5 and B6 you’ll modify the formulas to point to E5 for the Start Date and E6 for the End Date by adding the formulas below in each respective row. Don’t forget to also add a comma to the end of the previous line.

B4="Override",E5

B4="Override",E6

Here’s a screenshot for one of them:

CleanShot 2024-05-22 at 08.24.21

After that it’s just a matter of putting your override dates in E5 and E6 and when you select the new entry from the Period dropdown they will get picked up.

Let me know if it helps and if you are seeing any issues with the formulas.