SOMME.SI.ENS et NB.SI.ENS : additionner et compter avec plusieurs conditions

Savoir combien un commercial a vendu au total est simple. Les choses se compliquent quand la question comporte plusieurs parties : combien Ana a-t-elle vendu dans la zone Nord, combien de commandes de plus de 200 y a-t-il eu en mars, ou combien de fois le même produit apparaît-il pour le même commercial. C’est le rôle de SOMME.SI.ENS et NB.SI.ENS.

Ces deux fonctions appliquent toutes les conditions indiquées en même temps et ne retiennent que les lignes qui les remplissent toutes. Vous verrez leur syntaxe, des exemples avec du texte, des nombres, des dates et des caractères génériques, comment construire un tableau récapitulatif et comment s’en servir pour repérer des enregistrements en double.

Les données d’exemple

Nous utiliserons ce journal des ventes, avec les en-têtes en ligne 1 et les données dans les lignes 2 à 9. La colonne A contient de vraies dates, pas du texte :

Syntaxe des deux fonctions

Les deux sont disponibles depuis Excel 2007 et existent aussi dans Google Sheets et LibreOffice Calc.

=SOMME.SI.ENS(plage_somme;plage_critères1;critères1;plage_critères2;critères2)

=NB.SI.ENS(plage_critères1;critères1;plage_critères2;critères2)

• plage_somme : les cellules contenant les nombres à additionner (uniquement dans SOMME.SI.ENS).
• plage_critères : la colonne dans laquelle chaque condition est vérifiée.
• critères : la condition, par exemple "Ana", ">=200" ou une référence de cellule.

Vous pouvez ajouter jusqu’à 127 paires plage/critère. Attention à un détail qui piège beaucoup de monde : dans SOMME.SI, la plage à additionner se place à la fin, alors que dans SOMME.SI.ENS elle vient en premier.

Additionner et compter avec plusieurs conditions

Pour additionner les ventes d’Ana dans la zone Nord :

=SOMME.SI.ENS(E2:E9;B2:B9;"Ana";C2:C9;"Nord")

Le résultat est 520 : seules les lignes 2 et 4 sont prises en compte. La vente d’Ana dans la zone Sud est exclue. Pour compter les ventes du Produit A dans la zone Nord :

=NB.SI.ENS(C2:C9;"Nord";D2:D9;"Produit A")

La formule renvoie 3 (lignes 2, 5 et 9).

Critères avec opérateurs

Pour comparer des nombres, écrivez l’opérateur et la valeur ensemble entre guillemets. Cette formule compte les ventes de la zone Nord supérieures ou égales à 200 :

=NB.SI.ENS(E2:E9;">=200";C2:C9;"Nord")

Le résultat est 4. Les opérateurs valides sont =, <>, >, <, >= et <=. Par exemple, "<>Sud" compte tout ce qui n’est pas Sud.

Critères avec des dates

Pour additionner une période, placez la date de début en K1 et la date de fin en K2, puis reliez l’opérateur à la cellule avec & :

=SOMME.SI.ENS(E2:E9;A2:A9;">="&K1;A2:A9;"<="&K2)

Avec le 1er et le 31 mars dans ces cellules, le résultat est 1670. Remarquez que la même plage apparaît deux fois, une pour chaque borne. Utiliser des cellules plutôt que de taper la date dans la formule évite les problèmes liés au format de date de chaque pays.

Caractères génériques

Dans les critères de texte, vous pouvez utiliser * (n’importe quel nombre de caractères) et ? (un seul caractère). Pour compter les ventes des commerciaux dont le prénom commence par M :

=NB.SI.ENS(B2:B9;"M*")

La formule renvoie 2. Pour rechercher un véritable astérisque, saisissez ~*.

Construire un tableau récapitulatif

L’usage le plus utile consiste à créer un petit tableau croisé. Saisissez les commerciaux en G2:G4, les zones en H1 (Nord) et I1 (Sud), puis cette formule en H2 :

=SOMME.SI.ENS($E$2:$E$9;$B$2:$B$9;$G2;$C$2:$C$9;H$1)

Recopiez-la ensuite dans le reste du tableau. Les signes $ sont essentiels :

1. Les plages de données ont un $ sur la ligne et la colonne, pour ne pas se décaler lors de la recopie.
2. $G2 bloque la colonne : chaque ligne du tableau lit toujours son commercial.
3. H$1 bloque la ligne : chaque colonne lit toujours sa zone.
4. Appuyez plusieurs fois sur F4 sur une référence pour passer d’un type de référence à l’autre.

La somme de tout le tableau (2110) correspond au total de la colonne E : une vérification rapide qu’aucune ligne n’a été oubliée.

Quand il faut une condition OU

Toutes les conditions de ces fonctions sont combinées avec ET : une ligne doit les remplir toutes. Pour additionner les ventes d’Ana ou de Luis, le plus clair est d’additionner deux formules :

=SOMME.SI.ENS(E2:E9;B2:B9;"Ana")+SOMME.SI.ENS(E2:E9;B2:B9;"Luis")

Le résultat est 1520 (1020 pour Ana plus 500 pour Luis). Veillez simplement à ce que les conditions ne se chevauchent pas : si une ligne pouvait remplir les deux, elle serait comptée deux fois.

Repérer les doublons avec NB.SI.ENS

NB.SI.ENS permet aussi de savoir combien de fois une combinaison de valeurs apparaît. Dans une colonne auxiliaire, saisissez en ligne 2 :

=SI(NB.SI.ENS($B$2:$B$9;B2;$D$2:$D$9;D2)>1;"En double";"")

La formule compte combien de lignes ont le même commercial et le même produit que la ligne en cours. Dans les données d’exemple, elle signale les paires Ana et Produit A (lignes 2 et 7) et Luis et Produit A (lignes 6 et 9). C’est pratique pour vérifier avant de supprimer : vous pouvez filtrer la colonne auxiliaire et décider quoi faire dans chaque cas.

Si vous voulez simplement supprimer les lignes en double d’un gros fichier, notre outil gratuit pour supprimer les doublons le fait avec des fichiers XLSX, XLS, CSV et ODS, sans aucune formule.

Erreurs fréquentes

• Plages de tailles différentes. Si la plage à additionner est E2:E9 et qu’une plage de critères est B2:B8, Excel renvoie #VALEUR!. Toutes doivent avoir le même nombre de lignes.
• Opérateur non concaténé. ">=K1" compare avec le texte K1, pas avec la valeur de la cellule. La bonne écriture est ">="&K1.
• Nombres stockés sous forme de texte dans la plage à additionner. Ils sont ignorés, et le total est plus faible que prévu. C’est fréquent avec des données collées depuis d’autres logiciels ou importées d’un CSV.
• Classeurs externes fermés. Si les plages se trouvent dans un autre fichier et que celui-ci est fermé, la formule renvoie #VALEUR!. Ouvrez-le ou copiez les données dans le même classeur.
• Espaces en trop. « Ana  » avec une espace finale ne correspond pas à « Ana ». Nettoyez la colonne avec SUPPRESPACE avant de calculer.

Conclusion

SOMME.SI.ENS et NB.SI.ENS répondent à des questions à plusieurs conditions sans tableau croisé dynamique ni filtre manuel. Avec des critères dans des cellules, des références mixtes et l’opérateur &, vous pouvez créer des rapports qui se mettent à jour tout seuls quand vous ajoutez des données.

Gardez des plages de même taille, contrôlez les totaux avec une somme générale et nettoyez les données avant de commencer : la plupart des résultats étranges viennent d’espaces, de textes ou de lignes en double.

Questions fréquentes