Summing columns from multiple sheets

Joshua Medina

Hi, I have multiple sheets managing separate projects with similar columns. I would like to sum the data in a particular column across all sheets to get a total number for the dashboard in the Metric Widget. Is this possible?

Andrée Starå

Hi Joshua,

Yes, it is.

I’d recommend creating a so-called Master Metric Sheet and collect all metrics there and then sum them together.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or 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!

Joshua Medina

Thanks Andree, I was trying to do with a report. I would pull all the rows in that were not null and then use the sum of the column in a metric, however, you can't use a report in the metric widget which is frustrating.

What would be the best way to "collect all metrics" in the Master Sheet you are referencing.

Andrée Starå

Happy to help!

I'd recommend to either have the sum in section on each page and then use cell-linking or cross-sheet formulas to collect it all in the Metric Sheet.

Make sense?

Joshua Medina

Hi, thanks again. That would be a real pain to maintain as I would have to remember to add a new row on that sheet every time we added a new project. Since we run close to 100 of these a year (didn't originally mentioned that) I would not want to do that and there would also be a risk of forgetting on new projects. Unfortunately I think the the most efficient way here is to create the report I mentioned, which is dynamic when adding new project sheets in the folder, then copy and paste the report in a google sheet where I have have the data summed and linked to a google slide. Then I can use the web content widget to display the slide with the metric.

Do you happen to know the rationale as to why you can't use a report in the metric widget? That would solve this.

Andrée Starå

@Joshua Medina

It doesn't take that long, and if you add it to the steps to initiate the project, it will become a natural step. You could use cell-linking, and then it would tale max 30 seconds to add the project to the metric sheet.

That said, if it works with the Google Sheet/Slide, that's also an excellent option.

Unfortunately, I don't know why reports can't be selected for metrics.

Please submit an Enhancement Request when you have a moment to let Smartsheet know that you would like that option.

✅Remember! Did my post(s) help or 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!