是,还是

Detrie Zacharias
Detrie Zacharias ✭✭✭✭
inFormulas and Functions

=IF(AND((([Asset Type]113 = "Success Story"), OR([Asset Type]113 = "Video - Client")), Ancestor113 = "2"), "1")


Trying to populate "1" where Asset type = Success Story OR Video - Client AND Ancestor = 2

THis throws an UNPARSABLE error. I think it may be related to my parenthesis

· Share on Facebook Share on Twitter

Best Answer

一个nswers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31)) I've been noodling with it but can't seem to get it to work, help would be greatly appreciated!","snippet":"Hi!!! Tried this and it is #UNPARSEABLE: =COUNTIFS({cost savings check}, 1), AND({OPENED}, >=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31)) I've been noodling with it but can't…","categoryID":322,"dateInserted":"2023-07-12T21:43:48+00:00","dateUpdated":null,"dateLastComment":"2023-07-12T23:58:49+00:00","insertUserID":159929,"insertUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":159929,"lastUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3378406357,"url":"https:\/\/community.smartsheet.com\/discussion\/107529\/cross-sheet-formula-countifs-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107529\/cross-sheet-formula-countifs-and","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107529,"commentID":384936,"name":"Re: Cross Sheet Formula, COUNTIFS + AND?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384936#Comment_384936","dateInserted":"2023-07-12T23:58:49+00:00","insertUserID":159929,"insertUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+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-12T23:56:07+00:00","dateAnswered":"2023-07-12T23:44:08+00:00","acceptedAnswers":[{"commentID":384931,"body":"

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


<\/p>

Are you trying to get a count if the cost savings check is checked and the date is between 1st January to 31st December 2023? The formula should be =COUNTIFS({cost savings check}, 1, {OPENED}, >=DATE(2023,1,1),{OPENED},<=DATE(2023,12,31))<\/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":107532,"type":"question","name":"Formulas","excerpt":"I am trying to write a formula that would enter a text based on the text in another column with both columns being a dropdown. For example if in column D the text is Pass I would like the formula to choose Complete in column A, if in column D the text is Under Review column A should state In Progress.","snippet":"I am trying to write a formula that would enter a text based on the text in another column with both columns being a dropdown. For example if in column D the text is Pass I would…","categoryID":322,"dateInserted":"2023-07-12T21:59:13+00:00","dateUpdated":null,"dateLastComment":"2023-07-12T22:07:51+00:00","insertUserID":163421,"insertUser":{"userID":163421,"name":"swightman","url":"https:\/\/community.smartsheet.com\/profile\/swightman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T22:07:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163421,"lastUser":{"userID":163421,"name":"swightman","url":"https:\/\/community.smartsheet.com\/profile\/swightman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T22:07:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":15,"score":null,"hot":3378400024,"url":"https:\/\/community.smartsheet.com\/discussion\/107532\/formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107532\/formulas","format":"Rich","lastPost":{"discussionID":107532,"commentID":384921,"name":"Re: Formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384921#Comment_384921","dateInserted":"2023-07-12T22:07:51+00:00","insertUserID":163421,"insertUser":{"userID":163421,"name":"swightman","url":"https:\/\/community.smartsheet.com\/profile\/swightman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T22:07:29+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-12T22:07:27+00:00","dateAnswered":"2023-07-12T22:02:59+00:00","acceptedAnswers":[{"commentID":384917,"body":"

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


<\/p>

You will be adding the formula in Column A and it would be something like this. =IF([Column D]@row=\"Pass\", \"Complete\", IF([Column D]@row=\"Under Review\", \"In Progress\"))<\/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":107492,"type":"question","name":"Countifs with and","excerpt":"I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY if the project status is New, In Progress or Approval Pending (Metric21, Metric22, and Metric23 in metrics sheet). My formula before factoring…","snippet":"I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY…","categoryID":322,"dateInserted":"2023-07-12T12:11:29+00:00","dateUpdated":"2023-07-12T12:11:44+00:00","dateLastComment":"2023-07-12T21:19:36+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T23:04:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163321,"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-12T21:24:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":43,"score":null,"hot":3378364265,"url":"https:\/\/community.smartsheet.com\/discussion\/107492\/countifs-with-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107492\/countifs-with-and","format":"Rich","lastPost":{"discussionID":107492,"commentID":384900,"name":"Re: Countifs with and","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384900#Comment_384900","dateInserted":"2023-07-12T21:19:36+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-12T21:24:21+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-12T19:31:29+00:00","dateAnswered":"2023-07-12T18:47:42+00:00","acceptedAnswers":[{"commentID":384862,"body":"

Try this:<\/p>

=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT(), {Status}, OR(@cell = Metric21, @cell = Metric22, @cell = Metric23)<\/strong>)<\/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

  • Cross Sheet Formula, COUNTIFS + AND?

    Hi!!! Tried this and it is #UNPARSEABLE: =COUNTIFS({cost savings check}, 1), AND({OPENED}, >=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31)) I've been noodling with it but can't seem to get it to work, help would be greatly appreciated!

  • Formulas

    I am trying to write a formula that would enter a text based on the text in another column with both columns being a dropdown. For example if in column D the text is Pass I would like the formula to choose Complete in column A, if in column D the text is Under Review column A should state In Progress.

  • Countifs with and

    I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY if the project status is New, In Progress or Approval Pending (Metric21, Metric22, and Metric23 in metrics sheet). My formula before factoring…