SUM fields in a table based on week number in another table.

[PeteScribbles]

Beginner here, asking a question which has no doubt been asked a lot and probably has a simple, obvious answer! Apologies in advance...

I have one table containing all of our customer bookings. In this table there is a formula field that takes the date that the booking was made and assigns it a week number based on a weeknum formula.

I have another table where I have the week numbers listed from 1-52 in a field. The outcome I want is to have other fields that adds up figures from the customer bookings that took place in the same week number. Simple SUM-like formulas that add up how long the bookings went for, how much they cost etc (all of which are fields in the original customer bookings tables).

I'm not sure if I'm using the correct terms when I am googling this info but I haven't found anything that helps as yet.

I made a mock base that might better show what I've got and what I want

 

Basically, I was hoping to have those Total hours and Total $$ fields autofill with the details based on the week number.

I hope that makes sense! Any help is greatly appreciated!

ps - Just so ya know,  I'm aware I can filter a view in the original table that will show this data but I need it in it's own field so that I can export it!

[TheTimeSavingCo · Accepted solution]

You're pretty close!  You'll need to:
1. Create a formula field that'll output the week year of the date field (I recommend adding the year in there so that next year's stuff won't confuse things)
2. Make an automation that'll paste the value of the formula field from step 1 into the linked field to a new "Summaries" table and have a rollup to calculate your totals there

Link to base

[PeteScribbles]

Thanks for taking the time to reply in such detail! I'll give this a shot in the morning - it looks like it will work perfectly though!

Also, I wasn't using the year in there as yet - thanks for that! Great idea.