Question on SumIFS

10/17/20
Answered - Pending Review

We're hosting a "Bike to the Moon" where our supporters are uploading their daily trips, miles and emails in a worksheet. We want to display a riders' individual trips (showing date and miles traveled) and a total miles field that sums across all their trips (rows.)

I'm using this formula at the moment but I'd rather not have to manually swap out this formula's email every time I need to sum someone else's miles:

=SUMIF(Email:Email, FIND("[email protected]", @cell) > 0, [Actual Earth Miles]:[Actual Earth Miles])

I don't know if it's possible in Smartsheet, but ideally I'd like to show only an individual's trip details and total miles when they're the current user, so everyone else's trips and miles are hidden from the current user.

Any help and/or advice would be greatly appreciated!

Answers

  • Ramzi KRamzi K ✭✭✭

    @Vanessa Sandom @Vanessa Sandom

    One way to do it is to create a summary sheet with all the user's emails (column of type Contact) - Call it Rider. Then create another column and call it Total Miles. Then you can use this formula in the Total Miles column to reference your main data sheet:

    =SUMIF({Miles Tracker Sheet Email Column}, [email protected], {Miles Tracker Sheet Actual Earth Miles Column})

    In this example the Miles Tracker Sheet looks like this:

    And the Summary Sheet looks like this:

    Then create a report off of the Summary Sheet that filters on the Rider column as Current User.

    Now comes the tricky part in order for this to work and my assumption is that this is the case - your riders are registered users in your Smartsheet account and that they are given permission to the underlying sheets and report. So when they open the report, they will only see their row.

    Best practice is to put all this in a workspace and give the users access to the workspace.

    If they are not registered users, this will not work. If you want total privacy of the main Mileage Tracker sheet and not give anyone access to it, you will need to look into using a premium add-on to Smartsheet called Dynamic View.

    I hope all this helps.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Hi Ramzi. thanks for the feedback, but it looks as though you've confirmed we're going to need Dynamic View. You've confirmed our assumption. Thanks for your help!

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 10/20/20

    Hi @Vanessa Sandom

    To add to Ramzi's excellent advice/answer,


    It might be possible to create a solution without Dynamic View for this use-case.

    I've developed a similar solution recently for a client.

    If you have access to Dynamic View, I would recommend using it because that will take less work than setting up my solution.

    How many supporters will you approximately have in total?


    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    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 PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.