I want my dashboard to reflect each category sum automatically. How can I do this?
Hi @treasurer,
Here’s a quick formula you can use:
=QUERY(Transactions!D:E,"SELECT D,SUM(E) WHERE D IS NOT NULL GROUP BY D ORDER BY D LABEL SUM(E) 'Sum'")
This assumes that your Categories are in Column D in the Transactions sheet and your Amounts are in Column E in the Transactions sheet.
If your columns are different, change the references to D and E to match your columns.
An English translation of that formula is:
Use the QUERY function on the data in the Transaction sheet in columns D to E.
Then select the Categories in Column D and the Sum of the Amounts in Column E.
Do this only where D is not null, so uncategorized columns are not shown.
Then group the data by category.
Order the result by the Categories in Column D.
Finally label column SUM(E) with the word Sum.
Did that work for you?
Where does one enter this formula?
Where does one enter the formula?
Thank you.
You could put this on a new sheet in the spreadsheet.
Perhaps on a Dashboard sheet.
1 Like
Thank you.
How would I modify that formula to work only on a specified date range?
For example, I’d like it to sum transactions by category only for the first quarter – 01 Jan. through 31 March.
Thank you.
Filtering by dates is certainly possible but it’s a little tricky.
Here’s a link to an article that explains it better than I can: