Hi Mike! Thanks for the feedback.
How many extra categories would you like to add? I just now updated the sheet with some extra rows (there are 12 now), and a new tab called ‘non-fixed categories’. The idea is that you only have to specify the non-fixed categories once, in this tab. The fields you specify will be pulled to all the monthly tabs, and to the overview tab.
If you need more than the 12 non-fixed categories, then the sheet needs some configuring:
In Overview tab:
- Add however many rows you need to the non-fixed section
- Highlight the entire row above, and pull it down, so that all fields in the row are filled out.
In each of the monthly tabs:
- Highlight the entire row E from E2 all the way down.
- Click on ‘Data’ > ‘Data Validation’ > Right from ‘Criteria: List from a range’, and expand the range. Right now we are pulling from B3 to B14, but if you entered, let’s say 20 non-fixed categories, you’ll need all these fields, so we just change B14 to B22.
This takes some setting up, but after that, you’re good to go!
And how the cells are populated in the Overview sheet: with SUMIF formulas!
As an example: field D24 (total spent on food in January) has this formula in it: =SUMIF(‘jan 19’!$E$2:$E$301,$A24,’jan 19'!$D$2:$D$301). That means that the sheet will look in tab ‘jan 19’ at the range E2 to E301. It wants to get a sum of values, but it’s only interested in the value in A24, ‘Food’. So in tab ‘jan 19’, it will go through the range E2 to E301, and if it finds a field with the value ‘Food’, it will add up the values found in D2 to D301 to a sum. We end up with the sum of these values, neatly displayed to us in the Overview in field D24!
Does this answer your questions? Feel free to write me in case you need anything else.