# Timesheet-like function for multiple submissions - How to rollup data for each matching field?

Topic Labels: Formulas
Solved
888 4
cancel
Showing results for
Did you mean:
4 - Data Explorer

I have a form that users submit each week to log hours. I have a table that captures each submission in a new row along with how many hours they have left in total (not per week). I have fields that capture the hours logged that was submitted, the total available hours, and how many hours are left, but I am unable to get it to add up all of the hours for a single user and then provide me with the remaining hours.

As an example,

Jane K. (1st week)

Hours logged this week = 4. Total available hours = 20. Remaining hours = 16.

Jane K. (2nd week)

Hours logged this week = 10. Total available hours = 20. Remaining hours = 10.

How do I get the 'Remaining hours' total to be 6 (4 + 10 = 14 minus 20)? I would like to base it off of the first field in my table which is a concatenation of two values.

Thanks!

1 Solution

Accepted Solutions
5 - Automation Enthusiast

In your synced table that the 'person' field is coming from, make sure you have also added a lookup field to the field you have for 'hours logged' in the table you've shown.

Then, you just need  add a rollup field that sums the 'hours logged' field from the table with the list of users.

4 Replies 4
5 - Automation Enthusiast

If you have the Users in a separate table, you can use a rollup field to accomplish this. In the table where their form responses are logged, you have the hours logged and the available hours fields. If you haven't already, add a linked record to the table where you have the users stored. In the Users table, add a lookup field that pulls the hours logged values to that table. Then, back in the first table, add a rollup field like below. After that, you can just add a final formula field that gives you their total available hours-rollup

4 - Data Explorer

Hey Emily! Thanks for the help; however, my setup is a bit different.

My "Person" field is a lookup from a synced table.
My "Available Hours" is also a lookup from the same synced table but a different field.

These synced fields that I'm pulling from get updated by other teams, and I have everything pulling into one table.

This is the way it is currently setup:

5 - Automation Enthusiast

In your synced table that the 'person' field is coming from, make sure you have also added a lookup field to the field you have for 'hours logged' in the table you've shown.

Then, you just need  add a rollup field that sums the 'hours logged' field from the table with the list of users.

4 - Data Explorer

Great, thank you! I got it to work by adding the lookup field to the other synced table as you demonstrated.