Formule générique
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Sommaire
Pour compter les cellules qui ne correspondent à aucune des nombreuses choses, vous pouvez utiliser une formule basée sur les fonctions MATCH, ISNA et SUMPRODUCT. Dans l'exemple illustré, la formule de la cellule F5 est:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
où "données" est la plage nommée B5: B16 et "exclure" est la plage nommée D5: D7.
Explication
Tout d'abord, un peu de contexte. Normalement, si vous n'avez que quelques choses que vous ne voulez pas compter, vous pouvez utiliser COUNTIFS comme ceci:
=COUNTIFS(range,"apple",range,"orange")
Mais cela ne s'adapte pas très bien si vous avez une liste de nombreuses choses, car vous devrez ajouter une paire plage / critères supplémentaire pour chaque chose que vous ne voulez pas compter. Il serait beaucoup plus facile de construire une liste et de transmettre une référence à cette liste dans le cadre des critères. C'est exactement ce que fait la formule sur cette page.
Au fond, cette formule utilise la fonction MATCH pour trouver des cellules non égales à "a", "b" ou "c" avec cette expression:
MATCH(data,exclude,0)
Notez que la valeur de recherche et le tableau de recherche sont "inversés" par rapport à la configuration normale - nous fournissons toutes les valeurs de la plage nommée "data" comme valeurs de recherche, et donnons toutes les valeurs que nous voulons exclure de la plage nommée "exclure". Parce que nous donnons à MATCH plus d'une valeur de recherche, nous obtenons plus d'un résultat dans un tableau comme celui-ci:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Essentiellement, MATCH nous donne la position des valeurs correspondantes sous forme de nombre et renvoie # N / A pour toutes les autres valeurs.
Les résultats # N / A sont ceux qui nous intéressent, car ils représentent des valeurs non égales à "a", "b" ou "c". En conséquence, nous utilisons ISNA pour forcer ces valeurs à TRUE et les nombres à FALSE:
ISNA(MATCH(data,exclude,0)
Ensuite, nous utilisons un double négatif pour forcer TRUE à 1 et FALSE à zéro. Le tableau résultant, à l'intérieur de SUMPRODUCT, ressemble à ceci:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Avec un seul tableau à traiter, SUMPRODUCT additionne et renvoie un résultat final, 4.
Remarque: l'utilisation de SUMPRODUCT au lieu de SUM évite d'avoir à utiliser Ctrl + Maj + Entrée.
Nombre moins correspondance
Une autre façon de compter les cellules qui ne correspondent à aucune de plusieurs choses consiste à compter toutes les valeurs et à soustraire les correspondances. Vous pouvez le faire avec une formule comme celle-ci:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Ici, COUNTA renvoie un nombre de toutes les cellules non vides. La fonction NB.SI, étant donné la plage nommée "exclure" renverra trois comptes, un pour chaque élément de la liste. SUMPRODUCT additionne le total et ce nombre est soustrait du nombre de toutes les cellules non vides. Le résultat final est le nombre de cellules qui ne correspondent pas aux valeurs «exclure».
Le littéral contient une logique de type
La formule de cette page compte avec la logique «égal à». Si vous devez compter des cellules qui ne contiennent pas beaucoup de chaînes, où contient signifie qu'une chaîne peut apparaître n'importe où dans une cellule, vous aurez besoin d'une formule plus complexe.
