Modifying the Monthly Analysis template to use only certain tagged transactions

My Goal
Use the Monthly Analysis sheet to view to use only my transactions and half of shared transactions with my partner.

I think that this can be accomplished by adding a REGEX match function to the SUMIF function in cell AM7, but this is at the edge of my spreadsheet knowledge and after some tinkering I haven’t been able to get it to work. If any spreadsheet ninja’s out there have idea’s I’d love some input!


I’ve set up tiller to import both my expenses and my partner’s accounts. We use tags to distinguish between personal expenses, expenses shared between the two of us, and expenses shared with our roommates. For example, all of my transactions are tagged ‘X’, our shared transactions are ‘X+Y’, and household transactions are ‘X+Y+Z’.

I’m looking to understand what just my spending looks like and I really like the set up of the Monthly Analysis sheet. I’d like to modify the ‘Actuals’ table in the set up of the Monthly Analysis sheet to include only the following types of transactions when calculating the actuals for each category:

  • Transactions tagged ‘X’
  • Transactions tagged ‘X+Y’ divided in two
  • Transactions tagged ‘X+Y+Z’ divided by three

What I’ve Tried

I started out trying to get only the first item to work: transactions tagged ‘X’. If I get that I should be able to handle the rest.

By default the formula powering the Actuals table is as follows:

I added the ‘Tags’ column in the Cell References for the transactions sheet and inserted a REGEXMATCH() to the conditions for the SUMIF in the above formula, resulting in the following:

This array formula loads but shows only $0.00 for each category and month despite having plenty of corresponding tagged transactions.

I have a non-spreadsheet ninja idea :woman_shrugging:t2:

You can have up to five Tiller-powered spreadsheets with a single subscription. So you could sort your sheet by your tag, migrate those transactions into a fresh spreadsheet, then use Monthly Analysis on just your data.

1 Like

Thanks Morgan! That would definitely work to do that every month and transfer the new data over. However, I was able to find a different solution. I made an additional tab that references the transactions sheet but adjusts the amount column by the desired amount based on the tag. It was fairly straight-forward to change the data that the monthly analysis uses to be that tab instead of the transactions tab.

1 Like

Sounds like a creative solution, @samuelqe. Glad you’ve got it working.

In the future, the ARRAYFORMULA() we use are great for robustness of production spreadsheets but are hard to edit and debug and have some limitations on what functions can be called internal to them. If you want to hack on an existing template, I’d consider first unwrapping the ARRAYFORMULA() functions (you will need to change the ranges a little) and then dragging the formulas down (“expanding”?)… and then iterating from there.

1 Like