Rollups with categories

Hi, I’m working on an app to manage a budget. I have a list of categories (food, gas, rent…etc) and I can easily add new items, but what I want is for my tab to show the categories (with sums of the items in them (sum of Food) and then be able to go to detail and see all the entries for Food. Ideas? Here’s the app: cashburn.glideapp.io you’ll get what i’m trying to do. (kind of hacked right now) Thanks. M

So I’m looking at your ‘$ Spent by Category’ tab. How are you getting the totals right now?

I’m going to make assumptions on how your data is laid out. What I think you need is a relation column in the Categories sheet to link the category name to the category name in the individual Items sheet. This will 1. allow you to create an inline list of all the related items from the items sheet, and 2. create a rollup column that uses the relation column to sum the values of all the individual items that match that category. Use the rollup column to display a total value of all related items in the category.

Thanks!

Kind of works, but not quite.

My layout is one sheet (Sheet1) with Amount, Category Name, Note…the detailed transactions (all I want to do is sum up the amounts by category).

I have two other sheets to actually make this work, clumsily, Sheet2: Summary: with columns: 1. Category and 2. Summed Amount using an array sum formula to enable me show the sum of transactions by category.

And then Sheet3, a Totals sheet that just allows me to display the “Home screen” with the totals spent, and remaining.

Seems like glide should be able to sum up the Amount by Category name using the first sheet. I can see the totals for each category, then click on a category and see the details (each transaction) for that category

Thanks
M

This is very doable. Your sheet structure seems fine. I would get rid of any columns that have SUM formulas in your sheet, since you can do all of that in Glide and it will be faster. Have you set up the Relation and Rollup columns like I mentioned in my previous post. You’ll still need 3 sheets, one for each tab.

Yes, here’s a duplicate sheet that will let you see what i’m doing.

Feel free to have a crack at it.

Ok, that’s exactly how I pictured your sheets being set up. Can you show me screenshots of the Relation and Rollup columns that you have set up in the data tab of the glide builder?


also, I don’t need the amount column anymore.

Looks good, and correct, you don’t need the amount column in your Summary sheet anymore. All you need now is a Rollup column in your Total sheet to sum either all of the values from the Rollup column in either Summary, or all of the amount columns in your Months sheet. You should only need the monthly budget column in your Total sheet. The rest can be built with various built in glide column types.

The only thing I would add in your app is an inline list in the category details page. Here you can use the relation you set up to display all if the related items in that category.

Thanks! I appreciate the assistance.

1 Like

It’s very easy to make something like this in Excel (sumifs). But, how do you copy formula from above record to the next if you have additional category in sheet “Summary”?

This works. Thanks a lot.