Combining data from multiple rows


I have a dataset that pulls timestamps for individuals, however each day usually has multiple timestamps (before lunch, after lunch) along with other paycodes to fill in missed time. Below is an example of how the raw data looks:

How can I get a list of each person with their total time for each paycode/comment? I would like to not have a reference sheet with each person's name on it since the roster keeps changing over time. I know there is a way to do this in reports, I have already set one up, but I need to add additional columns for workflows (like an update request to find out what has been approved for each person). Is there a way to do this on a sheet?

Thanks in advance for any suggestions!


  • MedaUser
    MedaUser ✭✭✭✭

    Since you're trying to sum hours by multiple types of categories (i.e. 1. Paycode, 2. Comment), then reports is the best way to go without doing more manual work. Adding columns shouldn't create issues for your report if that was a concern.

    The 2 options that exists within the sheet that I can think of are:

    1. Sheet Summary - This will require you to create new "fields", which operate like columns, for each user and paycode/comment. This will be tedious, but once you have it setup with the formulas, then you're done.
    2. Using Hierarchies (i.e. Parent/Child) - This will require a lot of manual work as you will need to move new entries to the sheet under the correct parent row, so the calculations in the parent row will update.

    Good luck!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!