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
-
Jeff Reisman ✭✭✭✭✭✭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"),“噢,不!”,“哦,好!”),需要的东西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,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接: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
-
Jeff Reisman ✭✭✭✭✭✭
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,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接: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?
-
Jeff Reisman ✭✭✭✭✭✭
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"),“噢,不!”,“哦,好!”),需要的东西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,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接: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 youJeff Reisman!!
Help Article Resources
Categories
The formula below will get what you need. With Smartsheet, you have to convert % to decimal values in formulas. <\/p>
=IF([Participation %]@row = 0, \"Not Participating\", IF([Participation %]@row > 0.8, \"On Track\", IF(AND([Participation %]@row < 0.81, [Participation %]@row > 0), \"Needs Improvement\", \"Blank\")))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108795,"type":"question","name":"Error Using Max Function","excerpt":"Having an issue with the Max formula. Appreciate any advice the community can provide. This formula is giving me an Invalid Column Error: =MAX([End Date]195:[End Date]220) However, this one works fine so I don't see why there would be an invalid column error on the first formula. =NETWORKDAYS([End Date]207, [Start…","snippet":"Having an issue with the Max formula. Appreciate any advice the community can provide. This formula is giving me an Invalid Column Error: =MAX([End Date]195:[End Date]220)…","categoryID":322,"dateInserted":"2023-08-10T15:42:27+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T19:20:09+00:00","insertUserID":147716,"insertUser":{"userID":147716,"name":"Laura Krylov ASGLLC","url":"https:\/\/community.smartsheet.com\/profile\/Laura%20Krylov%20ASGLLC","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJzqP5l6RPvTnPZ_FElh_Gd3nK2OrCoGmkfTmo11=s96-c","dateLastActive":"2023-08-10T19:05:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-11T03:23:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":30,"score":null,"hot":3383379756,"url":"https:\/\/community.smartsheet.com\/discussion\/108795\/error-using-max-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108795\/error-using-max-function","format":"Rich","lastPost":{"discussionID":108795,"commentID":390113,"name":"Re: Error Using Max Function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390113#Comment_390113","dateInserted":"2023-08-10T19:20:09+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-11T03:23:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T17:50:34+00:00","dateAnswered":"2023-08-10T15:59:34+00:00","acceptedAnswers":[{"commentID":390050,"body":"
Is the column you are inserting the formula into formatted as a date column?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108786,"type":"question","name":"Can't Save Field Logic Updates in Dynamic View","excerpt":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It asks the user to select a Category and to deter using Issues\/Miscellaneous, I have added a dropdown column in the sheet for the user to confirm…","snippet":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It…","categoryID":322,"dateInserted":"2023-08-10T14:14:23+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:42:12+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T15:49:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T19:35:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3383364395,"url":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","format":"Rich","tagIDs":[443],"lastPost":{"discussionID":108786,"commentID":390063,"name":"Re: Can't Save Field Logic Updates in Dynamic View","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390063#Comment_390063","dateInserted":"2023-08-10T16:42:12+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T19:35:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8C0H47JEM9MT\/fieldlogic.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"fieldlogic.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T15:49:43+00:00","dateAnswered":"2023-08-10T14:51:23+00:00","acceptedAnswers":[{"commentID":390035,"body":"
Hi @BristolCVN<\/a> <\/p> I hope you're well and safe!<\/p> You must add\/change the Rule #1 name.<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":443,"urlcode":"dynamic-view","name":"Dynamic View"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
![https:\/\/us.v-cdn.net\/6031209\/uploads\/MTSYTS5VXCI6\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":\"Here's the points total formula:\\n=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)))\\n\\nI also have another column call 8 or 10 that is shown below that I can trigger automation off of...\\n=IF(PointsTotal@row >= 8, \\\"8\\\", IF(PointsTotal@row >= 10, \\\"10\\\", \\\"1\\\"))\\n\\nIf 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?\\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MTSYTS5VXCI6\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/MTSYTS5VXCI6\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-04-29T13:56:31+00:00","insertUser":{"userID":131859,"name":"Jennifer Lindquist","url":"https:\/\/community.smartsheet.com\/profile\/Jennifer%20Lindquist","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pfnMadzYsL4!bHfOG_jEQrw!uHr-MuZ_G7c","dateLastActive":"2022-04-29T16:38:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327871#Comment_327871","embedType":"quote"}">](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/MTSYTS5VXCI6\/image.png\"){kind=link}