Copy unique email address then sum entries question

Options
MBMaddox
MBMaddox ✭✭
edited 09/20/23 in Add Ons and Integrations

I'm relatively new to using Smartsheet, and I have a question regarding a formula I'm trying to create. My colleagues and I are tracking our steps for a work event, and I've set up a sheet and a form for them to submit their work email addresses, dates, and total steps. What I want to achieve is a formula that can automatically search for their email and sum their steps, which I plan to use in a Dynamic View to maintain privacy.

I've managed to create a sum formula using the following: =SUMIF({GIG Challenge Tracker Range 1}, "testemail@fakemail.com", {GIG Challenge Tracker Range 2}). However, I've been manually revieweing the submissions and creating a new formula for each new email address that is submitted. I'd prefer not to manually add email addresses to the formula. Is there a way to create a formula that can automatically identify and include new email addresses submitted in the sheet and then calculate the total steps for each person?

I initially attempted to create a report and group the submitted steps by email address, then summarizing the steps for that email address, but unfortunately, grouping doesn't display correctly in Dynamic View. I apologize if my explanation wasn't very clear, and I appreciate any help you can provide.

Tags:

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    Options

    Hello @MBMaddox ,

    What I would do is create a sheet with 2 Columns. Column 1 = Email, Column 2 = SumIf formula and make it column formula. You will be manually adding any new email that comes in from the form.

    Or you can create a Pivot if you have access.

    Row = Email

    Column = Blank

    Value = Steps (Sum)

    ...