Question on SumIFS


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("", @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!

Best Answer


  • Ramzi K
    Ramzi 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}, Rider@row, {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.



    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (

    Feel free to email me:

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

  • Vanessa Sandom

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!