Formule générique
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Sommaire
Pour compter les lignes visibles uniquement avec des critères, vous pouvez utiliser une formule plutôt complexe basée sur SUMPRODUCT, SUBTOTAL et OFFSET. Dans l'exemple illustré, la formule en C12 est:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Préface
La fonction SUBTOTAL peut facilement générer des sommes et des décomptes pour les lignes masquées et non masquées. Cependant, il n'est pas en mesure de gérer des critères tels que COUNTIF ou SUMIF sans aide. Une solution consiste à utiliser SUMPRODUCT pour appliquer à la fois la fonction SOUS-TOTAL (via OFFSET) et les critères. Les détails de cette approche sont décrits ci-dessous.
Explication
Au fond, cette formule fonctionne en mettant en place deux tableaux dans SUMPRODUCT. Le premier tableau applique des critères et le second tableau gère la visibilité:
=SUMPRODUCT(criteria*visibility)
Le critère est appliqué avec une partie de la formule:
=(C5:C8=C10)
Ce qui génère un tableau comme celui-ci:
(FALSE;TRUE;FALSE;TRUE)
Où VRAI signifie «répond aux critères». Notez que parce que nous utilisons la multiplication (*) sur ce tableau, les valeurs TRUE FALSE seront automatiquement converties en 1 et 0 par l'opération mathématique, nous nous retrouvons donc avec:
(0;1;0;1)
Le filtre de visibilité est appliqué en utilisant SOUS-TOTAL, avec le numéro de fonction 103.
SUBTOTAL est capable d'exclure les lignes cachées lors de l'exécution de calculs, nous pouvons donc l'utiliser dans ce cas pour générer un "filtre" pour exclure les lignes cachées à l'intérieur de SUMPRODUCT. Le problème est que SUBTOTAL renvoie un seul nombre, alors que nous avons besoin d'un tableau de résultats pour l'utiliser avec succès dans SUMPRODUCT. L'astuce consiste à utiliser OFFSET pour alimenter SUBTOTAL une référence par ligne, de sorte que OFFSET renverra un résultat par ligne.
Bien sûr, cela nécessite une autre astuce, qui consiste à donner à OFFSET un tableau contenant un nombre par ligne, en commençant par zéro. Nous faisons cela avec une expression construite sur la fonction ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
qui générera un tableau comme celui-ci:
(0;1;2;3)
En résumé, le deuxième tableau (qui gère la visibilité en utilisant SUBTOTAL), est généré comme ceci:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Et, enfin, nous avons:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Ce qui renvoie 1.
Critères multiples
Vous pouvez étendre la formule pour gérer plusieurs critères comme celui-ci:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Sommaire des résultats
Pour renvoyer une somme de valeurs au lieu d'un nombre, vous pouvez adapter la formule pour inclure une plage de somme:
=SUMPRODUCT(criteria*visibility*sumrange)
Les critères et les tableaux de visibilité fonctionnent de la même manière que celle expliquée ci-dessus, à l'exclusion des cellules qui ne sont pas visibles. Si vous avez besoin d'une correspondance partielle, vous pouvez construire une expression en utilisant ISNUMBER + SEARCH, comme expliqué ici.
