How to create a countif formula with OR
I am trying to create a formula across columns with different values in a cell. I have the formula for one value, Preliminary Draft. This works fine.
=COUNTIFS({Comments Log Range 1}, [Document Name]@row, {Comments Log Range 2}, "Preliminary Draft", {Comments Log Range 4}, "Open")
However, the value in range 2 can also be "Intermediate", or "Final".
This is what I tried:
=COUNTIFS(({Comments Log Range 1}, [Document Name]@row), OR({Comments Log Range 2}, "Intermediate", {Comments Log Range 2}, "Preliminary Draft", {Comments Log Range 2}, "Final"), {Comments Log Range 4}, "Open"))
Smartsheet says its UNPARSEABLE.
I am sure it's a simple solution, but I can't figure it out. Thanks.
Answers
-
Carson Penticuff ✭✭✭✭✭✭Try this one.
=COUNTIFS(({Comments Log Range 1}, [Document Name]@row), OR({Comments Log Range 2} = "Intermediate", {Comments Log Range 2} = "Preliminary Draft", {Comments Log Range 2} = "Final"), {Comments Log Range 4} = "Open"))
-
SJTA ✭
Still returns as unparseable
-
Carson Penticuff ✭✭✭✭✭✭
My apologies, I didn't notice your last condition was not inside the OR(). Try this one
=COUNTIFS({Comments Log Range 1}, [Document Name]@row, OR({Comments Log Range 2} = "Intermediate", {Comments Log Range 2} = "Preliminary Draft", {Comments Log Range 2} = "Final"), {Comments Log Range 4}, "Open")
-
SJTA ✭
Now the error is 'invalid operation'.
-
Carson Penticuff ✭✭✭✭✭✭
=COUNTIFS({Comments Log Range 1}, [Document Name]@row, {Comments Log Range 2}, OR(@cell = "Intermediate", @cell = "Preliminary Draft", @cell = "Final"), {Comments Log Range 4}, "Open")
-
CMoore ✭
Hi,
Thanks for the suggestion,@Carson Penticuff. However, I was unable to make the @cell work. Did it work for anyone else?
Thanks for any suggestions.
-
Carson Penticuff ✭✭✭✭✭✭
I have it working here:
Categories
@BKing<\/a> <\/p>
<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","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":109281,"type":"question","name":"Bridge and FedEx Module - Authentication Problem","excerpt":"I'm getting an authentication connect issue when trying to run the FedEx Module step in my Bridge job flow. I set up a FedEx Developer Account and created a test new project. When I run the Bridge job I get an error on on the Module run saying, \"failed to execute extension module : Authentication Failed\". I mapped the…","snippet":"I'm getting an authentication connect issue when trying to run the FedEx Module step in my Bridge job flow. I set up a FedEx Developer Account and created a test new project. When…","categoryID":320,"dateInserted":"2023-08-22T16:13:19+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T14:18:51+00:00","insertUserID":148855,"insertUser":{"userID":148855,"name":"cabbsman","url":"https:\/\/community.smartsheet.com\/profile\/cabbsman","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!88cq90f5xTs!8sDpd7uL9Ps!kj4X4avpgGN","dateLastActive":"2023-08-25T14:19:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-27T07:46:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":56,"score":null,"hot":3385695730,"url":"https:\/\/community.smartsheet.com\/discussion\/109281\/bridge-and-fedex-module-authentication-problem","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109281\/bridge-and-fedex-module-authentication-problem","format":"Rich","tagIDs":[227,369],"lastPost":{"discussionID":109281,"commentID":392578,"name":"Re: Bridge and FedEx Module - Authentication Problem","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392578#Comment_392578","dateInserted":"2023-08-25T14:18:51+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-27T07:46:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/G9UB21M8QHGL\/screenshot-28125-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot (125).png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T14:00:28+00:00","dateAnswered":"2023-08-25T08:23:46+00:00","acceptedAnswers":[{"commentID":392524,"body":"
![\"Screenshot](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/CXEN537MBHQV\/screenshot-2023-08-24-at-14-13-01.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
