Summarize project sheet hours by person

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

  • 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}, [email protected])

    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.


  • 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?

  • 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.

  • 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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • GordonGordon
    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!

Sign In or Register to comment.