Add Numbers, Based On Variables, Up Until A Checkbox Is Checked
Hello all. Got a formula request I'm not smart enough to answer. Currently, we're using this formula to track total hours worked (for training purposes).
Basically, add up all numbers in this column based on these parameters. Previously, this formula wasn't an issue since the moment an operator cleared a position they would cease appearing in Smartsheet. Therefore, the <TODAY parameter would include all training and nothing more.
However, upon expanding out to another group, they do things a little differently. They clear on positions that they then continue to train on in different capacities, so their training sessions will continue to appear in Smartsheet. But we want the numbers to stop counting once they clear the first time.
So the idea is this. How do I write a formula that adds up a column's numbers, not in totality, but up to a certain point. That point in particular being the selecting of a checkbox. Once that box is selected we'd like the numbers to cease counting.
I imagine it'll be similar to the formula above, just not the <TODAY aspect. Any help would be appreciated!
Answers
-
Hello @celtics345
The Record Date automation is one way of accomplishing your goal. You will need a date helper column for the Record Date to work. The helper column can be hidden and squished all the way to the right of the sheet - it is just there to help your automation and provide a receptacle to capture the date.
If you've never used the Record Date automation, it is in the Automation menu in the smartsheet ribbon. The Trigger will be when a Row is Changed or Added. You will then select your checkmark column and when its value = checked. Scroll down the automation window and select Record Date. You will insert the name of the newly created Date Helper column then save the automation.
*In my formulas I referred to your Helper column as Helper. Be sure to edit the formula to reference the real name of your helper column.
The SUMIFS becomes
=IF(ISDATE(Helper@row), SUMIFS({Actual Training Hours}, {Event Type}, "(tr)", {Trainee}, Trainee@row, {Discipline}, Qualifier@row, {Event Date}, @cell<=Helper@row), SUMIFS({Actual Training Hours}, {Event Type}, "(tr)", {Trainee}, Trainee@row, {Discipline}, Qualifier@row, {Event Date}, @cell<TODAY()))
Will this work for you? If you need any help setting up the Record Date automation, let me know. I'd be happy to help walk you through it.
Kelly
-
This is an incredible response Kelly, thank you so much! You explained things perfectly and provided a formula which worked on my first try. I had to reference a different checkbox for the Helper column, but it worked all the same. Thank you again!
-
I'm glad you were able to get this to work for you.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!