Summarize project sheet hours by person

Is there an easy way to summarize the total hours for all tasks on a project sheet by person?

I know I could use SUMIF/SUMIFS, but the problem is that I don't know how to provide it with the input criteria for the Assigned To column in a simple manner since that can be anyone of our 30+ team members.

I'm sure there must be a simple way that I'm just overlooking....

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/14/20

    I use a metrics sheet to pull the counts and totals I need from a data grid sheet. I then display the metrics figures as graphs on a dashboard display.

    In my example, I want to show the total value of all Bills of Lading in Closed status for each warehouse employee. In my metrics sheet I have a Name column and a Data column. Obviously the employee name goes into the Name column, and in the data column I use SUMIFS and reference the grid sheet (called "BOL Active Tracking") as follows:

    =SUMIFS({BOL Active Tracking Value}, {BOL Active Tracking Status}, "Closed", {BOL Active Tracking Assigned To}, Name@row)

    In plain English: Add up the values in rows in "BOL Active Tracking" sheet with Closed in the Status column, where the Assigned To person matches the Name column for this row.

    When writing your formula, use the prompt to "Reference another sheet" to select the appropriate column range in the project sheet. The system will allow you to select the sheet, the column, and to name the range something meaningful to you. In my case I just use the Sheet name followed by the Column name, ex {BOL Active Tracking Value}. The system adds the {} brackets around the reference range.

    Result:

    Name Data

    JOHN SMITH $189,560.00

    JANE DOE $157,495.08

    JACK SPRAT $24,128.00

    etc.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Gordon
    Gordon ✭✭✭✭✭

    Thank you for the very thorough answer. Unfortunately, we have 250+ project sheets, so to create a separate metrics sheet for each project sheet is not possible.

    Your formula suggestion did give me a thought though - what if I use a "master list" of all staff in an external sheet as the criteria? The only problem is I am not sure how to get SUMIFS to return the criteria "name" that it is matching against to sum?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Ah, you said "a project sheet," not 250+ sheets, lol.

    The metrics don't have to be on a separate sheet. You could add a column for sum total of task hours, list all your employees in rows at the bottom of the sheet, and in the sum column for those rows put in the formula to add up the hours. Add the new column, names, and formulas to your project template for when you create new project sheets and it will be there automatically.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/14/20

    Hi @Gordon

    To add to JReisman27's excellent advice.

    You could maybe use Sheet Summary instead and then you can get an easy overview in a report.

    Would that work?

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Gordon
    Gordon ✭✭✭✭✭
    edited 02/14/20

    Thank you both for the suggestions. The tasks will be assigned to any one of our 30+ team members in the assigned to column, and the same person may have multiple tasks assigned to them within the sheet. I want to get a summary total of hours across all tasks by person. In essence, I want to build out a pivot table total of hours by person for the project within a separate area in the same sheet or in summary fields.

    I do have a complete list of all team members in an external sheet that I can reference, but I'm still struggling with how to incorporate that into the SUMIFS formula for criterion1 to evaluate the criteria range1 (the assigned to column in the project sheet). Could I somehow use INDEX/MATCH?

    Thank you for any suggestions!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Build your table at the bottom or top of the sheet. One row for each possible employee. Use the Assigned To column to list your employees. In the next text/number field enter this formula on each row of the table:

    =SUMIF([Assigned To]:[Assigned To], [Assigned To] = [Assigned To]@row, [Hours]:[Hours])

    Result:

    Assigned To Hours

    John Smith 56

    Jane Doe 72

    etc

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Gordon

    Happy to help!

    Did you get it working? Let me know if you need more assistance!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Gordon
    Gordon ✭✭✭✭✭

    Unfortunately, not yet. I cannot figure out a way to have it summarize without having a hard-coded list of all our staff in one column. I can do an external sheet reference, but then I don't know what person the SUMIF is returning a value for.

    I thought maybe there was a combination of INDEX/MATCH and/or somehow incorporate DISTINCT so that I can have the person's name be returned and in another cell, the total hours in the sheet. I'm fine with 4-5 rows of range for the summary totals at the bottom or top of the sheet, but it gets clunky and awkward to have the full list of 30+ staff.

    I thought about sheet summary, but I don't see how that would help with my formula issue, except to provide a good "off-sheet" location to show the summar.

    I have a pro desk appointment scheduled for next month. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Gordon,

    It sounds like it would work if you add two columns.

    One contact column and in it you'd add all the people.

    Another column where we'd sum the hours.

    And then something like.

    =SUMIF(Person:Person; PersonHelper@row; Hours:Hours)

    The same version but with the below changes for your and others convenience.

    =SUMIF(Person:Person, PersonHelper@row, Hours:Hours)

    Depending on your country, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Then we'd either use cross-sheet formulas or cell-linking to collect everything together in a Master Metric Sheet.

    What do you think? Would any of those options work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • klizotte
    klizotte ✭✭✭

    Hi Gordon, were you ever able to get this to work? I have the exact same challenge! Kate

  • Gordon
    Gordon ✭✭✭✭✭

    @klizotte Unfortunately, no. I had a Smartsheet Pro Desk session and went over my issue with the advisor, and although he had several good suggestions, we concluded there's no way to implement exactly what I was trying to do with current functionality and without the pivot table add on. I could do it easily on an individual row basis for the "assigned to" in that row, but then if the person had another task assigned to them somewhere else in the sheet, the hours data is duplicated, and there wasn't really an effective way to either: 1) de-duplicate the total hours data or 2) de-duplicate the contacts in the sheet.

    Perhaps we both missed something, so please let me know if you can think of any other means to accomplish this.

  • klizotte
    klizotte ✭✭✭

    Drat, well what I ended up doing was linking to Tableau but I was hoping to do the same thing inside SS so I didn't have to go to another location. Thanks for getting back to me, I will stop wasting time and keep it where it is.

    Thank you,

    kate

  • Gordon
    Gordon ✭✭✭✭✭
    edited 06/29/20

    I found an answer to my question via another Community post here:


    Thanks @Paul Newcome and @Frank Falco for your contribution!


    Hey @klizotte check out the linked community post above. I was able to solve my problem by adapting the suggested formulas.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!