Counting from Sheet Summaries for Reporting

ryanhnelson

Hey there,

Novice user here with likely a novice question — however, a Google search didn't lend an answer to my question. :-)

I have a field that's currently in sheet summaries within a workspace, and that field is populated via a drop-down menu to select which team is working on a project.

I know that I can make a Sheet Summary Report to show me a list of the sheets within the workspace and I can include that field. However, I don't know how to get from that point to counting the instances of particular teams to go into a dashboard (e.g., Team A has 10 projects, Team B has 14, Team C has 6).

My first guess was to create an intermediary sheet and use a COUNTIF to count for each team, and then I could use the numeric that comes from that to populate my dashboard. That doesn't seem to work, however, as I can't select a sheet summary report when selecting another sheet in my formula builder for the count.

Any suggestions? Thanks!

Paul Newcome

Reports can't be used in formulas. You would need to collect all of that data onto a sheet using cell linking or cross sheet references and then compile your metrics from there.

ryanhnelson

Thanks for the reply, Paul.

Can you give me a hint on how to aggregate (count) sheet summary fields to get there? Thanks!

Andrée Starå

Hi Ryan, @ryanhnelson

There are two options.

1. Collect the same information somewhere else in the sheet as in the Summary Section and then use cell-linking or cross-sheet formulas to collect it all in a so-called Master Metric Sheet.
2. Use cross-sheet formulas to collect the same information as in the Summary Section but in the so-called Master Metric Sheet instead.

Make sense?

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

ryanhnelson

Hey Andrée,

I appreciate your reply! can you give me information on how I can collect the information from the Summary Section in a Master Metric Sheet? In the template that will be used on all the project sheets I could make an extra hidden column that just contains the same info that's in the summary. Or can you suggest another way to collect that Summary field into a Master Metric Sheet?

Thanks and regards! Ryan

Andrée Starå

@ryanhnelson

Happy to help!

Before we get started!

How many projects would be created approximately per month/year?

ryanhnelson

Hi Andrée, I would estimate in the low hundreds, all based on an identical template.

Andrée Starå

@ryanhnelson

Ok.

The simplest method would probably be to use a section or column in each sheet and then cell-link each of them to the Metric Sheet when you startup the project.

What kind of metrics will it be?

Do you have, or will you have an intake process?

Many questions! 😉

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Andrée Starå

@ryanhnelson

Here's an example with an example of a Metric Sheet.

https://www.smartsheet.com/marketplace/template-set/inspection-tracking