Comment utiliser la formule SOMME.SI avec des critères

(Last Updated On: )

Avec une seule formule, vous pouvez résumer tout type de données financières, en additionnant les nombres selon une liste de critères. Cela facilite la synthèse des données financières pour construire un bilan ou un rapport de ventes par exemple.

J’expliquai brièvement dans cet article l’utilisation des formules SOMME.SI pour synthétiser des données. Dans cet article, je vais donner plusieurs exemples détaillés d’utilisation des formules SOMME.SI et SOMME.SI.ENS.

Somme avec un seul critère

Pour additionner des valeurs selon un critère, utilisez la fonction SOMME.SI.

La syntaxe est la suivante :

=SOMME.SI(plage critère ; critère ; plage somme)

Le critère peut être une valeur numérique, du texte, une date, la référence à une cellule, une condition avec opérateur ‘<‘, ‘>’, ‘=’, ‘<>’, etc.

Voici quelques exemples :

total des ventes dont la région est renseignée en cellule D9 =SOMME.SI(DimVentes[Région];D9;DimVentes[Montant ventes])
total des ventes pour la région centre =SOMME.SI(DimVentes[Région];”Centre”;DimVentes[Montant ventes])
total des ventes pour la date 25/11/2015 =SOMME.SI(DimVentes[Date];”25/11/2015″;DimVentes[Montant ventes])
total des ventes depuis le 01/01/2016 =SOMME.SI(DimVentes[Date];”>01/01/2016″;DimVentes[Montant ventes])
totales des ventes supérieures ou égales à 1000 € =SOMME.SI(DimVentes[Montant ventes];”>=1000″;DimVentes[Montant ventes])

Somme avec plusieurs critères

Pour additionner des cellules en fonction de plusieurs critères, utilisez la formule SOMME.SI.ENS.

La syntaxe est :

=SOMME.SI.ENS(plage somme ; plage critère 1 ; critère 1 ; plage critère 2 ; critère 2 ; etc)

Exemple :

=SOMME.SI.ENS(DimVentes[Montant ventes];DimVentes[Région];”Est”;DimVentes[Responsable];”Hugues”)

 Somme avec un critère de correspondance sur le texte

On peut additionner des cellules avec un critère sur le contenu de la cellule.

Dans l’exemple ci-dessous, toutes les commandes de Stylo et Pack stylos seront additionnées, car elles contiennent la chaîne “stylo”.

Voici la syntaxe :

=SOMME.SI(E34:E40;”*stylo*”;H34:H40)

En utilisant le critère “*stylo*”, toutes les cellules de la colonne Item avec un texte contenant la chaîne “stylo” seront additionnées. Il est important de mettre des astéristiques “*” en début et fin de “stylo”, sinon la correspondance se fera de manière exacte et seules les ventes d’item “Stylo” seront additionnées.

Somme avec une liste extensible de critères

Au lieu d’additionner des valeurs suivant des critères prédéfinis comme avec SOMME.SI.ENS, il est aussi possible d’entrer une liste de critères et d’ajouter plus tard des critères à la liste sans modifier la formule.

Dans l’exemple ci-dessous, on calcule le total des ventes pour la région centre (1er critère défini) et pour la liste d’item Crayon et Classeurs renseignés en cellules H51:H52.

Pour calculer le total :

  1. Entrez la formule SOMME.SI.ENS avec la plage à sommer “Ventes”, suivie de la première plage de critères “Région”, puis le premier critère “Centre”
  2. Entrez la deuxième plage de critères “Item”, puis sélectionner une plage de cellules comme critère (ici H51:H54)
  3. Encadrer la formule SOMME.SI.ENS par la fonction SOMME et valider avec Ctrl+Shift+Entrée, car il s’agit d’une formule matricielle
  4. Alternativement, vous pouvez aussi utiliser SOMMEPROD au lieu de SOMME pour encadrer la formule SOMME.SI.ENS, puis valider simplement par Entrée

Si on ajoute des critères supplémentaires sous la cellule H52, la formule recalculera automatiquement le total des ventes suivant les nouveaux critères. Il en va de même si on enlève des critères.

