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

(Last Updated On: 15 avril 2020)

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 :

20 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;”

Répondre à Lionel Annuler la réponse

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