Summing columns from multiple sheets

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?

Answers

  • Andrรฉe Starรฅ
    Andrรฉe Starรฅ Community Champion

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrรฉe Starรฅ | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Joshua Medina
    Joshua Medina โœญโœญ
    edited 04/20/20

    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รฅ
    Andrรฉe Starรฅ Community Champion
    edited 04/20/20

    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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrรฉe Starรฅ | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Joshua Medina
    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รฅ
    Andrรฉe Starรฅ Community Champion
    edited 05/14/20

    @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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrรฉe Starรฅ | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

NEW Smartsheet API Documentation - bookmark the updated link! https://developers.smartsheet.com