{=SOMME(SOMME.SI.ENS(Ventes2[Ventes];Ventes2[Région];”Centre”;Ventes2[Item];ListeCritères[Critères]))}

ou bien :

=SOMMEPROD(SOMME.SI.ENS(Ventes2[Ventes];Ventes2[Région];”Centre”;Ventes2[Item];ListeCritères[Critères]))

Somme pour un intervalle de dates

Nous utiliserons une formule SOMME.SI.ENS pour obtenir le total des ventes à partir d’une date de départ et avant une date de fin. Deux colonnes sont nécessaires pour cela, une colonnes de dates de ventes et une colonnes de montant des ventes.

Voici un exemple :

On souhaite obtenir le total des ventes sur le premier semestre de l’année 2016

Entrez la formule suivante :

  1. Le premier critère sur les dates contient l’opérateur “>=” suivi de la date de début
  2. Le deuxième critère sur les dates contient l’opérateur “<” suivi de la date de fin
=SOMME.SI.ENS(DimVentes[Montant ventes];DimVentes[Date];”>=01/01/2016″;DimVentes[Date];”<01/07/2016″)

Somme pour une liste filtrée

Après avoir filtré les lignes dans un tableau de valeurs, vous pouvez utiliser la fonction AGREGAT au lieu de la fonction SOMME pour additionner des nombres seulement sur les lignes visibles. Cette fonction a été introduite dans Excel 2010.

Similaire à la fonction SOUS-TOTAL, AGREGAT ignore les lignes cachées ou les erreurs et propose plusieurs opérations, telles que SOMME ou MOYENNE sur les données sélectionnées.

Pour en savoir plus sur la fonction AGREGAT, vous pouvez vous référer à l’article détaillé que j’ai écrit sur l’utilisation de cette formule.

 

Avez-vous d’autres exemples d’utilisation des formules SOMME.SI et SOMME.SI.ENS ? Vous pouvez les partager en  commentaires.

 

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

Comment utiliser la formule SOMME.SI avec des critères

Ce fichier Excel résume l'utilisation à travers des exemples des fonctions SOMME.SI et SOMME.SI.ENS.
Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

Souscrivez à ma newsletter :

