Summarizing labor hours per person across multiple sheets

Mike Tomei

I’m looking for some advice on how to achieve something. I manage multiple projects, each with their own project plan sheet. On each project plan sheet I have a column that lists the estimated labor hours for each task. On a dashboard I would like to be able to show the total labor hours broken out by person across all projects. Get an idea of the total hours someone has for their assigned tasks across all projects. My first thought was to create a report, use the report’s group and summarize features to see the labor hours by person, then show that in in a dashboard. Am I correct that a report’s group and summarize data can’t be shown on a dashboard? Any other suggestions on how I can achieve this? My main concern is that if I create some sort of a roll up sheet summarizing this data it will be too manual of a process (cell linking) and not as convenient as a report that will automatically include new project plans as they’re added to folders. I’m open to all creative solutions. Thanks.

Genevieve P.

Hi @Mike Tomei

It sounds like a Grouped a Summarized report will be perfect for this scenario!

Grouped reports can be displayed on a Dashboard, either as just the top-level Summary rows or as an expanded out report showing the rows beneath. You can also use the first level of Grouping and Summary as a source for a Chart Widget in a Dashboard, if you'd prefer to show these values in a graph or pie chart.

Here are some examples. If this is the Report:

Then you can have it in a Dashboard as a Report Widget with collapsed rows:

Or you can create different charts:

See: Using Report Widgets

Cheers,

Genevieve

Mike Tomei

Hi Genevieve. Thanks for your message. I was very hopeful that your solution was the answer, but I'm running in an issue that isn't allowing it to work. I often have multiple contacts assigned to a single task. That fact isn't allowing the group/summary function to work on my report. Any suggestions? Thanks.

Genevieve P.

Hi @Mike Tomei

Thanks for clarifying your set-up! Report grouping cannot currently be applied to multi-select columns...you could change the Assigned To columns to be single select and create multiple rows for the tasks.

Depending on how many projects/sheets you have, the alternative would be to set up a Metric summary sheet with formulas. One single column would list out each possible person, then the second column would use multiple SUMIFS formulas to SUM together the totals across your sheets (IF the person in the cell to the left is in that other column).

Ex:

=SUMIFS({Column to Sum Sheet 1}, {Contact Column Sheet 1}, HAS(@cell, Contact@row)) + SUMIFS({Column to Sum Sheet 2}, {Contact Column Sheet 2}, HAS(@cell, Contact@row)) + SUMIFS({Column to Sum Sheet 3}, {Contact Column Sheet 3}, HAS(@cell, Contact@row))

Cheers,

Genevieve