Formula for automation help

Hello Smartsheet gurus!

I am trying to automate a step-challenge for teams at work.

Team members submit personal step counts via webform to Sheet 1.

Sheet 1-Column titles: Team Name (drop down), Team Member (free text), Steps Entered (free text)

Numbers submitted are automatically summed on another sheet, as an ongoing tally of each teams step progress on Sheet 2.

Sheet 2-Column Titles: Team Name, Steps total, Steps left to goal

I need a formula that will pull and sum the steps entered via webform on Sheet 1 and display them next to the corresponding Team Name on Sheet 2.

Find more posts tagged with

Formulas

Accepted answers

All comments

Kerry St. Thomas

Sheet 2 should have Team Name prepopulated, and deduplicated (pretty much the same as the dropdown on Sheet 1).

With that in place, make the Steps Total column a column formula with this:
=SUMIF({Sheet 1 Team Name}, [Team Name]@row,{Sheet 1 Steps Entered})

Mind you, you'll have to manually select the ranges currently listed in the {} sets so that Smartsheet knows what it's supposed to look at - the easiest way to do that is to NOT copy & paste the formula, but to type it - and click "Reference Another Sheet" in the popup when you reach each of the spots the curly brackets are. More info about cross-sheet formulas is here: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.