Formule générique
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Sommaire
Pour compter les lignes qui contiennent des valeurs spécifiques, vous pouvez utiliser une formule matricielle basée sur les fonctions MMULT, TRANSPOSE, COLUMN et SUM. Dans l'exemple illustré, la formule en G5 est:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
où data est la plage nommée B4: B12.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec la touche Ctrl + Entrée.
Explication
En travaillant de l'intérieur vers l'extérieur, les critères logiques utilisés dans cette formule sont:
--(data=90)
où data est la plage nommée B4: D12. Cela génère un résultat TRUE / FALSE pour chaque valeur dans les données, et le double négatif contraint les valeurs TRUE FALSE à 1 et 0 pour donner un tableau comme celui-ci:
(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)
Comme les données d'origine, ce tableau est de 9 lignes par 3 colonnes (9 x 3) et entre dans la fonction MMULT en tant que array1 .
Array2 est dérivé avec:
TRANSPOSE(COLUMN(data))
C'est la partie délicate et amusante de cette formule. La fonction COLUMN est utilisée simplement pour des raisons de commodité pour générer un tableau numérique de la bonne taille. Pour effectuer la multiplication de matrice avec MMULT, le nombre de colonnes dans array1 (3) doit être égal au nombre de lignes dans array2 .
COLUMN renvoie le tableau à 3 colonnes (2, 3, 4) et TRANSPOSE remplace ce tableau par le tableau à 3 lignes (2; 3; 4). MMULT s'exécute ensuite et renvoie un résultat de tableau 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Nous vérifions les entrées non nulles avec> 0 et contraignons à nouveau TRUE FALSE à 1 et 0 avec un double négatif pour obtenir un tableau final dans SUM:
=SUM((1;0;1;1;0;0;0;0;1))
Dans ce tableau final, un 1 représente une ligne où le test logique (données = 90) a renvoyé vrai. Le total renvoyé par SUM est un décompte de toutes les lignes contenant le nombre 90.
Littéral contient
Si vous avez besoin de vérifier des valeurs de texte spécifiques, en d'autres termes, de vérifier littéralement si les cellules contiennent certaines valeurs de texte, vous pouvez modifier la logique de la formule de cette page pour utiliser la fonction ISNUMBER et SEARCH. Par exemple, pour compter les cellules / lignes contenant «pomme», vous pouvez utiliser:
=ISNUMBER(SEARCH("apple",data))
Détails sur le fonctionnement de cette formule ici.
