Rolling up counts from several sheets to a dashboard
I have 6 projects. Each has a risk register on a separate sheet
Each risk has one of 5 possible statuses each of which has a color, (Blue, red, green…).
Each risk also has a Probability/Impact score, grouped by 5s, 125
I have created 10 sheet summary fields on each sheet to count these.
I have created a sheet summary report including all 10 fields for all 6 sheets.
The intent is to create charts for a dashboard
 to show how many risks there in each PI Category
 to show how many risks there are for each color
However when I attempt to make chart on the dashboard, it includes all the zeros when what I want is just the totals
So for example. I would like it to show, 2 for the PI< 5 column, 5 for the PI 610 column, 2 for the PI 1115 column, etc.
I fiddled with the data source and axis settings but I can't get it how I want it.
(BTW Yes I know the chart name is wrong)
Do I need to start over and just create summary fields on a separate metric sheet and pull the chart data from there?
Thanks for helping,
Best Answer

A separate metric sheet would be the way to go. You can write cross sheet countif(s) formulas to get the values from all 5 sheets by adding them to get a consolidated P1 <5 value, P1 610 value, etc. So something like =COUNTIF({Sheet 1 range}, "Blue") + COUNTIF({Sheet 2 range}, "Blue") + …
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers

A separate metric sheet would be the way to go. You can write cross sheet countif(s) formulas to get the values from all 5 sheets by adding them to get a consolidated P1 <5 value, P1 610 value, etc. So something like =COUNTIF({Sheet 1 range}, "Blue") + COUNTIF({Sheet 2 range}, "Blue") + …
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

Thanks @aravindGP for confirming my suspicions. I can still use the sheet summaries elsewhere so at least all that effort was not for naught!
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives