Formule générique
=SUMPRODUCT(COUNTIF(range1,range2))
Sommaire
Pour comparer deux plages et compter les correspondances totales entre les deux plages, vous pouvez utiliser une formule qui combine la fonction NB.SI avec la fonction SOMMEPROD. Dans l'exemple illustré, la formule de la cellule G7 est:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Remarque: cette formule ne se soucie pas de l'emplacement ou de l'ordre des articles dans chaque plage.
Explication
Supposons que vous ayez une liste "principale" d'un certain type, ainsi qu'une autre liste contenant certains des mêmes éléments. Vous voulez une formule qui compare les valeurs de la deuxième liste pour voir combien d'entre elles apparaissent dans la première liste. Vous ne vous souciez pas de l'ordre dans lequel les articles, vous voulez juste savoir combien d'articles de la liste 2 apparaissent dans la liste 1.
La fonction COUNTIF comptera les éléments dans une plage qui répond à vos critères. Normalement, vous donneriez à COUNTIF une plage comme A1: A10 et un critère simple comme "> 10". COUNTIF renverrait alors le nombre de cellules dans A1: A10 qui sont supérieures à 10.
Dans ce cas cependant, nous donnons à COUNTIF une fourchette de critères. Nous n'utilisons aucun opérateur logique, ce qui signifie que COUNTIF vérifiera l'équivalence (c'est-à-dire qu'il se comporte comme si nous utilisions l'opérateur equals (=)).
Parce que nous donnons à COUNTIF une plage (également appelée «tableau») qui contient 7 éléments, COUNTIF renverra un tableau de 7 éléments en conséquence. Chaque élément du tableau de résultats représente un décompte. Dans l'exemple, ce tableau renvoyé par COUNTIF ressemble à ceci:
(1; 1; 0; 1; 1; 0; 0)
Maintenant, nous devons simplement additionner les éléments de ce tableau, ce qui est un travail parfait pour SUMPRODUCT. La fonction SOMMEPROD est une fonction polyvalente qui gère les tableaux de manière native sans aucune syntaxe de tableau spéciale.
Si vous donnez à SUMPRODUCT deux tableaux ou plus, il multipliera les tableaux ensemble, additionnera les résultats et renverra ce nombre. Dans ce cas, nous ne donnons à SUMPRODUCT qu'un seul tableau, donc il résume simplement les éléments du tableau et renvoie 4 comme résultat.
Correspondance entre les lignes
Si vous souhaitez comparer deux plages ou colonnes et que vous souhaitez compter les correspondances au niveau de la ligne (c'est-à-dire ne compter que les correspondances lorsque le même élément apparaît à la même position), vous aurez besoin d'une formule différente.
