Counts of statuses across multiple sheets

daisyq

I have several sheets in a workspace which track specific tasks. Each of the sheets has the same columns and formatting. I want to be able to show on a dashbord the count, by status, of each of the columns where the status is being tracked. What is the best way to do this?

Genevieve P.

https://community.smartsheet.com/discussion/comment/328107#Comment_328107

Hi @daisyq

I can see that you have your report grouped by the Sheet Name. If you need the Report to generate numbers, you would want to Group by your preferred Status column instead, then apply Summary to that column, too. (See: Summarize content to extract key information with report builder)

You could create a second level of grouping, so you could see the individual counts per-sheet, but Charts in Dashboards can only take data from the top summary rows. (See: See: Configure grouping to organize results in report builder)

Keep in mind this also may mean you'd need to create multiple Reports, one for each of your status columns.

An alternative would be to set up Sheet Summary Fields in each of your sheets: Sheet Summary

You can use a COUNTIF Function to Count how many times a specific value appears in a specific column. Ex:

=COUNTIF([(1) Mapping]:[(1) Mapping], "Not Started")

Then as long as you've added these Summary Fields with the exact same name to each sheet, you can create a Sheet Summary Report instead of a Row Report. This can bring all your formulas across all your sheets into one place! See: Sheet Summary Reports

Cheers,

Genevieve

Mike TV

Do you want a report? Example, I created 4 sheets quickly each called "Status Sheet 1" then "Status Sheet 2" etc. Filled them in like this:

Then I created a report which has all 4 sheets. The report has just 3 columns. The Primary, the Status, and the Sheet Name. Then I grouped it by Sheet Name and then by Status. Here's an example of how the report is reporting on Status Sheet 1:

Andrée Starå

Hi @daisyq

I hope you're well and safe!

To add to Matt's excellent advice/answer.

• The easiest way would be to use a Report.
• Another way would be to create a so-called Master Metrics Sheet where you collect all the Metrics and, if needed, you can do further calculations.

Which one do you prefer?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

daisyq

I have the report built but now I want metrics to pull from that report into a dashboard. It should count the number, by status in each column. Each column represents a step in data migration.

Genevieve P.

https://community.smartsheet.com/discussion/comment/328107#Comment_328107

Hi @daisyq

I can see that you have your report grouped by the Sheet Name. If you need the Report to generate numbers, you would want to Group by your preferred Status column instead, then apply Summary to that column, too. (See: Summarize content to extract key information with report builder)

You could create a second level of grouping, so you could see the individual counts per-sheet, but Charts in Dashboards can only take data from the top summary rows. (See: See: Configure grouping to organize results in report builder)

Keep in mind this also may mean you'd need to create multiple Reports, one for each of your status columns.

An alternative would be to set up Sheet Summary Fields in each of your sheets: Sheet Summary

You can use a COUNTIF Function to Count how many times a specific value appears in a specific column. Ex:

=COUNTIF([(1) Mapping]:[(1) Mapping], "Not Started")

Then as long as you've added these Summary Fields with the exact same name to each sheet, you can create a Sheet Summary Report instead of a Row Report. This can bring all your formulas across all your sheets into one place! See: Sheet Summary Reports

Cheers,

Genevieve