Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Free coffee for help on a formula ☕️

1630 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Mikey_Lemieux
5 - Automation Enthusiast
5 - Automation Enthusiast

I’m a bit lost when it comes to formulas. I was hoping someone would be able to help me out! I set up my personal budget with Airtable as you can see with the linked photos.

I have one table for my budget. This has a category column to show what/where money is being spent, as well as, my ideal budget and a column for how much I actually spent.

The second table is for what I spent money on.

Is it possible to total what I spent, grouped by category, then get the sum but only with the same category and send that sum to my “budget” table column “spent”? Then I will be able to track my ideal budget next to what I actually spent. I will be tracking the spends with a form view to add receipts, total’s and category whenever I spend money.

airtable.png

7 Replies 7

Hi @Mikey_Lemieux

The way to do this is to use a Rollup field in the Budget table - something like this:

9c4251903fa737021d637b84c19aa4ca0c9d6c73.png

Looking at your question, I wondered whether your Budget is actually a monthly budget and so I thought I would add how you could also show a total for the current month.

This requires a calculated field in the Spends table (and a date field):

0ff6176e424fb968ea32d245089711b419554e10.png

You can then use another Rollup field in the Budget table to show you a total for the current month - and your tables would look like this:

Spends

Screen Shot 2017-01-11 at 10.45.47.png

Budget

Screen Shot 2017-01-11 at 10.46.39.png

I hope this helps.

Julian

Mikey_Lemieux
5 - Automation Enthusiast
5 - Automation Enthusiast

Julian, thank you for helping! The Rollup field was something I didn’t know about and know I’m so thankful you explained it! Thank you for that. The only thing I couldn’t figure out was the Formula section. Now that I think about what I need, this would be very helpful.

With the current Rollup it will just keep totaling all the receipts and I will need to manually archive them after each month. The way you suggested would allow me to send the “Spend This Month” to keep track of every receipt purchase and only send that month’s spends to my Budget table. This will allow “Budget” to remain as a monthly only view. Does that make sense?

Do I need to add details into the Formula you supplied?

Mikey, you should be good to go with this formula. It would also be possible (with a slightly more complex formula) to do a calculation for any month - for example you could have a last month column.

How did you learn how to write the formulas? Any videos you recommend or learning tools?

This is a good place to start:

Actually, the documentation is pretty good.

Mikey_Lemieux
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you! I’m going to try out the formula you sent again. I’m sure I wasn’t connecting all the dots. Can I send you a free coffee?

Hey!

Apologies for not seeing this earlier! We recently made a video with an introduction for formulas on Airtable if you’re interested: https://vimeo.com/200915169 :coffee: ️

Hope it’s helpful!

Cheers,
Jane