Search code examples
google-sheets

How to organize monthly expenses in Google Sheets

Every month I export my bank statement and want to see:

  1. All the income I get in total, and break it down to salary + tenant rent + other (this is easy and I did it)
  2. 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.

I would like to make it simple and efficient if that's possible.

Solution

  • Add these search terms Pasta,Burger and so on in a column, let's say E & then try the below formula:

    =arrayformula(sumif(regexmatch(indirect(D1),"(?i)"&textjoin("|",1,E:E)),true,indirect(D2)))