Summarize project sheet hours by person

Gordon

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

Jeff Reisman

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.

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

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å

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

Gordon

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

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

Andrée Starå

@Gordon

Happy to help!

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

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å

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

klizotte

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

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

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

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

https://community.smartsheet.com/discussion/67208/how-to-get-distinct-values-from-a-column-then-shows-their-maximum-number

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.