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!
Best Answer
-
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 EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
@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
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 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!
-
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 EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!