Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
Add a new column called “Tags” to your Transactions sheet.
Tagging Transactions
The Tags Report comes to life after you start tagging transactions on the Transactions sheet. The Tags on the Transactions sheet are distinct from those you can also use on the Categories sheet. Read more about using Tags on categories.
You can use a single tag or multiple tags for a transaction.
To use multiple tags for a single transaction simply separate each tag with a comma (preferably) with no space in between the tags.
How to use the Tags Report
After you’ve tagged some transactions you can start building analysis on the Tags Report sheet. You should see the list of tags you’ve used so far populate into the All Tags list starting in cell A10.
You can then select a tag from the dropdown list in the middle of the Tags Report to review all transactions associated with your selected tag and get a summary of the total amounts and counts of the tag used organized by category.
Customize the date range if you only want to view data for a specific time range or you can choose “All Dates” to see all tagged transaction data.
I it! Thank you @alan.heatherley for porting this to excel for us.
I do have 1 question though regarding @randy information about tagging transactions.
Before downloading the Tags Report, I already had a tags column. Each tag is separated with a comma AND a space. Even using a space, the totals appear to be correct. Should I be concerned and change all of my current tags so that there is only a comma separating the tags, without the space?
At their most basic, the formulas that identify the tags SPLIT() on commas. Better implementations also TRIM() leading and trailing whitespaces. The most robust convention is to not use leading or trailing whitespaces… but, if it is working, I wouldn’t worry about it, @ramona.
I have installed the Excel Tag Report and I like it and it works well. I happen to use subcategories for some of my categories (not all) I modified the middle part of the Tag Report to display the subcategory but incorporating it into the right part of the report which sums totals by category is beyond me. Meaning I would like to sum by both category and then subcategory. Does anyone have a similar type report that I could look at to see how to code it?
Hi @buzzmaster1, it should be possible, but it’ll depend on how you’re defining your sub-categories. For example, are you taking the category name and adding a special character then the subcategory name to the end? Like “Food > Groceries” or “Food > Eating Out”.
Thanks, my sub categories do not include the category as part of the name. I did create a pivot table that Works, but it’s not a very graceful solution.
Hi @buzzmaster1, so what’s in the Category column in the Transactions table - is it the category name or the subcategory? And is there another table that creates the linkage between the category and subcategory?
I’m thinking you would need to modify the tag_categories formula (you’ll find it in the Name Manager on the Formula tab in Excel) to do the lookup to match the subs with the parent category, but the specific changes you would need to make would depend on the answers to the above questions.
I’m happy to do a screen share to work on it with you. PM me if you want to set that up.
Hi Alan,
Thanks for your detailed response and offer of help. So in the transactions sheet, I have a column named category and a column named Subcategory. In a different sheet I created a table where I defined the sub categories for a given category. That way the drop down I use in the transactions sheet, knows what the choices are for subcategory based on the category. I also have a Notes and a tags column in transactions sheet.
I will be traveling until the end of July. But I would definitely love some help when I get back.
Hi @buzzmaster1, actually, as long as you haven’t changed the structure of the Tags Report sheet, this should get you where you need to be:
Replace the formula in J10 with this: =SORT(UNIQUE(INDEX($E$10#,,3)))
Add this formula to K10: =MAP(J10#,LAMBDA(cat,SUMIF(INDEX($E$10#,,3),cat,INDEX($E$10#,,4))))
Add this formula to L10: =MAP(J10#,LAMBDA(cat,COUNTIF(INDEX($E$10#,,3),cat)))
Instead of mucking about with different columns in the Transactions table, this just takes the contents starting in G10+ and summarizes them. So as long as the contents of that column are what you want (category vs. subcategory), then the summary table in J10#:L10# will reflect them accurately.
Hi @buzzmaster1, Just checking to see if Alan’s post was helpful. Looks like you may not be able to respond till end of July, but just checking in case.
Hi, I am finally back from my trip and looking at this again. I need to clarify. I did modify the tags report. Column G has Category but now Column H has SubCategory and Column I has Total. I am uploading a screenshot.
Hi @buzzmaster1, the screenshot is really helpful. Try this:
Add a column between K and L called Subcategory
Replace the formula in K10 with this: =SORT(UNIQUE(HSTACK(INDEX($E$10#,,3),INDEX($E$10#,,4))),{1,2})
Replace the formula in M10 with this: =MAP(INDEX(K10#,,1),INDEX(K10#,,2),LAMBDA(cat,subcat,SUMIFS(INDEX($E$10#,,5),INDEX($E$10#,,3),cat,INDEX($E$10#,,4),subcat)))
Replace the formula in N10 with this: =MAP(INDEX(K10#,,1),INDEX(K10#,,2),LAMBDA(cat,subcat,COUNTIFS(INDEX($E$10#,,3),cat,INDEX($E$10#,,4),subcat)))
Thank you very much for this, Alan. Yes this works and I agree much better than going back to the transactions sheet. Quick Question: Is there a way to have it gracefully return “No Results” rather than #VALUE? I added also add a total sum and total count in M8 and N8.
Hi @buzzmaster1, you can wrap each formula with an IFERROR() to eliminate the Excel errors. The format is: =IFERROR(<formula to do stuff>, "Message that replaces Excel error")
Shoutout
it! Thank you 