- SOMME.SI.ENS(plage_somme; plage1; critère1; plage2; critère2; …) cumule les lignes qui vérifient tous les critères : compte ET période, par exemple.
- Attention à l’ordre des arguments : la plage à additionner vient en premier, contrairement à SOMME.SI.
- Pour une période, deux critères sur la même colonne de dates : ">="&début et "<="&fin, avec les dates saisies dans des cellules de paramètres.
- Une balance se contrôle toujours : total débit = total crédit, aussi bien pour la période que pour les cumuls.
La fonction SOMME.SI de l’épisode précédent porte un seul critère. Or une balance exige au moins deux dimensions : le compte et la période. Construire une balance générale à partir d’un journal d’écritures est le travail typique de SOMME.SI.ENS. Cet épisode en fait une balance complète, avec cumuls et contrôle d’équilibre.
Ce que vous saurez faire à la fin de l’épisode
- écrire SOMME.SI.ENS et NB.SI.ENS avec plusieurs critères ;
- borner une période avec deux critères sur une colonne de dates ;
- piloter la balance avec des cellules de paramètres (du … au …) ;
- distinguer les mouvements d’une période et les cumuls ;
- contrôler l’équilibre débit/crédit.
La syntaxe
=SOMME.SI.ENS(plage_somme; plage_critère1; critère1; plage_critère2; critère2; …) (anglais : SUMIFS)L’ordre est inversé par rapport à SOMME.SI : la plage à additionner est le premier argument, suivie des paires plage de critère / critère. Toutes les plages doivent avoir la même taille. Les critères s’écrivent comme dans l’épisode 22 (valeurs, comparaisons, jokers, références de cellules). Les fonctions sœurs : NB.SI.ENS (nombre de lignes), MOYENNE.SI.ENS, MAX.SI.ENS et MIN.SI.ENS (Excel 2019 et suivants).
Le cas : janvier et février d’Atlas Négoce
Le journal compte 18 écritures, douze en janvier (on les connaît depuis l’épisode 9) et six en février : une vente de 8 000 HT au client (9 600 TTC, TVA de 1 600), et un achat de 4 000 HT (TVA récupérable 800, fournisseur 4 800). On veut la balance de février (mouvements du mois) et les cumuls à fin février.
Les paramètres sont saisis en I2 (début : 01/02/2026) et J2 (fin : 28/02/2026).
Figure 1 : la balance. Repère 1 : formule des mouvements ; repère 2 : les dates de la période ; repère 3 : les totaux de contrôle.
Les mouvements de la période
Débit de la période, pour le compte en A2 :
=SOMME.SI.ENS(Journal!$C$2:$C$19; ← plage à additionner : le débit
Journal!$B$2:$B$19; A2; ← compte égal à A2
Journal!$A$2:$A$19; ">="&$I$2; ← date postérieure ou égale au début
Journal!$A$2:$A$19; "<="&$J$2) ← date antérieure ou égale à la finTrois critères : le compte, la date de début, la date de fin. La colonne des dates est utilisée deux fois (une fois par borne). Le crédit s’écrit de la même façon avec la colonne D du journal. Le solde de la période est =B2-C2 (débit moins crédit).
Les cumuls à la fin de la période
Pour le cumul, on enlève la borne de début : tout ce qui est antérieur ou égal à la date de fin.
=SOMME.SI.ENS(Journal!$C$2:$C$19; Journal!$B$2:$B$19; A2; Journal!$A$2:$A$19; "<="&$J$2)Le résultat
| Compte | Débit février | Crédit février | Solde février | Cumul débit | Cumul crédit | Solde cumulé |
|---|---|---|---|---|---|---|
| 3421 Clients | 9 600,00 | 0,00 | 9 600,00 | 15 600,00 | 6 000,00 | 9 600,00 |
| 3455 TVA récupérable | 800,00 | 0,00 | 800,00 | 2 800,00 | 0,00 | 2 800,00 |
| 4411 Fournisseurs | 0,00 | 4 800,00 | (4 800,00) | 12 000,00 | 16 800,00 | (4 800,00) |
| 4455 TVA facturée | 0,00 | 1 600,00 | (1 600,00) | 0,00 | 2 600,00 | (2 600,00) |
| 5141 Banques | 0,00 | 0,00 | 0,00 | 6 000,00 | 20 500,00 | (14 500,00) |
| 6111 Achats | 4 000,00 | 0,00 | 4 000,00 | 14 000,00 | 0,00 | 14 000,00 |
| 6131 Loyers | 0,00 | 0,00 | 0,00 | 8 500,00 | 0,00 | 8 500,00 |
| 7111 Ventes | 0,00 | 8 000,00 | (8 000,00) | 0,00 | 13 000,00 | (13 000,00) |
| Totaux | 14 400,00 | 14 400,00 | 0,00 | 58 900,00 | 58 900,00 | 0,00 |
Lecture : en février, les mouvements s’équilibrent à 14 400 au débit comme au crédit ; les cumuls à fin février s’équilibrent à 58 900, soit 44 500 (janvier) + 14 400 (février). Le compte banque n’a bougé qu’en janvier (6 000 reçus, 20 500 payés) et affiche un solde créditeur de 14 500 : sa position dépend du solde d’ouverture, qui n’est pas dans cet exemple.
Le compte de solde nul dans la colonne Solde février (5141, 6131) est une situation normale : aucun mouvement dans la période.
Le contrôle
La ligne des totaux est la preuve que la balance est juste : un journal équilibré (chaque écriture a un débit égal à son crédit) donne toujours une balance équilibrée, à condition que tous les comptes du journal figurent dans la liste de la balance. Si un compte manque (par exemple le 6199 saisi par erreur), la balance affiche un déséquilibre : c’est le signal de chercher. Un contrôle simple : comparer SOMME(B2:B9) avec SOMME.SI.ENS(Journal!C:C; dates…) sans critère de compte, ou avec le total du journal.
Le décompte NB.SI.ENS(Journal!$A$2:$A$19;">="&$I$2;Journal!$A$2:$A$19;"<="&$J$2) donne le nombre d’écritures de la période : 6 pour février, qui permet de vérifier qu’aucune ligne n’a échappé à la période.
Les comptes avec jokers
On peut cumuler par classe avec un joker : "6*" désigne tous les comptes qui commencent par 6.
=SOMME.SI.ENS(Journal!$C$2:$C$19; Journal!$B$2:$B$19; "6*")Cette formule renvoie le total des charges (classe 6) : 14 000 + 8 500 = 22 500,00 au débit à fin février, soit 6111 et 6131. Le joker ne fonctionne que sur du texte : si les numéros de compte sont stockés en nombres, la formule ne trouve rien. C’est une des raisons pour lesquelles nos numéros de compte sont des textes depuis l’épisode 2.
Les pièges
- Ordre des arguments : la plage à additionner d’abord. Un oubli donne une erreur ou un résultat bizarre.
- Tailles de plages différentes :
#VALEUR!. Les plages du journal doivent toutes aller de la ligne 2 à la ligne 19. - Plages qui ne suivent pas : quand le journal grandit, la plage
$A$2:$A$19ne couvre pas les nouvelles lignes. Solution : tableau Excel (tblJournal[Débit]) ou plages plus larges ($A$2:$A$5000). - Dates en texte : le critère
">="&$I$2compare des nombres ; si les dates du journal sont du texte, aucune ne correspond. - Critère
"<="&$J$2avec une date sans heure : la date de fin est incluse. Une date avec heure poserait problème pour le dernier jour. - Comptes manquants dans la balance : signalés par un écart avec le journal.
À vous de jouer
Dans le classeur d’exercice :
- Complétez les colonnes B, C, E et F de la balance, puis les soldes et les totaux. Les totaux doivent être 14 400 et 58 900.
- Changez la période : du 01/01/2026 au 31/01/2026. Quels sont les totaux de la période ?
- Revenez à février. Ajoutez une ligne du journal : 12/02/2026, compte
6199, débit 500, crédit 0. Que devient la balance ? Que faut-il faire ? - Écrivez la formule du total de la classe 7 (produits) à fin février avec le joker
"7*".
Correction. Pour janvier, les totaux de période sont 44 500,00 au débit et 44 500,00 au crédit. À l’étape 3, le journal n’est plus équilibré (500 au débit sans contrepartie), et le compte 6199 n’étant pas dans la liste, la balance reste à 14 400 au débit et 14 400 au crédit pour février : elle ne voit pas les 500. La comparaison avec le total du journal révèle l’écart ; il faut ajouter 6199 à la balance et saisir la contrepartie de l’écriture. Pour la classe 7, =SOMME.SI.ENS(Journal!$D$2:$D$19;Journal!$B$2:$B$19;"7*";Journal!$A$2:$A$19;"<="&$J$2) donne 13 000,00 (5 000 + 8 000).
À retenir
SOMME.SI.ENS(plage_somme; plage1; critère1; plage2; critère2…): tous les critères doivent être vrais.- Une période = deux critères sur la même colonne de dates, avec des dates dans des cellules de paramètres.
- Mouvements de période (borne de début et de fin) ≠ cumuls (borne de fin seule).
- La balance doit s’équilibrer ; sinon, un compte, une date ou un type est en défaut.
- Les jokers (
"6*") exigent des comptes en texte.
Épisode 24, dernier du module 4 : SOMMEPROD, pour les calculs pondérés que ni SOMME.SI ni SOMME.SI.ENS ne savent faire.
Questions fréquentes
Combien de critères peut-on mettre dans SOMME.SI.ENS ?
Jusqu’à 127 paires plage/critère. Toutes les plages doivent avoir exactement la même taille que la plage à additionner, sinon la fonction renvoie #VALEUR!.
Comment obtenir tous les comptes d’une classe (par exemple tous les comptes 6…) ?
Avec un joker : le critère "6*" additionne les comptes qui commencent par 6. Le joker ne fonctionne que sur du texte : si vos comptes sont des nombres, il ne trouve rien. C’est une raison de stocker les numéros de compte en texte.
Pourquoi ma balance ne tombe-t-elle pas à zéro ?
Causes fréquentes : un compte du journal absent de la liste de la balance, une date hors période, un montant stocké en texte, un doublon de ligne, ou une plage de somme qui n’inclut pas les dernières lignes. Comparez le total de la balance avec le total du journal.
SOMME.SI.ENS ralentit-elle un grand classeur ?
Sur plusieurs dizaines de milliers de lignes et plusieurs centaines de comptes, le temps de calcul reste raisonnable. Au-delà, ou avec de nombreux critères, un tableau croisé dynamique (épisode 25) ou Power Query est plus rapide.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.