nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Sheet Summary - Column Totals - How to Combine in a Report

Jenny A.

I have #'s that pull from each project into each sheet's - sheet summary.

Those then get pulled into a sheet summary report. They are currently grouped by what channel they are in. Each column gives a total sum.

I was asked to see if we can combine the total sum into one number, so it shows how many total SKU's are being worked on. Below is a screenshot with certain things redacted.

Any help is much appreciated. Thanks!

Find more posts tagged with

Sheets

Formulas

Reports

functionality

Accepted answers

All comments

Paul Newcome

The only way to get this in there would be to include a new sheet summary field that includes this total in each sheet then add it to your report the same way you have the separate columns already in there.

You can reference sheet summary fields in sheet summary formulas if adding each of the existing fields together is easier for you to get the grand total on each sheet.

Jenny A.

I would have to manually go into 125 projects to add the new sheet summary field (which I have done before). And it pulls from specific lines within each project which can change line # depending on the project.

I am not sure the reference piece would work, as it would have to pull from a bunch of projects and continue to pull from new projects.

Jenny A.

@Paul Newcome so if I go through each of the projects and add a new sheet summary field that adds together the three fields everyone wants to see together - is easy enough.

It pulls from certain fields within the project. The issue is that until we get to certain stages there are words in those fields to prompt people to fill them out with #'s. Is there a formula I can add that would just add together the #'s and not include the words?

Ex of the formula is below:

=Notes17+Notes200+Notes325

Ex of what it might return if there are not #'s in each Notes field:

2QTY OF SKUsQTY OF SKUs

Paul Newcome

Try a series of IF statements.

=IF(ISNUMBER(Notes17), Notes17, 0) + IF(ISNUMBER(Notes200), Notes200, 0) + IF(ISNUMBER(Notes325), Notes325, 0)

Jenny A.

Thank you - that worked! I wish there was an easy way to just mass update adding this field to every project, but the cells will not be the same between project.

Jenny A.

One more question about something I just ran into for this. If I wanted to do a metric widget with the just the total # of SKU's from all the sheets how can I easily do that?

Leadership just wants to see the overall total SKU count. I can pull all the Total SKU Count fields from Sheet Summary into a report, but reports will not go into the metric widget for dashboards. Thank you for all your help!

Paul Newcome

Happy to help. 👍️

Did you mention that you already have sheet summary fields for each of the groups? If so, you shouldn't need to reference specific cells. If you already have those individual numbers as sheet summary fields, you can add sheet summary fields together.

=[Summary Field 1]# + [Summary Field 2]# + [Summary Field 3]#

You can also use the added IFs the same way but reference the summary fields.

You would still have to go into each of the project sheets, but at that point, you should be able to just paste the same formula over and over again which should help speed the process up a little bit.

Paul Newcome

You are right that you can't use a report to populate a metrics widget, but you can use a grouped/summarized report in a report widget that is sized to only show the section that has the number you want displayed.

Jenny A.

That is cool and works, but if there are words in the selected sheet summary text how can I remove those? It does the same thing as if I was just adding them together with nothing else.

Ex: 2QTY OF SKUsQTY OF SKUs

Paul Newcome

You would use the IFs added together as in my previous post. The one with ISNUMBER worked in.

=IF(ISNUMBER([Summary Field 1]#), [Summary Field 1]#, 0) + IF(ISNUMBER([Summary Field 2]#), [Summary Field 2]#, 0) + IF(ISNUMBER([Summary Field 3]#), [Summary Field 3]#, 0)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.