Formula Help

Not the greatest at creating formulas so I am looking for some assistance on this one.

=IF(CONTAINS("0", [Product Categories selected]@row:[Keyword field filled]@row), "Complete", "Incomplete")

Here is what I am looking to do: If the four columns are all "O" then mark the status of Incomplete but if all four columns are all "1" then mark it as Complete. Also is there any way for the formula to be nested meaning every time import a new sheet the formula stars so that I can use this for other imported data?

image.png


Tags:

Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭
    Answer ✓

    Woops, sorry. I tried to write the formula without testing in Smartsheet first and missed a few parenthesis.

    =IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 0, "Incomplete", IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 4, "Complete", "In Progress"))

    Jason Tarpinian -Sevan Technology

    Smartsheet Platinum Partner

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭

    Since you are strictly using numbers, it might be easier to use SUM instead of CONTAINS:

    =IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=0, "Incomplete",IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=4,"Complete","In Progress"))

    This will check if the sum of all 4 is 0, then "Incomplete". If sum of all 4 = 4, "Complete". Else "In Progress".

    To make it less prone to human error (which I am a big fan of), you can use checkboxes as well for your 4 columns instead of numbers, so people don't accidentally type in something wrong. Then you could use this formula:

    =IF(COUNTIFS([Product Categories selected]@row:[Keyword field filled]@row, 1) = 0, "Incomplete", IF(COUNTIFS([Product Categories selected]@row:[Keyword field filled]@row, 1) = 4, "Complete", "In Progress"))

    Jason Tarpinian -Sevan Technology

    Smartsheet Platinum Partner

  • Manuel Lemus
    Manuel Lemus ✭✭✭✭

    Thank you for the help! When I added this formula below, it gave me an unparsable error...

    =IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=0, "Incomplete",IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=4,"Complete","In Progress"))

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭
    Answer ✓

    Woops, sorry. I tried to write the formula without testing in Smartsheet first and missed a few parenthesis.

    =IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 0, "Incomplete", IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 4, "Complete", "In Progress"))

    Jason Tarpinian -Sevan Technology

    Smartsheet Platinum Partner

  • Manuel Lemus
    Manuel Lemus ✭✭✭✭

    Thank you@Jason Tarpinian, that worked! Have a great night!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Does this work?<\/p>
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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":108205,"type":"question","name":"Either checkbox column to contribute to % complete","excerpt":"I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to…","snippet":"I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete…","categoryID":322,"dateInserted":"2023-07-27T17:53:55+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:21:53+00:00","insertUserID":164195,"insertUser":{"userID":164195,"name":"Rachel Kelly","url":"https:\/\/community.smartsheet.com\/profile\/Rachel%20Kelly","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T19:00:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T00:33:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3380967948,"url":"https:\/\/community.smartsheet.com\/discussion\/108205\/either-checkbox-column-to-contribute-to-complete","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108205\/either-checkbox-column-to-contribute-to-complete","format":"Rich","lastPost":{"discussionID":108205,"commentID":387648,"name":"Re: Either checkbox column to contribute to % complete","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387648#Comment_387648","dateInserted":"2023-07-27T19:21:53+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T00:33:57+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\/V4HP6H7XE3I9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T19:01:16+00:00","dateAnswered":"2023-07-27T18:58:57+00:00","acceptedAnswers":[{"commentID":387623,"body":"

Try this:<\/p>

=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N\/A]@row = 1), 1, 0), AVG(CHILDREN()))<\/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":108195,"type":"question","name":"COUNTIF Function Not Working Correctly","excerpt":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], \" \")","snippet":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this…","categoryID":322,"dateInserted":"2023-07-27T16:41:32+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164192,"lastUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3380962360,"url":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","format":"Rich","lastPost":{"discussionID":108195,"commentID":387638,"name":"Re: COUNTIF Function Not Working Correctly","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387638#Comment_387638","dateInserted":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+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-07-27T19:10:56+00:00","dateAnswered":"2023-07-27T16:47:29+00:00","acceptedAnswers":[{"commentID":387588,"body":"

Hi @ewitt19205<\/a> <\/p>

Try removing the space between the quotes. You are counting all cells with 1 space in the cell. Blanks are counted with the quotes together.<\/p>

When counting blanks though, its also good to use a COUNTIFS and compare the blank off another column that always has a value. Like Countifs when the Project name is not blank (<>\"//www.santa-greenland.com/community/discussion/74764/\") and the Account Dashboard is blank (\"//www.santa-greenland.com/community/discussion/74764/\").<\/p>

I hope that helps.<\/p>

Thanks,<\/p>

Matt<\/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":[]}],"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":[]}">

Trending in Formulas and Functions