Merchant report - Most spend to least spend at each

Google Sheets
1

Brand new to Tiller. I’ve got a bunch of transactions in and loving everything. I played around with autocat in order to clean up my descriptions. I would love a report that would list top down how much I spend at each vendor (I assume that would be the info that is in the description column, since I’ve cleaned them up). Can someone help me figure out how to do that?

2

You can check out the Insights template or the Yearly Insights template in Tiller Labs to get some of this data. These reports pull information from the description column in the Transactions sheet… so it may be more than what you are looking for.

If you have certain merchants in mind, perhaps you can create a report that pulls this information from your transactions sheet. You can set up rules using Autocat to pull specific merchant names into specified columns within your transactions sheet. From there, you can pull this data and any other relevant data via a LOOKUP function or IF function to make your reporting complete.

3

The transactions sheet is the most powerful and important sheet in Tiller. You can learn a lot just by sorting it. This is what I usually do. Real simple. No creating new reports. No lookup or if functions (what are those?). First, select the whole sheet. Do your first sort on the descriptions column (if this is your vendor). Do your second sort on date, using Z to A for the order. Then go to your amount column. You can select all transactions for that vendor for 2020 and look at the bottom for the sum. This is quick and easy. Of course, if you want something more formal, permanent, and fancy, then you will need to put in more work.

B

2 Likes
4

Hi @jordanchap,
@warren and @Blake 's solutions are good.

But if you are looking for a dedicated sheet that provides this info, you could add this formula to cell A1 of a new sheet:

=QUERY(Transactions!C:E,"SELECT C,SUM(E) WHERE C IS NOT NULL GROUP BY C ORDER BY SUM(E)")

This assumes your Descriptions are in column C and Amounts are in column E of your Transactions sheet.

You will get 2 columns, Descriptions and Sum Amount.

By adding ORDER BY SUM(E), the list will be ordered by lowest to highest sum. Since expense transactions are negative, the largest expenses will be first. To change the order use ORDER BY SUM(E) DESC and then it will flip.

If you want to add a column that counts the number of transactions per Description, use this:
=QUERY(Transactions!C:E,"SELECT C,SUM(E),COUNT(C) WHERE C IS NOT NULL GROUP BY C ORDER BY SUM(E) DESC")

If instead of Descriptions, you want to group by Categories, use the Category column letter instead of the Description column letter in the formula.

Does that work for you?

Jon

4 Likes
5

Wow, all of your suggestions have been great, thanks so much! I ultimately went with Jon’s suggestion as I have no idea what lookup functions and IF functions are. Blake’s suggestion worked but I did want something more permanent. Jon’s suggestion gave me exactly what I wanted. Thanks!!!

1 Like
6

Much impressed - Jono is extremely proficient in EXCEL - - these queries helped me as well. Thanks

2 Likes
7

Adding to that excellent suggestion if you want to limit to a set period of time you could modify the formula to something like this:

=QUERY(Transactions!C:I,“SELECT C,SUM(E) WHERE C IS NOT NULL AND I>= date '”&YEAR(today())-1 &"-" & MONTH(Today())&"-01’ GROUP BY C ORDER BY SUM(E)")

Which would only include transactions within the last 12 months.

1 Like