Increasing and Decreasing Totals
My smartsheet calculates points based on employee call-ins. When the points total changes/goes above 8, we send an email stating that the employee reached between 8 and 9.5 in half point increments. The question has come up where an employee is at 8.5 and then points get updated down to 8, another email is sent for review. Does anyone have experience with how to NOT send the second email?
Thanks for your help!!
Jennifer
Best Answer
-
If the automation is triggered by a change to the "8 or 10" column, and the change to the points column does not change the "8 to 10" column, then a second alert would NOT be sent. Nothing in that field changed to trigger the alert. So I think you're good!
BTW, doing it the way you did is brilliant, and far less complicated than what was going on in my head. You taught me something today, so thanks!
Some notes on your SUMIFS formula: When looking for criteria like an exact match of a name from the row in the Name column, you don't need the AND, CONTAINS, or @cell.
You need AND if you're giving a formula more than one criteria to look for in a logical statement, such as in an IF (i.e. =IF(AND(Name:Name = Name@row, Injury:Injury = "Broken Neck"), "Oh No!", "Oh Good!") where both things need to be true. With functions like SUMIFS or COUNTIFS, "AND" is kind of built-in to the functions, since they're designed to consider criteria from multiple ranges. You can use AND if you want to consider two things about the same range in a SUMIFS or COUNTIFS at the same time - ex. =COUNTIFS(Date:Date, AND(MONTH(@cell) > 1, MONTH(@cell) < 8), Name:Name, Name@row)
CONTAINS is only needed if you are searching for a text value that may only be part of the text in the cell. Maybe you have a Name column where the name isn't always entered in lastname, firstname format. Then you'd use AND, CONTAINS, and @cell: ex. =COUNTIFS(Name:Name, AND(CONTAINS("Vitela", @cell), CONTAINS("Jordan", @cell)). This way if counts all cells that have both these names in them, in whatever order, comma or no comma.
@cell is only needed when you need to perform a function on values in your range so that COUNTIFS or SUMIFS act only on the results of the function. So if you want to count all the rows with a date in June, you need to use the MONTH function on the date values, before the COUNTIFS can determine if the date is in June. ex. =COUNTIFS(Date:Date, MONTH(@cell) = 6)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
This is a tricky one. It would be great if the change cell value automation would allow us to copy one cell value into another at a given time, but alas it does not. There may be other more complex ways to do this, but I need some information.
From your screenshot, it looks like an employee can have multiple rows in this sheet. Is this the sheet you are using to send the alert? How are your PointTotals being calculated? By adding up all the values in the Points column for that employee - Something like =SUMIF(Name:Name, Name@row, Points:Points) ?
If this is the way you're doing it, let me know - I have an idea of how to make this work. There will be a bunch of moving parts, but nothing too daunting!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Here's the points total formula:
=SUMIFS(Points:Points, [Absence End Date]:[Absence End Date], >=TODAY(-365), [Absence End Date]:[Absence End Date], <=TODAY(), Name:Name, AND(CONTAINS(Name@row, @cell)))
I also have another column call 8 or 10 that is shown below that I can trigger automation off of...
=IF(PointsTotal@row >= 8, "8", IF(PointsTotal@row >= 10, "10", "1"))
If the points total row goes to 8, the 8 or 10 column will be 8, if it's 8.5, it'll be 8. If the points total changes, but the value in the 8 or 10 column recalculates and stays at 8, do you think it will trigger automation again?
-
If the automation is triggered by a change to the "8 or 10" column, and the change to the points column does not change the "8 to 10" column, then a second alert would NOT be sent. Nothing in that field changed to trigger the alert. So I think you're good!
BTW, doing it the way you did is brilliant, and far less complicated than what was going on in my head. You taught me something today, so thanks!
Some notes on your SUMIFS formula: When looking for criteria like an exact match of a name from the row in the Name column, you don't need the AND, CONTAINS, or @cell.
You need AND if you're giving a formula more than one criteria to look for in a logical statement, such as in an IF (i.e. =IF(AND(Name:Name = Name@row, Injury:Injury = "Broken Neck"), "Oh No!", "Oh Good!") where both things need to be true. With functions like SUMIFS or COUNTIFS, "AND" is kind of built-in to the functions, since they're designed to consider criteria from multiple ranges. You can use AND if you want to consider two things about the same range in a SUMIFS or COUNTIFS at the same time - ex. =COUNTIFS(Date:Date, AND(MONTH(@cell) > 1, MONTH(@cell) < 8), Name:Name, Name@row)
CONTAINS is only needed if you are searching for a text value that may only be part of the text in the cell. Maybe you have a Name column where the name isn't always entered in lastname, firstname format. Then you'd use AND, CONTAINS, and @cell: ex. =COUNTIFS(Name:Name, AND(CONTAINS("Vitela", @cell), CONTAINS("Jordan", @cell)). This way if counts all cells that have both these names in them, in whatever order, comma or no comma.
@cell is only needed when you need to perform a function on values in your range so that COUNTIFS or SUMIFS act only on the results of the function. So if you want to count all the rows with a date in June, you need to use the MONTH function on the date values, before the COUNTIFS can determine if the date is in June. ex. =COUNTIFS(Date:Date, MONTH(@cell) = 6)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you Jeff Reisman !!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
