COUNTIF problem with OR / " INVALID DATA TYPE "

ALAIN-STEPHANE

Hi all , 

Can you tell me what's wrong with this formula : 

COUNTIFS({Catégorie_courrier}; ="Permis"; OR({COURRIER_Réponse}; ="Oui"; {Traitement_demande}; ="Oui"))

It returns the error message : " INVALID DATA TYPE " .

As {Catégorie_courrier} , {COURRIER_Réponse} and  {Traitement_demande} are 3 ranges each corresponding to a drop-down list column . 

The corresponding columns are " Catégorie " , " Réponse" and " Traitement " and I want to count the number of rows with : " Catégorie " column taking the value of " Permis " , and the " Réponse " column taking the value of " Oui " or the " traitement " column taking the value of " Oui " . 

Can someone help me ? 

sean59916

Hello - 

I took a look at this and whilst not a direct path, I have a workaround to give you the answer to count the number of rows with : " Catégorie " column taking the value of " Permis " , and the " Réponse " column taking the value of " Oui " or the " traitement " column taking the value of " Oui "

The problem with using COUNTIFS is that it's only going to return results where all conditions are met.

This is how i approached it - i'm sure there may be other ways (and someone will point it out!!)

1. Create a helper/reference column in the underlying sheet - which is simply a concatenation of Réponse + Traitement. Example: =Réponse1 + Traitement1

2. I then created for illustration purposes the combination of COUNTIFS - Réponse, Traitement & also the "OuiOui"!! (They have already been counted and will need to be subtracted)

 

The combined formula to count based on your conditions is:

=COUNTIFS({Catégorie_courrier}, "Permis", {COURRIER_Réponse}, "Oui") + COUNTIFS({Catégorie_courrier}, "Permis", {Traitement_demande}, "Oui") - COUNTIFS({Catégorie_courrier}, "Permis", {Helper}, "OuiOui")

 

Refer to screenshots. I hope this helps and apologize in advance for any French translation errors!

Regards, 

Sean

Paul Newcome

You could actually just use a COUNTIFS function to find the count for each column type that you are looking for in the OR function and add them together.

 

=COUNTIFS({Catégorie_courrier}; ="Permis"; {COURRIER_Réponse}; ="Oui") + COUNTIFS({Catégorie_courrier}; ="Permis"; {Traitement_demande}; ="Oui")

ALAIN-STEPHANE

Nice SEAN , 

The helper column is a good idea . 

I will try it .

Thanks !

sean59916

Thanks for the suggestion Paul and I'm sure that there is another way to skin this cat, but, I'm getting an INVALID DATA TYPE when using the formula above

=COUNTIFS({Catégorie_courrier}; ="Permis"; {COURRIER_Réponse}; ="Oui") + COUNTIFS({Catégorie_courrier}; ="Permis"; {Traitement_demande}; ="Oui")

Maybe a silly question? Is the ; used in place of the , due to the keyboard settings/language?

Sean

ALAIN-STEPHANE

Great workaroud PAUL .

Thank you very much .

Paul Newcome

Different countries use different separators. Some use commas and some use semicolons. I am not sure if it a language setting or a regional setting or what. I just know that there is a difference. The only reason I know that this particular use is semicolon is because of the formula in the original post using them. I am assuming that the OP is using the correct punctuation for their particular area/language.

 

You will see a lot of Andree's posts referencing this (all of the more recent ones). He may have more insight as to the differences.

Paul Newcome

Happy to help! 

sean59916

yep, understand the separators. I did see that mentioned in Andrée's posts.

I don't understand how the formula works though if you have the value of Oui in both columns as it double counts the row? Am I missing something here?

Sean

Paul Newcome

You aren't missing anything. You are correct that it would double count. To resolve that, we can use your idea of a helper column and just tweak/simplify it a bit.

 

Create the helper column as in your post to join the two response columns. But then we can vastly simplify the overall formula to get the count to something like this...

 

=COUNTIFS({Catégorie_courrier}, "Permis", {Helper}, FIND("oui", LOWER(@cell)) > 0)

 

The bold portion is what allows us to simplify. We look at the helper column only. If "oui" is found anywhere within a cell after it has been converted to all lowercase (standardization of data helps with accuracy), it will count 1. So even if the helper cell is "OuiOui", the FIND function will return the number 1 because it has found "oui" in the first position of the cell. 1 is greater than 0, so it counts.

 

Thanks for pointing out the double row counting issue. 

sean59916

So true, I created the helper column, but didn't optimize the formula  The new formula is even better! 

The power of the community!!

Thanks Paul.

Paul Newcome

No worries. Thanks for catching that double count issue.

ALAIN-STEPHANE

Thank you very much PAUL & SEAN , 

Your comments were very rewarding .

Regards .

Paul Newcome

Happy to help!