Capturing a Total Count to display in a Dashboard

I am trying to capture the total number of active projects found in a specific Workspace. Each project is its own, separate, SmartSheet. The way I am able to do this now is by manually creating a Formula (COUNTIF({Proj Name Range 1}, "Yes"),in a separate SmartSheet, and then creating a Metric Widget within the Dashboard to display the results. I am hoping there is a much simpler, and automated, way to get this same result. Any ideas?

Thanks

Find more posts tagged with

Workspaces

Sheets

Formulas

Reports

Services

Project Management

Comments

Shaine Greenwood

Hi Bryan,

This is the best way to capture your total of active projects.

If you're looking for a more simple way, please submit a Product Enhancement Request using the form under Quick links on the right of the community site and let our Product team know exactly how you'd like to have this data calculated.

Bryan Sampson

Thanks for the feedback Shaine.

What I am trying to get to is a single report that will display a total count WITHOUT having to have a separate "Formula Sheet".

Right now, if I were to run a Report on the Workspace looking for projects that are marked as "Active" then display it on a Dashboard, I will get the individual projects displayed but not the total count. I am trying to make the Dashboard I am creating more simplistic and not reliant on other Sheets to populate the desired data.

I have opened several enhancement requests in the last few weeks, one of which was for this particular question. Do you know if there is a way to track my submitted enhancement requests?

Thanks

Shaine Greenwood

Hi Bryan,

We currently don't have a system in place for you to track the enhancement requests that you've sent. I still want to encourage you to use the Product Enhancement Request form (under Quick links on the right) to let our Product team know exactly what type of features you're wanting from Smartsheet.

Currently, sheets are the "source of truth" for other features in Smartsheet. For reports and dashboards to gather and visualize data, that data needs to be aggregated (and calculated with formulas) in a source sheet.

Either adding a formula in your projects sheet, or on a separate formulas sheet is the best way to accomplish what you're wanting to do.

spezoinks

I am also looking for something like this. Is there or is it maybe in consideration to be able to reference a report to run your calculations. Reason being is that I use the ability of a report to be able to pull info from a workspace automatically if a new sheet gets added to that workspace. My report gets updated as soon as a new one is added or one is removed... It would be nice to then have the ability to look to that report and grab a count of any of the columns found in that report.

Main idea would be: workspace controls all sheets being produced, Report pulls up to date info from that workspace, calculation sheet pulls up to date info on number of Active Projects (for example) and Dashboard shows this to leadership without the need of anyone linking a new smartsheet to a calculation sheet.

Is there something like that and I am just missing it or could we get this enhancement?

jcarter67946

Greetings,

I too am looking for how I can generate summary stats on projects. All projects are stand-alone sheets within a workspace. I need to summarize (count) how many we have, how many of each type, how many sold by each rep, how many assigned to each engineer, etc.

I'm attempting to understand the use of a dedicated formulas sheet (apparently that is required) but am, admittedly, very novice in this arena so far.

Any guidance greatly appreciated!

Andrée Starå

Hi,

I'd recommend creating a so-called Master Metric Sheet where you'd collect the summary information from each sheet and then you can calculate further on the data and present it in reports or dashboards. You could also gather all the information on each sheet and then cell-link that to the Master Metric Sheet.

We'd use cross-sheet linking and cell-linking depending on the need.

Would that work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Sara_Cook

I'm looking for something like this too. I will make a master metric sheet if that's what it takes, but I don't know what formula to use on that master metric sheet. I want a total number of a CERTAIN TYPE of project. I have created a dropdown column in my sheets titled "Project Type". I want to total each project type, and then display on the appropriate dashboard. If I have 7 projects that are "ABC" project type in that dropdown column, how do I create a formula to count that dropdown column across all projects??

Sara_Cook

I could also add the "Project Type" dropdown to the Sheet Summary of each project. That would make even more sense. If I did that, would there be a way in a report or in a formula to count the number of sheets that have a certain dropdown selected in Sheet Summary?

Sara_Cook

Yes! This is exactly what I want too. A report automatically pulls new sheets that get added. I don't want to have to go manually add my new sheet info to a "Metric Sheet" to be able to pull a number to a dashboard. Just a sum row on the report would be ideal, and then be able to pull that sum to a dashboard. Have you found any workarounds to help with this??

Andrée Starå

Hi Sara,

Do you still need help? (missed your post)

Can you describe your process in more detail and maybe share 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)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Sara_Cook

Hi Andree - yes I still need help! Please and thank you!

Here's a (probably way too detailed) explanation of what I'm looking for:

Essentially, I would like a dashboard for leadership that displays rollup data for all of the different types of projects our team is working on. In our Workspace, we have several different types of projects we work on (emails, case studies, press releases, videos, etc.). I'd like a dashboard that quickly shows that how many projects of each type we are working on at any given time. For example: 6 email projects, 3 case studies, and 3 videos.

I created a field in Sheet Summary to capture "Project Type" on each project. And then I created a Sheet Summary report for each type of project. I can pull each of those reports to a dashboard. However, there's no way for me to show the total number of projects by type. (Screenshot attached to show an example of how I'd like to present this information on a dashboard.)

Ideally I'd also like to have a chart that shows how much of the pie each project type takes up. (50% of our projects are emails, 25% case studies, 25% videos). But I haven't even tried to do anything with that yet until I can figure out how to get an automatically updating total number of projects by type.

I realize I could manually add a number field in the sheet or sheet summary to represent that each project is "1" project, and then have a metric summary sheet that totals that number, but then I would have to update the source data on the metric sheet each time a new project is added. And that would be unmanageable. I'd like a way to sum the number of rows in my Sheet Summary report and then display that sum number as a metric on my dashboard. That way, whenever a new sheet is added to the workspace, it would automatically pull to the appropriate report, and then the sum number on the report would automatically be updated, thus updating the dashboard widget.

That's all I'm looking for. :-) Any advice?

Thanks,

Sara

Marketing Projects Dashboard.png

Karen Cole

Sara,

Did you ever get an answer on this? I need the same thing and can't seem to figure out how to do it!

kchin

I was able to do this using the Sheet Summary fields. In my case, we have a sheet that lists all of the courses we develop. I wanted to get a count of the number of courses that were "a work in progress", and also of a certain type (in this case Sales courses). Here's what I did...

I added a new Sheet Summary field in that worksheet. I used this formula in my field...

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Canceled", [Course Name]:[Course Name], CONTAINS("(Sales)", @cell))

To break it down a bit further in case that helps... My sheet has a column called "Status" which has one of multiple options in it (Complete, Canceled, In Progress, more...), but I just wanted the ones that were not Complete or Canceled (ie, "In Progress"). The first two conditions above help me filter out the "Complete" and "Canceled" items. [Pro-Tip: The quotation marks are critical as that ensures you're filtering on the specific word (letters in that order), and not just the overall collection of letters in the word. Without the quotes, I was getting strange things included unexpectedly.]

Next... My sheet has a column called "Course Name" where we include the word Sales in parentheses if it's a sales course. This is what the CONTAINS element is looking for. If the line item meets all these criteria, then it gets counted. This gives me a Summary field that gives me the count I'm looking for.

Then in my Dashboard, I simply added a Metric Widget which displays that Summary field number. Seems to do exactly what I wanted. Hope that helps.

JoAnn Kennedy

Hey, Sara! I'm trying to do pretty much the same... summarize the data from ALL of my sheet summaries in a sheet (NOT A REPORT) to use in dashboards without a whole lot of cell linking. Have you had a response? Thanks!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…