Every month I export my bank statement and want to see:
- All the income I get in total, and break it down to salary + tenant rent + other (this is easy and I did it)
- All the expenses, which I categories myself with things like Health, Sports, Regular expense etc
So what I have is 3 columns. Date, name of expense, and amount of expense, for example 2024-03-23, ITALIAN PASTA/24-03-22, -125
Then to tell Sheets it’s a month, I create 2 Named Ranges: one for the names of expenses, the other for amount. And I put that into a cell for example D1 with “Mar24Names”, and D2 with “Mar24Amount”
Then I create formulas to SUMIF all of the occurrences of specific words into specific categories, like *PASTA*, and I do it like so =SUMIF(INDIRECT(D1), "*PASTA*", INDIRECT(D2)) + SUMIF(INDIRECT(D1), "*BURGER*", INDIRECT(D2)), which sums all the costs for lunches for example
For coloring to see better myself the categories I do Conditional Formatting and do the same regular expressions there too, for example for *PASTA* etc to mark them the same color for lunch expenses
The problem with this approach is I have tons of SUMIFs now, some of them are like 20-30 different ones, and it get very confusing to keep up with it and finding if I added something or not, and I need to do that together with coloring it too, which I sometimes forget to do together and I lose some things.
Ideally, I wanna have just a range of cells, for example “Lunch places”, and I would list all the different lunch places in separate cells in a column, and just add a new lunch places and have it all automatically pick up for the coloring and the calculation
Please point me in the direction of making it more simple and efficient if that’s possible