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"), "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,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"), "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,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
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/327867/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":43,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
![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}