Formula to value cell based on 3 other cells
I'm struggling how to write a formula for a Job Status Column, which is based on whether or not 4 individual date columns have a value in them. If date Column 1 has a date, then the Job Status Column should automatically show as "25% Complete". If date Column 2 has a date, then the Job Status Column should automatically show as "50% Complete". If date Column 3 has a date, then the Job Status Column should automatically show as "75% Complete." If date Column 4 has a date, then the Job Status Column should automatically show as "100% Complete." I've been trying all sorts of IF, OR, and ISDATE functions but cannot seem to get anything to work.
Best Answers
-
Paul Newcome ✭✭✭✭✭✭
Ah. Ok. Since the date columns are not next to each other, we can tweak the nested IF you have above...
=IF(ISDATE([Step 4 Date]@row), "100% Complete", IF(ISDATE([Step 3 Date]@row),"75% Complete", IF(ISDATE([Step 2 Date]@row),"50% Complete", IF(ISDATE([Step 1 Date]@row),"25% Complete"))))
-
Paul Newcome ✭✭✭✭✭✭
Happy to help.️
One way to remember it is that IF # 2 is the third argument in the first IF.
=IF(this is true, output this, otherwise that)
You would drop the next IF into the "otherwise that" portion keeping the parenthesis in place.
Here is something I do when building complex formulas that helps me keep parenthesis from getting too jumbled up. Put each portion in its own cell and use cell references to link them together. Once you have each portion working, you can replace the cell references with the formulas in those cells.
Example:
If "Primary@row" = 1, then I want to output "One", otherwise I want to say that if "Primary@row" = 2 then output "Two".
So in Column A I have
=IF(Primary@row = 1, "One")
In Column B I have
=IF(Primary@row = 2, "Two")
I know that if Primary@row does not equal 1, then I want to run the formula in Column B. So now I have:
=IF(Primary@row = 1, "One", [Column B]@row)
Then I can take the IF statement in Column B and drop it into the formula where it says "[Column B]@row".
=IF(Primary@row = 1, "One", [Column B]@row)
=IF(Primary@row = 1, "One", IF(Primary@row = 2, "Two"))
As you can see... When you nest IF statements like this, because the next one goes into the 3rd portion of the previous one, they all get closed out at the end. So if you have 3 nested IF statements, then they should all be left open until you finish it off with 3 closing parenthesis.
Answers
-
Paul Newcome ✭✭✭✭✭✭
Try this...
=COUNTIFS([Column 1]@row:[Column 4]@row, @cell <> "") / 4
-
我应该澄清我的日期列not next to each other. Here's what I've been trying to work with but keep getting a #UNPARSEABLE error. Note that I have ordered the formula from end to start as I understand that Smartsheet will read/apply the formula from left to right. Example: if the order has a date at Step 3 I want the field to be valued "75% Complete"; however, if the order has a date only at Step 1 I want the field to be valued "25% Complete".
=IF((ISDATE([Step 4 Date]@row)), "100% Complete")), IF((ISDATE([Step 3 Date]@row)),"75% Complete")), IF((ISDATE([Step 2 Date]@row)),"50% Complete")), IF((ISDATE([Step 1 Date]@row)),"25% Complete"))
-
Paul Newcome ✭✭✭✭✭✭
Ah. Ok. Since the date columns are not next to each other, we can tweak the nested IF you have above...
=IF(ISDATE([Step 4 Date]@row), "100% Complete", IF(ISDATE([Step 3 Date]@row),"75% Complete", IF(ISDATE([Step 2 Date]@row),"50% Complete", IF(ISDATE([Step 1 Date]@row),"25% Complete"))))
-
Paul, I wanted to say thank you for the correction to my nested IF formula (darn parenthesis placement always confound me in formulas). It worked perfectly.
-
Paul Newcome ✭✭✭✭✭✭
Happy to help.️
One way to remember it is that IF # 2 is the third argument in the first IF.
=IF(this is true, output this, otherwise that)
You would drop the next IF into the "otherwise that" portion keeping the parenthesis in place.
Here is something I do when building complex formulas that helps me keep parenthesis from getting too jumbled up. Put each portion in its own cell and use cell references to link them together. Once you have each portion working, you can replace the cell references with the formulas in those cells.
Example:
If "Primary@row" = 1, then I want to output "One", otherwise I want to say that if "Primary@row" = 2 then output "Two".
So in Column A I have
=IF(Primary@row = 1, "One")
In Column B I have
=IF(Primary@row = 2, "Two")
I know that if Primary@row does not equal 1, then I want to run the formula in Column B. So now I have:
=IF(Primary@row = 1, "One", [Column B]@row)
Then I can take the IF statement in Column B and drop it into the formula where it says "[Column B]@row".
=IF(Primary@row = 1, "One", [Column B]@row)
=IF(Primary@row = 1, "One", IF(Primary@row = 2, "Two"))
As you can see... When you nest IF statements like this, because the next one goes into the 3rd portion of the previous one, they all get closed out at the end. So if you have 3 nested IF statements, then they should all be left open until you finish it off with 3 closing parenthesis.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+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-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+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-27T02:16:35+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-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+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-26T17:06:33+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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"