COUNTIFS+CONTAINS
I get the message UNPARASABLE, what am I doing wrong?
I want to count the non blank fields in rage 1, when in range 4 I have a certain value AND is the range 7 = OPEN value.
=COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1};<>"";[{Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}; CONTAINS("AG",@cell)];{Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7};="OPEN")
Best Answer
-
Genevieve P. Employee Admin
In your other formulas, do you use ; or , ?
If you use ; then I think I can see the issue! My apologies, I left a comma in the HAS function. We'll want to swap that out for ; as well:
=COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}; <>""; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}; "OPEN"; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}; HAS(@cell; "AG"))
Answers
-
Christian Graf ✭✭✭✭
Hello@CaroCambre
You are using semicolons instead of commas to separate your conditions, the syntax is incorrect so the formula will not work. You also suddenly added a random CONTAINS() for some reason. You can keep going and specify which range and criteria you are looking for using the COUNTIFS() function without using CONTAINS().
=COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}, <>"", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}, ="OPEN", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}, ="AG")
Hope this Helps
-
Genevieve P. Employee Admin
If you're looking into a multi-select column, you can use theHAS Functionto search for one value:
=COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}, <>"", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}, "OPEN", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4},HAS(@cell, "AG"))
If this hasn't worked, can you explain if you're getting an incorrect result or a formula error?
-
I get the message "Unparseable".
-
Genevieve P. Employee Admin
What are your language settings? Depending on your language, you'll want to use ; or ,
In your original formula you had ;
=COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}; <>""; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}; "OPEN"; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4};HAS(@cell, "AG"))
If neither ; or , works, please post a screen capture of the formula open in your sheet, but block out sensitive data.
Thanks!
Genevieve
-
Both give same "unparseable" message.
Hereby my screenshot:
-
Genevieve P. Employee Admin
In your other formulas, do you use ; or , ?
If you use ; then I think I can see the issue! My apologies, I left a comma in the HAS function. We'll want to swap that out for ; as well:
=COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}; <>""; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}; "OPEN"; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}; HAS(@cell; "AG"))
-
This is working! Thank you! :-)
-
Genevieve P. Employee Admin
No problem! I'm glad we got there in the end.
Help Article Resources
Categories
You would drop it in in place of <\/p>
[Date Column]@row + 10<\/p>
<\/p>
Everything else should stay the same.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-06 05:54:52","updateUser":{"userID":141269,"name":"Fialko66","url":"https:\/\/community.smartsheet.com\/profile\/Fialko66","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-06T05:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":98409,"type":"question","name":"I have a formula that does not work now because of the new year.","excerpt":"This formula has been working just fine until the month of December and now returns #invalid Data Type\" I assume it is because it is looking for dates within this calendar year and not next calendar year? Does anyone have any suggestions on how I can fix this problem? The formula looks at the Determination Date and then…","categoryID":322,"dateInserted":"2022-12-05T16:45:56+00:00","dateUpdated":null,"dateLastComment":"2022-12-05T19:43:51+00:00","insertUserID":126337,"insertUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":126337,"lastUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":28,"score":null,"hot":3340531187,"url":"https:\/\/community.smartsheet.com\/discussion\/98409\/i-have-a-formula-that-does-not-work-now-because-of-the-new-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98409\/i-have-a-formula-that-does-not-work-now-because-of-the-new-year","format":"Rich","lastPost":{"discussionID":98409,"commentID":353207,"name":"Re: I have a formula that does not work now because of the new year.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353207#Comment_353207","dateInserted":"2022-12-05T19:43:51+00:00","insertUserID":126337,"insertUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-05T19:15:44+00:00","dateAnswered":"2022-12-05T19:11:41+00:00","acceptedAnswers":[{"commentID":353196,"body":"
@Christopher Flemings<\/a> <\/p>
=IF(COUNTIFS([Date1]@row:[Date6]@row, ISDATE(@cell))=6, 1)<\/p>