26 commentaires

  1. PETIT dit

    Bonjour,
    Je souhaiterai rendre le critère de SOMME.SI (ou SOMME.SI.ENS) dynamique.
    J’explique : en prenant votre exemple de ventes entre 2 dates, je souhaiterais avoir des dates dynamiques (par exemple construites avec TEXTE($A$1;”jj/mm/aa”, ou même “>$A$1”) avec pour mon la valeur de A1 qui évolue, c-a-d la chaine “>=01/01/2016” est remplacée par le contenu d’une valeur construite et non figée.
    J’ai essayé “>=TEXTE(DATE($A$1;1;1);”jj/mm/aa”))”, avec A1 qui est elle-même une formule(qui donne 2015, ou 2016 …), mais ça ne le fait pas.
    Merci de votre aide.

    • Lionel dit

      Bonjour,
      Oui c’est possible de rendre le critère dynamique. Le plus simple est d’entrer la date dans une cellule, mettons A1.
      Puis dans la formule SOMME.SI ou SOMME.SI.ENS, dans le critère vous renseignez ceci “>=”&$A$1.
      Ensuite quand vous modifiez la date dans la cellule A1, le résultat de la formule se met à jour automatiquement.
      Voilà !
      Lionel

      • Aurelien dit

        Bonjour Lionel,
        Au détour d’une recherche, je tombe sur votre article et plus précisément, sur votre commentaire “critère dynamique” qui me donne la réponse que je cherchais !
        Alors, merci beaucoup pour votre coup de main indirect !
        Une très bonne journée,
        Aurélien

  2. Alex dit

    Bonsoir,
    Je déterre un vieux sujet mais je me tord l’esprit sans résultat.
    J’ai une formule SOMME.SI.ENS qui ne reconnait pas une date, comment faire ?

    Voici la formule =SOMME.SI.ENS(D2:D50;C2:C50;”>=I8″;C2:C50;”<=I9").
    Le résultat qui apparait est 0 à la place de ma somme
    Alors que si dans la formule je note la date manuellement aucun soucis.
    Je rappelle que j'ai vérifié I8 et I9 qui sont bien configurées en format date.

    Merci pour le temps que vous m'accorderez

  3. ROMMELAERE dit

    Bonjour,
    Dans une plage de donnée, je souhaite faire la somme des cellules contenant une formule. Pour cela je pensais utiliser SOMME.SI et utiliser ESTFORMULE comme critère, mais je n’arrive pas à trouver la bonne syntaxe :
    =SOMME.SI(B2:E2;ESTFORMULE()”)

    Savez-vous comment utiliser une fonction en tant que critère ?

    Cordialement,
    Victor

    • Lionel dit

      Bonjour Victor,

      Je comprend que vous souhaitez faire la somme des valeurs des cellules obtenues par une formule.
      Il faut donc utiliser la formule SOMME.SI.ENS(plage à sommer; plage de critères; critère)
      Pour le critère, il faut tester quand la formule ESTFORMULE renvoie VRAI.

      Une méthode simple : insérez une ligne, par exemple en B3 avec ESTFORMULE(B2), puis tirez vers la droite jusqu’à E2.
      => Cela indique pour chaque cellule B2 à E2 si elle contient une formule.

      Puis n’importe où dans la feuille, insérez la formule : =SOMME.SI.ENS(B2:F2; B3:E3; VRAI)
      => Cela va additionner les valeurs des cellules B2 à E2 pour lesquelles le critère ESTFORMULE en B3 à E3 est VRAI.

      En espérant vous avoir mis sur une piste.

      Lionel

  4. Youexcelize dit

    Bonjour,

    est-il possible de mettre 2 listes de critères dans la même formule svp ?

    =SOMMEPROD(SOMME.SI.ENS(Ventes2[Ventes];Ventes2[Région];”Centre”;Ventes2[Item];ListeCritères[Critères]))

    Merci par avance.

  5. ANTOINE dit

    bonjour

    sur la formule suivante :=SOMME.SI(DimVentes[Région];D9;DimVentes[Montant ventes])

    si je souhaite rajouter une période mensuelle (ex : du 01/01/2018 au 31/01/2018)mais impossible de trouver la bonne formule .

    pour faire simple je recherche une formule par intervalle de date mais 2 voir 3 critères

    merci de votre aide

    • Lionel dit

      Bonjour Antoine,

      le sujet est traité dans l’article : cf. Somme pour un intervalle de dates
      en ajoutant les critères de dates comme suivant à votre formule : “>=01/01/2018” et “=01/01/2018″; DimVentes[Date];”

  6. Dominique dit

    Bonjour Lionel,
    J’utilise SOMME.SI pour obtenir la somme des factures liées à une même commande. Mon problème est que je vais chercher l’information dans un autre fichier et que lorsque ce dernier est fermé, les cellules concernées indiquent toutes #VALEUR! Voici la formule :
    =SOMME.SI(‘X:\COMMUN CONTROLE FINANCIER DPI\[DAD budget travaux 2020 – Vdef.xlsx]Factures’!$C:$C;G208;’X:\COMMUN CONTROLE FINANCIER DPI\[DAD budget travaux 2020 – Vdef.xlsx]Factures’!$F:$F)
    J’obtiens en revanche un résultat en passant par la formule RECHERCHEV (=RECHERCHEV(G209;’X:\COMMUN CONTROLE FINANCIER DPI\[DAD budget travaux 2020 – Vdef.xlsx]Factures’!$C$2:$J$77;4;0)) mais il ne prend que la résultat de la première occurence et non la somme de toutes les factures liée à la commande.
    Rien n’y fait non plus en insérant les accolades d’une formule matricielle.
    Auriez-vous une solution à me donner ?
    D’avance merci
    Bien à vous,

    Dominique

    • Lionel dit

      Bonjour Dominique

      La fonction SOMME.SI retourne une erreur #VALEUR! si elle fait référence à un classeur fermé (cf. Microsoft).

      A la place de :

      =SOMME.SI(‘X:\COMMUN CONTROLE FINANCIER DPI\[DAD budget travaux 2020 – Vdef.xlsx]Factures’!$C$2:$C$77;G208;’X:\COMMUN CONTROLE FINANCIER DPI\[DAD budget travaux 2020 – Vdef.xlsx]Factures’!$F$2:$F$77)

      Essayez ceci :

      =SOMMEPROD( --('X:\COMMUN CONTROLE FINANCIER DPI\[DAD budget travaux 2020 – Vdef.xlsx]Factures'!$C$2:$C$77=G208); 'X:\COMMUN CONTROLE FINANCIER DPI\[DAD budget travaux 2020 – Vdef.xlsx]Factures'!$F$2:$F$77)

      Cette formule va tester si chaque cellule de la plage C2:C77 du classeur fermé est égale à la valeur commençant en G208 dans le classeur ouvert. Si c’est Vrai, alors au lieu de retourner VRAI, elle retourne 1 (on met 2 fois le signe – – pour convertir VRAI/FAUX en 1/0). Puis elle va multiplier par 1 les cellules de la plage F2:F77 si la condition est remplie ou par 0 si la condition n’est pas remplie, avant de faire la somme des valeurs de la plage.

      Notez que je n’ai pas mis de référence de colonne entière dans la formule, et l’importance de l’opérateur – – suivi de la condition entre ().

      Lionel

  7. Keli KOHOUE dit

    Bonjour M. Lionel,
    Je veux utiliser la formule SOMME.SI.ENS(), mais je veux utiliser un critère dynamique. La particularité du critère ici est qu’il s’agit d’un text. Vous l’aviez fait avec des chiffres, mais avec un texte, c’est plus compliqué. Je peux utiliser les guillemets mais c’est pour des text figé. J’ai une cellule C1 qui contient une liste données sous forme de text. La formule SOMME.SI.ENS(plage_som; plage_crit; C1) retourne “#valeur”
    Je ne sais pas si je me fait comprendre mais jusqu’ici j’ai pas pu trouver la solution à mon problème. Je vous en serais reconnaissant si vous pouvez m’aider. Cela fait bien trop longtemps que je suis sur ce problème.
    Je vous remercie.

    • Lionel dit

      Bonjour Keli,

      Je ne suis pas sûr de comprendre votre critère “une cellule C1 qui contient une liste données sous forme de texte”.
      Si vous avez plusieurs critères de texte à respecter, utilisez SOMME.SI.ENS(plage_som; plage_crit1; texte1; plage_crit2; texte2).
      Si votre problème est plus complexe, sans doute qu’il faut le décomposer en petits bouts, en décomposant par exemple les critères complexes en colonnes de critères simples.
      Puis vous vous ramenez à la formule SOMME.SI.ENS(plage_som; plage_crit1; C1; plage_crit2; C2; plage_crit3; C3 ; etc.)

  8. Lola dit

    Bonjour,

    Tout d’abord merci pour vos explications,
    Cependant je ne comprends pas ce que signifie le “Dim” dans “DimVentes” etc.

    Pour ma part j’ai plusieurs colonne a mon tableau, dont les dates de paiement (colonne E), ainsi que les montants (colonne F); j’aimerais calculer: la somme des montants a payer chaque mois. Par exemple ce mois-ci, calculer la somme des montants compris entre le 1/05/2020 et le 31/05/202.

    J’ai essayé avec la formule : =SOMME.SI.ENS(F:F;E:E;“”>=01/05/2020””;E:E;”“=30/05/2020″”)

    Mais elle ne fonctionne pas.. pouvez-vous m’éclairer s’il vous plait,
    Un grand merci par avance !

    • Lionel dit

      Bonjour Lola

      DimVente est simplement le nom de la table ou de la plage de cellules. Ce pourrait être F1:F22 par exemple.
      Je vous invite à lire mon article sur les tables Excel et voir quels sont les avantages à convertir une plage de cellules en table si vous ne savez pas comment les utiliser.

      Votre formule ne peut pas fonctionner, il faut la modifier en remplaçant le = de la 2ème condition par SOMME.SI.ENS(F:F;E:E;”>=01/05/2020″;E:E;”

      Lionel

  9. Lola dit

    Re

    Oui c’est bien le début de ma formule que vous avez écrit? je dois réecrire deux fois cela?
    La formule que vous avez écrite n’a pas de parenthèse de fin?
    Pouvez-vous m’écrire la formule entièrement svp?

    Merci

      • Lola dit

        Bonjour,

        Ce que je vois de votre message :

        J’ai repris votre formule en intégralité et simplement modifié ce qui n’allait pas dans la 2ème condition : SOMME.SI.ENS(F:F;E:E;”>=01/05/2020″;E:E;”

        Je ne vois pas la fin de la formule (parenthèse de fin), est-ce un beug ?

        • Lionel dit

          Lola

          J’ai repris votre formule en intégralité et simplement modifié ce qui n’allait pas dans la 2ème condition :

          SOMME.SI.ENS(F:F;E:E;”>=01/05/2020″;E:E;”

  10. Nibart dit

    Bonjour. Merci du travail. Ma formule bloque sur la date :
    =SOMME.SI.ENS(Budget[Dep];Budget[DATE];”>=16/11/2020″;Budget[DATE];”<01/12/2020")
    Elle bloque dès le 1er 2020
    Où est l'erreur ?
    Merci d'avance

  11. ESCOUBAS Xtophe dit

    Bonjour Lionel,
    Je tombe sur votre site qui est super et très bien expliqué. Je bloque sur un tableau ou j’ai en colonne A des dates et en colonne B des chiffres, mais je ne veux faire la somme des chiffres uniquement si la date change. Pourriez vous m’aider??
    Merci d’avance.
    Xtophe

  12. Elvis-Denis dit

    Bonjour,
    Je souhaiterai faire apparaitre la somme la plus elevé d’une série selon un critere de nombre de cellule a additionner.
    La condition de cette somme est que ces cellules devront-seront obligatoirement côte a côte pour faire l’addition de ces cellules (selon le critère du nombres des cellule a addtionner )
    Pour shématiser ci-dessous :
    ci-aprés ma série : 15 / 3 / 12 / 8 / 21 / 7
    ci-aprés mon critère de nombre de cellule a additionner : 3
    Donc je m’attend a ce que la formule face l’addition de 3 cellules de ma série qui totalisent la plus grande somme : soit ici 12 + 8 + 21 = le plus grand nombre selon la somme de 3 cellules qui se suivent

    Merci a Celle, Celui, et Ceux qui pourront m’aider dans ce probleme

  13. Alexis dit

    Bonjour Lionel,

    Je cherche à connaitre le montant des commandes par mois en fonction d’une marque.
    Voici la formule : =SOMME.SI.ENS(Commandes!$V$8:V10000000;Commandes!$F$8:F100000000;’Suivi Achat_Ventes’!A5;Commandes!$R$8:R10000000;”=>01/01/2022″;Commandes!$R$8:R10000000;”=<31/01/22")

    J'ai comme résultat : #NOM?

    Merci pour votre retour,
    Alexis

    • Lionel dit

      Bonjour,
      – Le dernier critère est “>=31/01/22”.
      – La dernière cellule d’un fichier xlsx (je suppose) ne peut aller au delà de 1 048 576 lignes, or vous avez renseigné 10 000 000
      Essayez de corriger et voir ce que cela donne

  14. Jean Claude Nalasse MENDY dit

    Bonjour,
    Je veux faire la somme d’une colonne ‘Montant’ par le filtre de deux dates qui se trouve sur une colonne ‘date’.
    Ex: J’ai une feuille excel de plusieurs lignes environ 2000 lignes et deux colonnes, une colonne Montant et l’autre Date.
    Alors je veux en faisant du 01/01/2022 comme date début au 30/03/2022 date fin avoir la somme du montant à cette période
    Merci de votre….

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *