Modifying the Monthly Analysis template to use only certain tagged transactions

Google Sheets
1

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!

Details

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:
=ARRAYFORMULA(IF(ISTEXT(AK7:AK),SUMIF({ARRAYFORMULA(EOMONTH(INDIRECT($U$10),-1)+1)}&INDIRECT($U$11),AM1:AX1&AK7:AK,INDIRECT($U$12))*IF(AI7:AI="Expense",-1,1),IFERROR(1/0)))

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:
=ARRAYFORMULA(IF(ISTEXT(AK7:AK),SUMIF({ARRAYFORMULA(EOMONTH(INDIRECT($U$10),-1)+1)}&INDIRECT($U$11)&INDIRECT($U$13),AM1:AX1&AK7:AK&{REGEXMATCH(INDIRECT($U$13),"^X$|^X,")},INDIRECT($U$12))*IF(AI7:AI="Expense",-1,1),IFERROR(1/0))

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

2

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.

3

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.