For those that like to create their own sheets and formulas, you may have run into a stumbling block to using your monthly category budget values.
Since the budget for each month is in a separate monthly column, this can make writing formulas to work with this data a bit complicated.
One solution is to create a sheet that “flattens” this data, so all the information is in just 3 columns: Category, Month, and Budget.
@benlcollins latest weekly newsletter points out the FLATTEN function is now included on the Google Sheets official function list. Official documentation is also available. (Signup for Ben’s tips here: https://www.benlcollins.com/ )
This function makes it much easier to flatten your monthly budget data.
It will turn this:
Into this:
How To Do It
On a new sheet, put the word Category, Month, and Budget in the top row of the first three columns.
Then, you need just one formula in cell A2. Here’s an example of the formula, but you will need to adjust it a little to match your Categories sheet.
=ARRAYFORMULA(SPLIT(FLATTEN(Categories!A2:A21&"❤️"&Categories!E1:P1&"❤️"&Categories!E2:P21),"❤️"))
Notice there are three category ranges, one for each column generated.
The first one, Categories!A2:A21 covers the range of all the Category names. (I tried using Categories!A2:A for an unlimited range, but that caused extra rolls to appear on the bottom of the sheet.)
The next range, Categories!E1:P1, is the range of all the monthly budget columns in the first row. Note that this is the first day of the month for the budget month.
The final range, Categories!E2:P21, is the range of the top budget value in the earliest month to the bottom budget value in the last month.
In between each of the Category ranges, there is an emoji (using the heart here, but any will work) surrounded by double quotes, and surrounded by ampersands. This allows the SPLIT function to work thanks to the heart emoji at the end of the formula.
Personalize The Formula
To make this work on your sheet, adjust those category ranges to match your Categories sheet.
I’m sure there is a way to make that formula dynamic (maybe using the OFFSET function) so you don’t need to manually enter the last budget category row number. But then it gets more complicated and I want to keep it simple here. The downside is that if you add a new category row, you might need to adjust the flatten formula.
With this sheet in place, you can write formulas sum and lookup your budget values without having to worry about all the different monthly columns.
For example,
=SUMIF(A2:A,"Phone",C2:C)
will get the total of all your Phone category budgets
=SUMIFS(C2:C,A2:A,"Phone",B2:B,"5/1/2021")
will get your Phone budget for May 2021.
Of course, if you need to refer to these values on a different sheet, you will need to add the sheet names, such as ‘Flatten’!A2:A and ‘Flatten’!C2:C.
Add this formula in your new sheet in cell D1 and you will add the Category Type for each Category, so you can do even more types of summations and filters using formulas:
={"Type";ARRAYFORMULA(IF(ISTEXT(A2:A),VLOOKUP(A2:A,Categories!A2:C,3,FALSE), IFERROR(1/0)))}
The formula above works if in your Categories sheet, column A is Categories and column C is Type.
If you want to get a list of the Category Groups, change the word Type to Groups, change the range to Categories!A2:B and the number after that from 3 to 2. (This assumes your Groups are in Column B.)
Let us know if this helps or how you might take advantage of it.
Jon