Si vous utilisez des tailles de tableau variables dans une formule de Countifs dans Google Sheets, vous obtiendrez une erreur. C’est parce que la fonction ne prend pas en charge une telle utilisation.
Cela se produit généralement lorsque vous spécifiez des colonnes incompatibles dans les plages de critères 1 et 2 dans Countifs.
Avant de continuer, familiarisez-vous avec la syntaxe de la fonction. Elle est la suivante :
COUNTIFS(plage_critère1, critère1, [plage_critère2, …], [critère2, …])
Lorsque vous spécifiez plusieurs plages de critères, tous les tableaux/plages doivent avoir la même taille verticalement et horizontalement.
Cela signifie que le nombre de lignes et de colonnes doit correspondre ou être le même dans toutes les plages de critères/tableaux spécifiées.
Passons rapidement à un exemple pour le comprendre.
=countifs(A2:D10, »Pomme »,E2:E10, »Oui »)
Voici les quatre arguments utilisés :
- plage_critère1 – A2:D10
- critère1 – « Pomme »
- plage_critère2 – E2:E10
- critère2 – « Oui »
Comme vous pouvez le voir, les deux arguments de tableau ci-dessus (plage_critère1 et plage_critère2) pour COUNTIFS sont de tailles différentes.
Il y a quatre colonnes dans le premier tableau (argument 1) et une colonne dans le deuxième tableau (argument 3).
Dans l’exemple ci-dessus, je veux compter les « Pommes » dans la plage de colonnes A2:D10 si les valeurs dans E2:E10 sont « Oui ».
Des solutions de contournement pour utiliser des tailles de tableau variables dans Countifs sur Google Sheets
J’ai trois formules de contournement à vous proposer lorsque vous avez des tailles de tableau variables dans Countifs qui entraînent l’erreur #VALEUR! comme ci-dessus.
Je peux peut-être en trouver d’autres, mais les options ci-dessous seront suffisantes pour vous.
Option 1 – Tailles de tableau variables dans Countifs avec des plages de critères virtuelles
Nous pouvons adopter différentes approches pour utiliser des tailles de tableau variables dans Countifs sur Google Sheets.
Pour respecter la logique de la règle Countifs, nous pouvons créer une plage de critères virtuelle avec un nombre égal de colonnes.
Nous avons déjà adopté cette méthode avec SUMIF, c’est-à-dire pour retourner la somme des lignes ou des colonnes de matrice en utilisant SUMIF sur Google Sheets.
Je suis en train d’appliquer la même technique avec COUNTIFS ici.
Formule #1:
=countifs(A2:D10,"Pomme", ARRAYFORMULA(if(E2:E10="Oui",column(A2:D2)^0)),1)
Résultat : 9
La formule ci-dessus est la première option pour spécifier des colonnes incompatibles dans les plages de critères 1 et 2 dans Countifs.
Vous voulez en apprendre plus sur la formule COUNTIFS ci-dessus en détail ?
Explication de la formule
- plage_critère1 – A2:D10
- critère1 – « Pomme »
- plage_critère2 – ARRAYFORMULA(if(E2:E10= »Oui »,column(A2:D2)^0))
- critère2 – 1
Dans le Countifs (Formule #1), la plage_critère2 est une formule de tableau.
Elle renverrait un tableau avec un nombre de colonnes égal à plage_critère1.
Le nombre de lignes est déjà égal dans les deux tableaux, c’est-à-dire, dans plage_critère1 et 2.
Ce tableau virtuel aura le nombre 1 dans chaque colonne correspondant à « Oui » dans E2:E10. C’est pourquoi j’ai utilisé # 1 à la place de « Oui » dans le critère_2.
Voici deux autres options que vous pouvez envisager par rapport à la formule ci-dessus.
Option 2 – Basée sur le filtrage et les tests logiques
Sans correspondre virtuellement à des tailles de tableau variables dans Countifs, nous pouvons obtenir le résultat souhaité.
Vous trouverez ci-dessous deux combinaisons de formules de ce type, et j’ai également indiqué ma formule recommandée.
Formule #2 (Recommandée):
=countif(flatten(filter(A2:D10,E2:E10="Oui")),"Pomme")
Ici, j’ai utilisé Countif à la place de Countifs car nous n’avons qu’une seule colonne après manipulation des données avec FILTER et FLATTEN.
Ça fonctionne comme ça :
- Le filtre filtre les lignes de la plage A2:D10 si E2:E10 est « Oui ».
- Le Flatten regroupe les quatre colonnes en une seule colonne.
- Le Countif fait le reste (compte les « Pommes »).
Formule #3:
=sum(ArrayFormula(if((A2:D10="Pomme")*(E2:E10="Oui")=1,1,)))
La formule ci-dessus est une autre formule à prendre en compte lorsque nous avons des tailles de tableau variables à spécifier dans Countifs sur Google Sheets.
Comment cette formule renvoie-t-elle le nombre de valeurs conditionnellement à partir de plusieurs colonnes incompatibles ?
Voici comment :
Le test logique, la formule encadrée dans SUM, renvoie le # 1 là où les deux conditions correspondent. Veuillez consulter l’illustration ci-dessous.
La fonction SUM renvoie la somme de ces valeurs.
Si vous avez des doutes sur la formule de tableau que j’ai utilisée dans SUM, vous pouvez en apprendre davantage ici – Comment utiliser IF, AND, OR dans un tableau sur Google Sheets.
C’est simplement une autre façon de coder un test logique AND sous forme de tableau.
J’espère que vous avez pu comprendre les astuces ci-dessus.
Merci pour votre attention. Amusez-vous bien !
Ressources des fonctions Countif | Countifs
- Countifs avec plusieurs critères dans la même plage sur Google Sheets.
- Countif dans un tableau sur Google Sheets en utilisant la combinaison VLOOKUP et QUERY.
- Comment utiliser COUNTIF avec UNIQUE sur Google Sheets.
- Countif | Countifs en excluant les lignes masquées sur Google Sheets.
- Google Sheets : Countifs avec différent de dans des plages infinies.
- COUNTIFS dans une plage horaire sur Google Sheets [Colonne de date et d’heure].
- COUNTIF pour compter par mois dans une plage de dates sur Google Sheets.
- Pas vide comme condition dans Countifs sur Google Sheets.
