Category Tracker - Add Tag Column

Google Sheets
1

I’m using the Tags column on the Transactions sheet to group some of my Categories into subtypes (e.g. Gifts has the name of the person for whom the gift was purchased). I’d like to include this subtype data in the Category report, but when I add an additional identifier to the Select clause to add a third column to the returned results, the Query returns no values.

=iferror(QUERY(Transactions!A:AF,“SELECT " &I1& " , SUM(” &I4 & “) , P WHERE " &I5& " >= DATE “”” & text(E5, “yyyy-mm-dd”) & “”" AND " &I5& " <= DATE “”" & text(E6, “yyyy-mm-dd”) & “”" AND " &I3& " = ‘" & E4 & "’ GROUP BY " &I1& " ORDER BY " &I1& " ASC LABEL SUM(" &I4 & “) ‘’, " &I1& " ‘’”, -1),“”)

Any thoughts?

2

Great question @wiggin. I think @jono is familiar with the Category Tracker and might have ideas.

3

Hi @wiggin,
There are a few things going on here, but I think I can get you up and running with this feature.

In addition to adding your tag column P to the SELECT part of your QUERY, you also need to add P to the GROUP BY part.

.... GROUP BY " &I1& ",P ORDER BY ...

Since you are selecting now by the Category and the Tag, both need to be in the GROUP BY.

This might work for you by itself. But if you are still having problems, read on.

I also noticed your cell references in the formula are different than the original formula. I believe this is because you added a row or column somewhere. This is the original formula:

=iferror(QUERY(Transactions!A:AE,"SELECT " &I1& " , SUM(" &I3 & ") WHERE " &I4& " >= DATE """ & text(E4, "yyyy-mm-dd") & """ AND " &I4& " <= DATE """ & text(E5, "yyyy-mm-dd") & """ AND " &I2& " = '" & E3 & "' GROUP BY " &I1& " ORDER BY " &I1& " ASC LABEL SUM(" &I3 & ") '', " &I1& " ''", -1),"")

I was able to show Categories and Tags using this adjusted formula:

=iferror(QUERY(Transactions!A:AE,"SELECT " &I1& " , SUM(" &I3 & "),P WHERE " &I4& " >= DATE """ & text(E4, "yyyy-mm-dd") & """ AND " &I4& " <= DATE """ & text(E5, "yyyy-mm-dd") & """ AND " &I2& " = '" & E3 & "' GROUP BY " &I1& ",P ORDER BY " &I1& " ASC LABEL SUM(" &I3 & ") '', " &I1& " ''", -1),"")

You might need to revert back to this formula or modify the formula based on row/column changes you made to the template.

Let us know if that works.

Jon
cc @heather

4

Success! Thanks for the quick response!

1 Like