Formule générique
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Sommaire
Pour compter les valeurs uniques avec une ou plusieurs conditions, vous pouvez utiliser une formule basée sur UNIQUE et FILTER. Dans l'exemple illustré, la formule en H7 est:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
qui renvoie 3, car il y a trois noms uniques dans B6: B15 associés au projet Omega.
Remarque: cette formule nécessite des formules de tableau dynamique, disponibles uniquement dans Excel 365. Avec une ancienne version d'Excel, vous pouvez utiliser des formules alternatives plus complexes.
Explication
Au cœur, cette formule utilise la fonction UNIQUE pour extraire des valeurs uniques et la fonction FILTER applique des critères.
Travaillant de l'intérieur vers l'extérieur, la fonction FILTER permet d'appliquer des critères et d'extraire uniquement les noms associés au projet "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Le résultat de FILTER est un tableau comme celui-ci:
("Jim";"Jim";"Carl";"Sue";"Carl")
Ensuite, la fonction UNIQUE est utilisée pour supprimer les doublons:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
ce qui donne un nouveau tableau comme celui-ci:
("Jim";"Carl";"Sue") // after UNIQUE
À ce stade, nous avons une liste unique de noms associés à Omega, et nous avons juste besoin de les compter. Pour les raisons expliquées ci-dessous, nous le faisons avec la fonction LEN et la fonction SUM. Pour clarifier les choses, nous allons d'abord réécrire la formule pour inclure la liste unique:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
La fonction LEN obtient la longueur de chaque élément de la liste et renvoie un tableau de longueurs:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Ensuite, nous vérifions si les longueurs sont supérieures à zéro:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Et utilisez un double négatif pour forcer les valeurs TRUE et FALSE à 1 et 0:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Enfin, nous additionnons les résultats avec la fonction SOMME:
=SUM((1;1;1)) // returns 3
Ce tableau est livré directement à la fonction COUNTA, qui renvoie un décompte final:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Notez que parce que nous vérifions la longueur de chaque élément retourné par UNIQUE, les cellules vides ou vides qui répondent aux critères sont ignorées. Cette formule est dynamique et recalculera immédiatement si les données source sont modifiées.
Compter unique avec plusieurs critères
Pour compter des valeurs uniques en fonction de plusieurs critères, vous pouvez étendre la logique «inclure» à l'intérieur de FILTER. Par exemple, pour compter les noms uniques pour le projet Omega en juin uniquement, utilisez:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Voici un exemple d'utilisation de la logique booléenne pour appliquer plusieurs conditions. L'approche est expliquée plus en détail ici.
Pour plus de détails, regardez cette vidéo de formation: Comment filtrer avec plusieurs critères.
COUNTA
Il est possible d'écrire une formule plus simple qui répond sur la fonction COUNTA. Cependant, une mise en garde importante est que COUNTA retournera 1 lorsqu'il n'y a pas de valeurs correspondantes. Cela est dû au fait que la fonction FILTER renvoie une erreur lorsqu'aucune donnée ne correspond aux critères et que cette erreur finit par être comptée par la fonction COUNTA. La formule de base COUNTA ressemble à ceci:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Encore une fois, cette formule renverra 1 lorsqu'il n'y a pas de données correspondantes. Il comprendra également des cellules vides répondant aux critères. La formule basée sur LEN et SUM est une meilleure option.
Pas de tableaux dynamiques
Si vous utilisez une ancienne version d'Excel sans prise en charge des tableaux dynamiques, vous pouvez utiliser une formule plus complexe. Pour une discussion plus générale sur les alternatives de tableau dynamique, voir: Alternatives aux formules de tableau dynamique.
