Reporting multiple criteria from multiple sheets

Angie Little

I am trying to create an output (maybe a report?) that shows multiple criteria from multiple sheets. I'd like to show each project (I have a sheet for each), with the assigned personnel by "task type" and "priority". I'm not really trying to do true resource loading. I just want to see number of tasks assigned to each person that are "task type" A or B AND "priority" A, B or C (See my example below). Creating sheet summary fields for each person by task type and priority seems labor intensive. It'd be even better if I could add some color to it (high priority over 9 project tasks = red).

Example:

Any suggestions?

jmyzk_cloudsmart_jp

Hi @Angie Little

This is an interesting topic, so I have created a solution to demonstrate.

The easy way is to use a report.

In the project sheet, create a column called Type / Priority that combines Task Type and Priority, enter the values in a formula, and group them using this as the key.

In the report, you can group by Assigned to, Type / Priority, and then create a Summary by [Type / Priority] with Count to show the number of Type / Priority for each Assigned to.

Please take a look at the demo dashboard below.

https://app.smartsheet.com/b/publish?EQBCT=d236b9e1c7b24df294712f588ed05e97

Doing the same thing with a sheet is a bit more complicated.

First, for Project A, Project B, and Project C, you would create a range, for example, {Type / Priority A}, {Assigned to A} for Project A. 

Then, for Project B and Project C, you would create a range {Type / Priority A}, {Assigned to A}. Similarly, create ranges for Project B and Project C, for a total of six ranges.

Then, using the ranges on each sheet, the number of tasks is obtained by using the data in the [Type / Priority] column of the Summary Sheet, which shows the aggregate results, for example, A/A, B/B, as keys, and using the following formula to condition the number of tasks on the value of each Assigned to.

=COUNTIFS({Type / Priority A}, [Type / Priority]@row, {Assigned To A}, PARENT()) 

+COUNTIFS({Type / Priority B}, [Type / Priority]@row, {Assigned To B}, PARENT()) 

+COUNTIFS({Type / Priority C}, [Type / Priority]@row, {Assigned To C}, PARENT()) + ""

In the above expression, Parent() is used because the Assigned To value used as the condition is in the parent row.

To show the result in Contact List Column, I added + "" at the end. Since the result is a text, I added Val 1 to Val 3 to convert it to values to use in conditional format and SUM function.

Comparing the two methods, the report is scalable because it can handle an increase in the number of projects by simply adding more source sheets.

The one by sheet is not as scalable, but it allows you to display the results in the format you desire.

Angie Little

Thank you! I will try both on a sample and (assuming I can make them both work - I could potentially have a few more questions along the way) I'll present both to my team.

jmyzk_cloudsmart_jp

@Angie Little

Feel free to reach out with questions or issues as you test these. 

Good luck!

Angie Little

@jmyzk_cloudsmart_jp I was able to use your first suggestion for some of our reporting, but am digging deeper and wanted to try this 2nd option as I think it might be better for the number of projects we're dealing with (over 75!). I understand how to create ranges for specific criteria, but I'm not sure I completely understand the above...

I have the following for each project:

Priority: 0, 1, 2, 3

Assigned: multiple tasks for multiple people within each project (in other words 3 assigned to Bob, 4 assigned to Bud, etc. within a project).

Input Type: 3 input types (support, implementer, technical)

For each person, I'd like to be able to see, for example, Bob has (2) priority 1 "technical" tasks, (10) priority 3 "support" tasks, (4) priority 2 "implementer" tasks, etc.

Is this feasible with what you've suggested? And if so, I don't think I understand how I would set up the ranges to count these. Thoughts?

Genevieve P.

Hey @Angie Little

If you have over 75 sheets, I would highly recommend staying with the Report option. This will make it very easy to simply add in new sheets into the Report when they're created.

The other option (formulas) requires multiple individual references to each sheet. Since there's a maximum of 100 cross-sheet references, you'd hit that limit before reaching sheet 20.

Reports can have 3 levels of Grouping:

1. By Assigned (as long as it's single select)
2. By Priority
3. Input Type

This will show you the breakdown of priority and tasks per-person across all 75 sheets. Note that if your Report brings in more than 250 rows (which it is likely to do), this summary won't be expandable.

If this is the limit you're running into, you may want to create a few different types of Report.

1. First Report only grouped by Assigned to COUNT how many tasks each person has (you can display this as a graph on a Dashboard if you'd like!)
2. Multiple individual reports per-person, Filtered by one person (e.g. "Bob") and then Grouped by Priority to see that more granular count.

Cheers,

Genevieve