COUNTIFS+CONTAINS

CaroCambre · 05/02/22

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")

Genevieve P. · 05/03/22

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"))

Christian Graf · 05/02/22

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

CaroCambre · 05/02/22

Is still not working, the range 4 is containing multiple values, is that the reason?

Genevieve P. · 05/03/22

Hi@CaroCambre

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?

CaroCambre · 05/03/22

I get the message "Unparseable".

Genevieve P. · 05/03/22

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

CaroCambre · 05/03/22

Both give same "unparseable" message.

Hereby my screenshot:

Genevieve P. · 05/03/22

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"))

CaroCambre · 05/03/22

This is working! Thank you! :-)

Genevieve P. · 05/03/22

No problem! I'm glad we got there in the end.

COUNTIFS+CONTAINS

Best Answer

Answers

Help Article Resources

Categories