Compter selon plusieurs conditions avec COUNTIFS

COUNTIFS compte le nombre de lignes qui satisfont plusieurs conditions simultanément. Alors que COUNTIF répond à la question « combien de ventes ont été réalisées dans l'Est ? », COUNTIFS répond à la question « combien de ventes dans l'Est ont été réalisées en janvier ? »

La fonction n'additionne ni ne calcule aucune moyenne — elle dénombre simplement les lignes qui satisfont tous les critères. Elle est donc idéale pour des questions comme « combien de commandes de plus de 100 $ provenaient de nouveaux clients ? »

Apprenons sa structure et mettons-la en pratique.

COUNTIFS ne comporte pas de plage de somme, car la fonction n'additionne pas de valeurs : elle a uniquement besoin des plages à vérifier et des critères auxquels les comparer.

• criteria_range1, criteria1 — le premier critère
• criteria_range2, criteria2 — le deuxième critère
• ... et ainsi de suite, jusqu'à 127 paires

Chaque argument forme une paire claire plage, critère. Le résultat est un seul nombre entier : le nombre de lignes pour lesquelles toutes les conditions sont vraies.

En reprenant le même tableau de ventes — la colonne A contient la Région, la colonne B le Mois et la colonne C le Montant — comptons combien de ventes concernent la région Est et le mois de janvier.

Il n'y a pas de colonne de montant dans cette formule, car nous comptons des lignes et non une somme d'argent. Chaque paire restreint davantage le décompte.

Tout comme SUMIFS, COUNTIFS accepte les opérateurs de comparaison entre guillemets pour les critères numériques.

• ">100" — valeurs supérieures à 100
• ">=18" — 18 ou plus
• "<>" — toute cellule non vide

Nous comptons ici les lignes de la région Est dont le montant est supérieur à 100. Notez que la colonne de montant apparaît comme une plage de critères et non comme une plage de somme.

Une demande fréquente consiste à compter les valeurs qui se trouvent entre deux nombres. Vous ne pouvez pas le faire avec un seul critère : vous utilisez la même colonne deux fois, avec une borne inférieure et une borne supérieure.

Cela compte les montants d'au moins 50 et d'au plus 200. Les deux conditions portent sur la colonne C et, comme COUNTIFS utilise une logique AND, seules les lignes situées dans cet intervalle sont comptées.

Pour rendre les bornes dynamiques, stockez-les dans des cellules et associez l'opérateur avec &. Placez la valeur minimale dans F1 et la valeur maximale dans F2.

">="&F1 construit le critère supérieur ou égal à F1. L'utilisateur peut ainsi saisir de nouvelles limites et voir instantanément le décompte mis à jour sans modifier la formule elle-même.

Deux critères particuliers sont utiles pour contrôler la qualité des données :

• "<>" compte les cellules qui ne sont pas vides
• "=" ou "" compte les cellules qui sont vides

Cette formule compte le nombre de lignes de la région Est dont le commercial est renseigné (colonne D non vide) — un moyen rapide de repérer les données manquantes.

Les critères textuels acceptent les mêmes caractères génériques que SUMIFS. Utilisez * pour une suite quelconque de caractères et ? pour un seul caractère.

Cela compte chaque ligne dont le nom du commercial commence par la lettre M — Maria, Mike et Mohammed correspondent tous. Les caractères génériques ne s'appliquent qu'au texte ; ils sont ignorés avec les nombres.

La même règle de dimension que pour SUMIFS s'applique : chaque plage de critères doit avoir la même forme. Si une plage est A2:A100 et une autre B2:B50, COUNTIFS renvoie une erreur #VALUE!.

Les références à des colonnes entières comme A:A et B:B ont automatiquement la même taille. C'est pourquoi elles sont souvent utilisées comme plages de critères : elles évitent les erreurs.

COUNTIFS est le moteur de nombreux tableaux de bord. Placez les noms de régions dans la colonne F et les noms de mois sur la ligne 1, puis comptez chaque combinaison à l'aide de références mixtes afin de remplir toute la grille.

Le verrouillage des colonnes avec $A:$A et des cellules de recherche avec $F2 / G$1 permet à une seule formule de remplir tout un tableau croisé de décomptes lorsqu'elle est copiée.

Ne confondez pas ces fonctions. COUNTIF accepte une seule paire range, criteria, tandis que COUNTIFS en accepte une ou plusieurs. Contrairement à SUMIFS, aucune des deux fonctions n'a de plage de résultat distincte — elles se contentent de compter.

Dès que vous avez besoin de plus d'une condition, utilisez COUNTIFS. Beaucoup de personnes utilisent COUNTIFS même pour un seul critère afin de ne retenir qu'un seul schéma cohérent pour SUM, COUNT et AVERAGE.

Réfléchissez à la différence entre COUNTIFS et SUMIFS.

Vous pouvez maintenant dénombrer les lignes selon de nombreuses conditions :

• COUNTIFS(range1, crit1, range2, crit2, ...) — aucune plage de somme, uniquement des paires de critères à vérifier.
• Comptez une plage entre deux valeurs en répétant la même colonne avec deux bornes.
• Utilisez "<>" pour les cellules non vides et concaténez avec & pour les limites définies dans des cellules.
• Toutes les plages doivent avoir la même taille.

Ensuite : calculer la moyenne des valeurs selon plusieurs conditions avec AVERAGEIFS.