SOMME.SI, NB.SI et MOYENNE.SI : cumuler selon une condition

Un comptable passe son temps à répondre à des questions du type : « combien avons-nous payé au compte 6111 ? », « combien de factures sont encore impayées ? », « quel est le montant moyen des achats auprès de ce fournisseur ? ». Ces questions s’expriment par « total (ou nombre, ou moyenne) des lignes qui répondent à un critère ». Trois fonctions les résolvent sans trier ni filtrer.

Ce que vous saurez faire à la fin de l’épisode

• écrire SOMME.SI, NB.SI et MOYENNE.SI avec la bonne plage de critère ;
• formuler un critère : texte, nombre, comparaison, joker, référence de cellule ;
• déléguer le critère à une cellule pour rendre le tableau interactif ;
• vérifier un résultat sans se tromper de plage ;
• reconnaître quand un second critère impose SOMME.SI.ENS.

Les trois fonctions

Lecture de SOMME.SI : « parmi les lignes dont la plage de critère vérifie le critère, additionne la plage de somme ». Si la plage de somme est omise, Excel additionne la plage de critère elle-même (pratique pour ">=5000" sur la colonne des montants).

Le cas : un journal de charges de janvier

Atlas Négoce a huit lignes de charges avec compte, tiers, montant et statut de paiement. Le gérant pose sept questions.

Figure 1 : les questions (colonne G), les critères saisis (colonne H, en jaune) et les réponses (colonne I).

Vérifions deux résultats à la main, ce qu’il faut toujours faire. Le compte 6111 compte six lignes : 10 000 + 12 600 + 3 450 + 4 800 + 2 150 + 780 = 33 780 ; sa moyenne est 33 780 ÷ 6 = 5 630. Les montants supérieurs ou égaux à 5 000 sont 10 000, 8 500, 12 600 et 8 500 : 39 600. Les tiers commençant par « Sud » sont Sud Distribution (10 000 + 12 600 + 2 150) et Sud Négoce (4 800) : 29 550.

Écrire un critère

Trois règles pratiques :

1. Mettez le critère dans une cellule. =SOMME.SI($B$2:$B$9;H2;$D$2:$D$9) se modifie en changeant H2, sans toucher à la formule ; on peut y brancher une liste déroulante (épisode 11) pour créer un petit tableau de consultation.
2. Un opérateur reste entre guillemets, la référence est collée avec & : ">="&H2, et non ">=H2" (qui chercherait le texte « H2 »).
3. Pour chercher un vrai * ou ?, faites-le précéder d’un tilde (~*).

Les pièges

• Plages de tailles différentes ou décalées. SOMME.SI($B$2:$B$9;…;$D$2:$D$10) n’est pas refusée par Excel : elle additionne les cellules en alignant le début des deux plages, et produit un résultat erroné sans erreur. Les deux plages doivent débuter à la même ligne et avoir la même hauteur (les tableaux Excel règlent ce risque).
• Les types. SOMME.SI est plus tolérante que RECHERCHEV : le critère "6111" trouve aussi bien le texte 6111 que le nombre 6111. Mais un espace parasite ("6111 ") ou une valeur en texte comme "≥ 5 000" échappe au critère.
• Les critères sur le texte ne tiennent pas compte de la casse : "payé" et "Payé" sont équivalents.
• Un second critère. Dès qu’il en faut deux (compte et statut, compte et période), SOMME.SI ne suffit plus : c’est le rôle de SOMME.SI.ENS (épisode 23). Quelques contournements existent (cumuler avec SOMMEPROD, épisode 24), mais ils sont moins lisibles.
• Fichiers fermés. SOMME.SI ne lit pas une plage située dans un classeur fermé (elle renvoie #VALEUR!) : ouvrez le fichier source ou utilisez Power Query (épisode 46).
• Références à des plages entières. SOMME.SI(B:B;…;D:D) est valide et prend en compte les lignes ajoutées ; elle est plus lente sur de très gros classeurs, mais très pratique dans un tableau de bord alimenté en continu.

Une construction fréquente : le taux d’impayés

La combinaison de deux fonctions donne des indicateurs utiles :

=SOMME.SI(E2:E9;"Non payé";D2:D9) / SOMME(D2:D9)

Sur notre journal, le total des charges est 10 000 + 8 500 + 12 600 + 3 450 + 8 500 + 4 800 + 2 150 + 780 = 50 780, dont 23 250 non payés : le taux d’impayés vaut 23 250 ÷ 50 780 = 45,8 %. Un taux se vérifie sur ses deux termes (numérateur et dénominateur), pas seulement sur le résultat.

À vous de jouer

Dans l’onglet Journal du classeur d’exercice :

1. Complétez I2:I8 avec les formules du tableau et vérifiez les résultats à la main sur deux d’entre elles.
2. Ajoutez la question « Total des lignes dont le tiers contient Atlas » avec le critère *Atlas* : quel montant ?
3. Calculez le total des lignes payées du compte 6111. Quel problème rencontrez-vous avec SOMME.SI ? (Indice : deux critères.)
4. Modifiez le critère de la question 5 en ">"&10000 : quel résultat ?

Correction. La question 2 donne : Immobilière Atlas (8 500 + 8 500) et Papeterie Atlas (3 450 + 780) = 21 230,00. La question 3 nécessite deux critères (compte 6111 et statut Payé) : SOMME.SI ne le permet pas ; la réponse, par SOMME.SI.ENS, est 19 030,00 (10 000 + 3 450 + 4 800 + 780). Avec le critère ">"&10000, une seule ligne dépasse strictement 10 000 (12 600) ; la ligne de 10 000 exactement est exclue, car elle n’est pas strictement supérieure : le résultat est 12 600,00.

À retenir

• SOMME.SI(plage_critère; critère; plage_somme), NB.SI(plage; critère), MOYENNE.SI(…) : un critère, trois usages.
• Un critère peut être un texte, un nombre, une comparaison entre guillemets, un joker ; mettez-le dans une cellule.
• Même taille, même point de départ pour les plages ; vérifiez un résultat à la main.
• Pour plusieurs critères : SOMME.SI.ENS, à l’épisode suivant.

Épisode 23 : SOMME.SI.ENS et NB.SI.ENS, avec lesquelles nous construirons une balance générale à partir d’un journal.