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
-
Hi @CaroCambre
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
-
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
-
Is still not working, the range 4 is containing multiple values, is that the reason?
-
Hi @CaroCambre
If you're looking into a multi-select column, you can use the HAS Function to 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".
-
Hi @CaroCambre
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:
-
Hi @CaroCambre
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! :-)
-
No problem! I'm glad we got there in the end. 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!