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.

[5arahlou]

Hi Adam!  Just jumping on an old post as i've done something the same as this which works a treat, however i want to add the 'summaries' tab to interface to see the total value for the current week.  but to do this i think need to convert the week name to a date format i.e week commencing?  Do you know if there's a way i can do this without creating an extra column and manually entering them all?  Thank you so much, Sarah 🙂

[TheTimeSavingCo]

Hi Sarah!  Sorry, I don't really understand.  If you could provide an example of the value you'd want to see I can see what I can do! 

For example, if the value is "14 2024", what would you want it to show instead?