Formulas

TODAY(-30),…","categoryID":322,"dateInserted":"2018-04-30T17:28:39+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-01T18:44:47+00:00","insertUserID":1016,"insertUser":{"userID":1016,"name":"Elvin Montanez","url":"https:\/\/community.smartsheet.com\/profile\/Elvin%20Montanez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-04-30T17:16:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":23666,"lastUser":{"userID":23666,"name":"Chris McKay","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20McKay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-09\/d5\/d2\/nd5d2693be28ccbd24f978b00c5d1db1d.JPG","dateLastActive":"2018-08-14T22:59:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":134,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/24496\/formula-to-count-rows-in-a-date-range-across-columns-and-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24496\/formula-to-count-rows-in-a-date-range-across-columns-and-status","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":234,"urlcode":"health-care","name":"Healthcare"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":271,"urlcode":"services","name":"Services"},{"tagID":281,"urlcode":"support","name":"Support"}],"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}]},{"discussionID":24526,"type":"discussion","name":"IF Formula for a sheet","excerpt":"I am trying to get this formula to work correctly. I believe I am close however it is not working correctly. I have attached a screen shot. ","categoryID":322,"dateInserted":"2018-05-01T09:56:06+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-01T11:22:46+00:00","insertUserID":46491,"insertUser":{"userID":46491,"name":"justin46491","url":"https:\/\/community.smartsheet.com\/profile\/justin46491","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-27T09:55:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":46131,"lastUser":{"userID":46131,"name":"Bryan Sampson","url":"https:\/\/community.smartsheet.com\/profile\/Bryan%20Sampson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-05-15T13:13:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":51,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/24526\/if-formula-for-a-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24526\/if-formula-for-a-sheet","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-05\/9e\/e0\/9ee0adcbe918d32c8356aff9dba51d38.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Snapshot.PNG"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":24361,"type":"discussion","name":"Nested IF using additional AND logical functions","excerpt":"I am struggling with creation of formula for defining tasks status (Green, Yellow, Red). Getting the UNPEARSEABLE or INCORRECT ARRANGMENT SET error messages. My formula is right now as follows: =IF(ISBLANK([RESPONSE DATE]88); \"Gray\", IF([RESPONSE DATE]88 <= [ORIGINAL DUE DATE]88; \"Green\", IF(AND([RESPONSE DATE]88 >…","categoryID":322,"dateInserted":"2018-04-26T06:44:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-30T11:53:21+00:00","insertUserID":46111,"insertUser":{"userID":46111,"name":"w.bawaked46111","url":"https:\/\/community.smartsheet.com\/profile\/w.bawaked46111","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-04-28T17:19:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-08-23T14:56:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":63,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/24361\/nested-if-using-additional-and-logical-functions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24361\/nested-if-using-additional-and-logical-functions","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":218,"urlcode":"construction","name":"Construction"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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}]},{"discussionID":24441,"type":"discussion","name":"Cross-sheet \"countif\" formula with three criteria","excerpt":"I am struggling with another Countif formula. I have a working formula in one column that counts proposals filtered by month and year. I am trying to modify it to add another criteria so I can count proposals filtered by the month year that also have the sales stage of \"Asset Management Plan.\" The formula doesn't give me…","categoryID":322,"dateInserted":"2018-04-27T14:58:21+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-27T15:14:22+00:00","insertUserID":12177,"insertUser":{"userID":12177,"name":"Kelly Ospina","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Ospina","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xejAscrj8FY!5OjBOSryaaY!xV-7tumcaRX","dateLastActive":"2023-08-25T14:01:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-08-23T14:56:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":181,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/24441\/cross-sheet-countif-formula-with-three-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24441\/cross-sheet-countif-formula-with-three-criteria","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

IF ERROR Formula when trying exclue zeros from average
I am creating a sheet for PM weekly time tracking. The top parent row (blue) is YTD roll up of total hours, as well as a column for average weekly hours (<---- this is my issue but I'll to that shortly). I have a child row (green) for each quarter, and then grandchildren rows (white) for each work week. The child rows also…
Populate Start/End Date based on Status change
I use the Card View for quickly creating tasks and tracking their status/state. I'm trying to see if it's possible to take this further and populate the Start Date and End Date fields when the task first enters a specific state. See screenshot attached.
净2日期和时间之间的时间
Hi folks, Ok, this is a tricky one: I'm trying to set something that was requested by many people on this community forum - a NETWORKDAYS that can account for TIME as well. In other words, the amount of working days/hours between to periods (that accounts for. There is this amazing…
Need help with date formula
I am trying to create a line chart widget. The chart would display the date an incident or incidents were reported on the X axis. The y axis would display the number of incidents scale. The widget would report the incidents per the last 90 days. Only the date an incident(s) was reported would display. in the chart.…
Adding a Title to a COUNTIF formula
I am looking for guidance as I feel like I am missing something really simple. I would like to be able to have a title(label) included in my COUNTIF statement. This is the total count formula I have for the parent row: ="May 2018" + " (" + COUNT(CHILDREN()) + ")" This formula displays in the cell as May 2018 (3) ~~~~~~ I…
Sum total number of cartons by date
I have vendors fill out a form every time they have a shipment leaving their building. I want to create a widget on the dashboard for our leaders that show the total number of cartons leaving our vendors each day. (Bar Graph) But first, how do I create a report or formula to sum cartons by date, without me providing the…
Formula to count rows in a date range across columns and status
I am getting suck trying to create a =COUNTIF formula from a sheet that has my date column, and several other qualifying status columns.Any advise would be greatly appreciated. Here is where I am at so far and I am getting an #INVALID Data Type error. =COUNTIFS({Sheet1 Range 3}, AND(@cell <= TODAY(), @cell > TODAY(-30),…
IF Formula for a sheet
I am trying to get this formula to work correctly. I believe I am close however it is not working correctly. I have attached a screen shot.
Nested IF using additional AND logical functions
I am struggling with creation of formula for defining tasks status (Green, Yellow, Red). Getting the UNPEARSEABLE or INCORRECT ARRANGMENT SET error messages. My formula is right now as follows: =IF(ISBLANK([RESPONSE DATE]88); "Gray", IF([RESPONSE DATE]88 <= [ORIGINAL DUE DATE]88; "Green", IF(AND([RESPONSE DATE]88 >…
Cross-sheet "countif" formula with three criteria
I am struggling with another Countif formula. I have a working formula in one column that counts proposals filtered by month and year. I am trying to modify it to add another criteria so I can count proposals filtered by the month year that also have the sales stage of "Asset Management Plan." The formula doesn't give me…

Formulas

Discussion List