Problem: Tracking our joint spending became difficult when we had to change the bank account number for our Household account after it was hacked. This change complicated our financial reporting, as we now manage two separate accounts for the same purpose. We’re about to make this even more complex by adding a Fidelity Cash account for our joint expenses, meaning our Household/Joint spending will soon be spread across three different accounts.
Solution: On the Transactions sheet, add a new column named “Acct_Group” (or any other unique name that isn’t already used by Tiller). In the first row of this new column, insert an ARRAYFORMULA that assigns labels like “Joint Acct” based on the values in the “Account” column of the Tiller sheet. The “Account” column contains account numbers in the format xxxx1234 and is automatically updated with new transactions when the Transactions sheet is refreshed.
Benefit: The Acct_Group column enables me to generate summary reports for our joint spending, even though the spending is spread across multiple bank accounts.
The ARRAYFORMULA
The formula below assigns “Joint Acct” if the value in Col L (“Account” in my Transaction sheet) is “xxxx1234” or “xxxx5678”. Likewise, it assigns “SCC Acct” if the value in Col L is either “2222” or “1111”.
=ARRAYFORMULA(
IF(ROW(L:L)=1, "Acct_Group",
IF(LEN(L:L)=0, "",
IF((REGEXMATCH(L:L, "xxxx1234")) + (REGEXMATCH(L:L, "xxxx5678")) > 0, "Joint Acct",
IF((REGEXMATCH(L:L, "xxxx2222")) + (REGEXMATCH(L:L, "xxxx1111")) > 0, "SCC Acct",
IF(REGEXMATCH(L:L, "xxxx3333"), "Vanguard",
IF(REGEXMATCH(L:L, "xxxx4444"), "FC Main",
IF(REGEXMATCH(L:L, "xxxx5555"), "FC Security Deposit",
IF(REGEXMATCH(L:L, "xxxx6666"), "Fidelity Invest", "")
)
)
)
)
)
)
)
)
Notes:
1. Change the account numbers shown in the following example to match yours.
2. More accounts can be added to each of the REGEXMATCH lines.
3. Fidelity Cash will be added as a third condition to the the “Joint Acct” calculation.
Explanation (from AI)
• The formula works on column L and uses an ARRAYFORMULA to apply the logic to the entire column.
• For the first row, it displays the text "Acct_Group".
• For rows where the cell in column L is empty, it returns an empty string.
• It then checks the contents of column L to match specific patterns using REGEXMATCH and assigns different account group labels based on the matched patterns:
• If the value in column L matches "xxxx1234” or "xxxx5678”, it assigns "Joint Acct". (
• If it matches "xxxx2222” or "xxxx1111”, it assigns “SCC Acct".
• If it matches "xxxx3333”, it assigns "Vanguard".
• If it matches "xxxx4444”, it assigns "FC Main".
• If it matches "xxxx5555, it assigns "FC Security Deposit".
• If it matches "xxxx6666”, it assigns "Fidelity".
• If no matches are found, it assigns an empty string.
