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("alyssakatz26@gmail.com", @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!

Find more posts tagged with

Formulas

Reports

Project Management

Accepted answers

Andrée Starå

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!

All comments

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.

Cheers,

Ramzi

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!

Andrée Starå

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

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.