Add Numbers, Based On Variables, Up Until A Checkbox Is Checked

celtics345

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!

Kelly Moore

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

celtics345

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!

Kelly Moore

I'm glad you were able to get this to work for you.

Kelly