Excel : SOMME.SI.ENS et NB.SI.ENS (sumifs et countifs)

Publié le 24 juillet 2012
par Sophie Marchand M.Sc., CPA, CGA, MVP
Fonction SOMME.SI.ENS

Excel : SOMME.SI.ENS et NB.SI.ENS (sumifs et countifs)

Avant la version 2007 d’Excel, il était plus fastidieux de faire la somme de certaines cellules ou le décompte de certaines cellules selon plusieurs critères. Il fallait utiliser différents stratagèmes, comme les suivants :
.

.

Depuis 2007 par contre, Excel nous offre la fonction SOMME.SI.ENS ou SUMIFS en anglais. Cette simple fonction vous permet de faire la somme de certaines cellules selon divers critères. De la même manière, la fonction NB.SI.ENS ou COUNTIFS en anglais, vous permet de faire le décompte de certaines cellules selon divers critères.

 

Par exemple, la fonctionne SOMME.SI.ENS comprend 3 critères minimum :

  1. La plage à additionner (en bleu dans l’exemple ci-dessous)
  2. La plage où se trouve le premier critère (en rouge dans l’exemple ci-dessous)
  3. Le critère à chercher dans la plage précédente (en mauve dans l’exemple ci-dessous)
  4. [optionnel] La plage où se trouve le deuxième critère (en vert dans l’exemple ci-dessous)
  5. [optionnel] Le critère à chercher dans la plage précédente (en rose dans l’exemple ci-dessous)

 

Explication de la fonction EXCEL SOMME.SI.ENS

.

Dans cet exemple, nous calculons donc le coût total des “légumes” “en spécial” puisqu’on cherche à retourner :

  1. L’additionne de toutes les lignes de la colonne Coût total (colonne bleue)
  2. Pour les lignes où les informations de la colonne Groupe sont égales à Légumes (colonne rouge)
  3. Et pour les lignes où les informations de la colonne Spécial sont égales à En spécial (colonne verte)

 

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

 

Jetez un coup d’oeil à la courte vidéo suivante pour comprendre comment fonctionnent ces deux fonctions.

.

Pour une meilleure expérience de visionnement, je vous conseille de cliquer sur le logo “YouTube” en bas à droite.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’approfondir vos connaissances avec Excel, dont celles touchant la fonction SOMME.SI.ENS et la fonction NB.SI.ENS, nous vous recommandons notre formation Excel – Mise à niveau.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

François Le May
Écrit il y a 5 mois
Il était grandement temps que je la suive!

Mon efficacité au travail et ma capacité à faire des analyses rapidement vont être grandement améliorées.

Mélanie Bonneau
Écrit il y a 5 mois
Adaptée pour le monde des CPA

Je trouve que cette formation est la plus adaptée pour le monde des CPA. En d'autres mots, j'apprends davantage ici que dans une formation générale de type "débutant - intermédaire - avancé" et je trouve que c'est plus concret pour mes activités quotidiennes. Merci!

Isabelle Madé
Écrit il y a 7 mois
Excellente formation!

Le découpage des sections est parfait, on peut prendre le tout à petites bouchées à notre rythme. J'utilise Excel depuis très longtemps et comme bien des gens j'ai appris sur le tas. Cette mise à niveau m'a permis d'apprendre ou mieux comprendre certaines formules et fonctionnalités que j'aurais dû maîtriser bien avant! Je suis prête pour la suite...

Melissa Forgues
Écrit il y a 8 mois
J'ai adoré!

Je croyais faire cette formation pour rien puisque je travaille avec Excel et prit quelques cours dans le passé. Je croyais que je maîtrisais toute la base! Ayayaye! Je me suis trompé! La formatrice donne énormément de trucs et des informations jusque là inconnue pour moi, jamais montré en salle de formation avec d'Autres compagnies... Déjà là, je vais sauvé beaucoup de temps. OMG! Bravo!

Sylvie Giroux
Écrit il y a 2 ans
Captivant

La formatrice expliquait très clairement chaque exemple et avait un ton de voix dynamique. En suivant une formation en ligne, je craignais de m'endormir, mais non! J'ai été captivée et intéressée tout au long de ma formation.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

54 réflexions sur “Excel : SOMME.SI.ENS et NB.SI.ENS (sumifs et countifs)”

  1. Bonjour,
    merci beaucoup pour cet article. Ces fonctions semblent être très utiles. J’ai un problème lors de la saise des formules, on me dit qu’il y a une erreure dans la limite de la plage des montants à additionner (cas de sumifs) ou la plage des celles à compter (countifs). Merci d’avance pour votre aide.

    Le Fan

    1. Bonjour Le Fan,

      Merci pour votre commentaire. Vous avez raison de dire qu’il y a un maximum de “critères” que l’on peut insérer dans une telle formule. Le maximum se situe à 127 dans la version 2007 d’Excel. Vous pouvez retrouver cette information dans le menu d’aide d’Excel.

      Au plaisir,

      Sophie

  2. Pour palier à la limite des critères dans le base trop large j’utilise la fonction sous.total avec les filtres c’est le mieux que j’ai trouvé.

    1. Bonjour Emmanuel,

      Merci pour votre question.

      Je crois qu’existe seulement une façon de faire sans faire intervenir VBA. Il s’agit de faire un filtre sur votre couleur et ensuite d’ajouter une somme en bas de votre colonne de données.

      Pour voir un exemple de tri par couleur (le filtre fonctionne de la même façon), voici un lien: http://lecfomasque.com/2011/07/13/excel-trier-des-couleurs/.

      Sinon, vous pouvez utiliser VBA: http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm.

      Au plaisir,

      Sophie

  3. oui, les guillemets ne passe pas bien..

    J’aurais une autre question s’il vous plait.
    Cette fois ci au sujet de graphiques.

    je trace un nuage de points, abscisse allant de 0 a 10.
    Est ce que je peux faire directement sur ce graphique un histogramme en regroupant (moyenne, somme, etc) tout les points se trouvant entre l’abscisse 0 et 1, 1 et 2, 2 et 3, etc.?

    Pour le moment je crée un autre graphique a partir d’un autre tableau réalisé avec les fonctions sommeifs, countifs, averageifs.

    Merci beaucoup
    Samuel

  4. Bonjour Sophie,

    Est-ce que je peux appliquer automatiquement une fonction de excel a 1 cellule sur 2.
    Par exemple, =min(A1, C1, E1, G1, etc.)

    Merci

        1. Ah je vois…

          Vous ne pourriez pas le faire en une étape mais vous pourriez le faire en deux étapes. La première étape serait de reconstituer une ligne de données qui ne contiendrait que les données à inclure dans votre formule. Pour cela vous pourriez par exemple utiliser la formule offset() (decaler en français). Ensuite, vous pourriez appliquer votre fonction min.

          En espérant que cela a pu vous aider.

          A+

          Sophie

  5. Bonjour,

    J’aimerais avoir des informations concernant les graphs surfaciques sur excel 2010, s’il vous plait.
    Je souhaiterai changer l’échelle de couleurs.
    Par exemple, sur un graph de température, il y aura un gradient de couleur allant de 0 a 20°C – 20 a 40°C, etc.
    Je voudrais changer cette échelle et faire plutot 0 a 10°C, 10 a 20°C, 20 a 30°C, etc.

    Je sais que sur excel 2003 il suffit de faire un click droit et les options apparaissent.

    Merci beaucoup.

    Tomaš

  6. Bonjour MonCherWatson,

    Concernant la fonction NB.SI.ENS j’aimerais savoir si vous avez connaissances de restrictions sur les arguments, notamment s’agissant de passer comme plage de données une plage définie dans un classeur externe à la formule.

    EXEMPLE : Dans un classeur j’ai la formule suivante :
    =NB.SI.ENS(‘C:DATASuivi bug[Suivi Bug.xls]Feuil1’!$A$2:$A$10000;”=Urgent”)

    Le problème est qu’elle renvoie #VALEUR si le fichier “Suivi Bug.xls” est fermé.Est-ce normal ou ai je fais une erreur? NB.ENS.SI est t’elle incompatible avec les liaisons externes?

    1. Bonjour,

      Merci pour votre question.

      En général, si vous ne cliquez pas sur “Mettre à jour les liens” quand vous ouvrez votre fichier Excel, vous ne devriez pas avoir de problème. Par contre, si vous cliquez sur “Mettre à jour les liens” et que le fichier lié est fermé, Excel retournera des #valeurs.

      Merci,

      Sophie

  7. bonjour,

    J’ai réalisé un graph surfacique sur excel 2010 en 2d.
    mais j’ai une ombre ds toute la partie basse de mon graph.
    comme si il y avait un trait qui sépare le graph en 2. Comme si ce trait était un sommet. Du coup, j’ai une face éclairée en haut et une face ombragé en bas.
    pourtant les parties foncées ont la meme valeur que celle du haut.
    pouvez vous m’aider a enlever cette effet s’il vous plait.

    Merci par avance

  8. Bonjour Sophie,

    J’ai le même type de fichier que vous à la différence qu’à la place du montant, j’ai du texte. Par conséquent, la formule somme.si.ens me retourne 0.

    J’ai une colonne avec des numéros de département, une colonne avec un code article et donc un nom de ville. Je voudrais savoir s’il est possible de récupérer ce nom de ville?

    En vous remerciant,

    Glenn

  9. Bonjour
    Je suis en train de faire un tableau sur excel (étonnant non ? :-)) et j’utilise la fonction nb.si. Je voudrais maintenant trier le résultat par ordre décroissant. La fonction trier ne fonctionne pas dans ce cas. Comment dois-je m’y prendre ?
    Je vous remercie par avance de votre réponse.
    très bonne soirée
    Annie

  10. Bonjour,

    Bravo pour votre article.

    J’ai bien compris le fait que cette formule n’était disponible qu’à partir d’Excel 2007, toutefois, existe-il une formule similaire ou une combinaison de formules équivalente pour Excel 2003?

    Merci beaucoup,
    Loïc

  11. Bonjour,
    Merci pour ce bel exemple par contre j’ai un problème la formule somme.si.ens me renvoi aucune donnée quand il s’agit de texte je sais que cela est logique vu qu on ne peut pas additionner du texte 🙂 Par contre je ne trouve pas de formule équivalente pour renvoyer un texte avec plusieurs critères.
    Je vous remercie.

      1. Bonjour Sophie,
        En fait, j’ai une base de données et je fais un tableau récapitulatif qui reprend un chiffre d’affaires et la devise de ce dernier (usd ou euro) en triant par nom fournisseur, par produit et par mois. La formule =SOMME.SI.ENS me renvoi les bonnes valeurs dans la colonne chiffre d’affaires mais me renvoi une case vide dans la colonne devise étant donnée que cette formule ne prend en compte que les données numériques.

        Merci pour votre aide.

        Laila.

          1. Holala je suis bête je suis tellement obnubilée par cette fonction que j’en ai oublié les basiques. Merci 🙂
            Laila.

          2. Bonjour,

            moi j’ai bien essayé la fonction recherchev mais je ne peux y mettre plusieurs critères.

  12. Bonjour,
    Merci pour l’aide que vous nous apportez sur vos deux blogs !

    Je suis bloquée sur l’utilisation de la fonction NB.SI.ENS. Je souhaite appliquer deux critères pour une même plage : =NB.SI.ENS(C:C;”69″&”57″;etc…) En fait, la première plage (colonne C) doit filtrer sur les deux critères 69 et 57. Mais cela ne fonctionne pas. J’ai essayé d’insérer un “&”, ou encore une “*” entre mes critères mais rien y fait !

    Merci de me donner un petit coup de pouce 😉

    Cdlt,

    Elodie

    1. Bonjour Élodie,

      Les paramètres de la fonction NB.SI.ENS doivent être enchaînés comme suit:

      – Plage de référence pour le premier critère, critère 1
      – Plage de référence pour le deuxième critère, critère 2
      – Etc.

      Donc, dans votre exemple, vous ne pouvez pas demander de faire le compte des cellules de la colonne C qui sont à la fois égales à 69 et à la fois égales à 57. C’est impossible.

      Si ce que vous souhaitez faire, c’est le compte des cellules de la colonne C qui comporte soit 69 ou soit 57, alors ça, c’est différent.

      Est-ce que c’est ce que vous tentez de faire?

      Sophie

  13. Bonjour,

    Oui je cherche à compter combien de cellules de la colonne C comportent les références 69 et 57. J’ai réussi à le faire en utilisant cette formule :
    =NB.SI.ENS(C:C;”69″;Q:Q;201404)+NB.SI.ENS(C:C;”57″;Q:Q;201404)
    Cela fonctionne bien sauf lorsque j’ai 4 critères de références. Il y a trop d’arguments pour la formule…

    Il faudrait donc je trouve une formule qui me permette de compter le nombre de cellule de la colonne C qui contiennent 4 numéros de référence différents.

    Avez-vous une idée ?

    Merci beaucoup ! Je continue à chercher de mon côté mais je crois que j’ai trop la tête dans le guidon !

    Elodie

  14. Bonjour,

    Votre commentaire m’a aidé ! Je ne vais pas vous faire perdre votre temps avec un si petit truc ! Comme vous m’avez répondu que la formule que je souhaitais utiliser pouvait comporter plusieurs arguments, alors j’ai tout recommencé (tête reposée, en train de digérer mon déjeuner 😉 ) et j’ai finalement trouvé mon erreur de frappe ! tout fonctionne ! Mais cela étant, il est vrai qu’au delà d’un certain nombre de critères pour une même colonne, écrire “a la mano” toute la formule avec des NB.SI.ENS qui se répètent pour chacun des critères, c’est très lourd.

    Avez-vous une astuce ?

    Merci beaucoup de me stimuler les méninges !
    Elodie

  15. Ping : Un oeil sur le web | Excel: Calculer un min ou un max conditionnel

  16. Bonjour, Je dois calculer le nb de cellules dont le résultat est compris entre deux données (dans mon exemple 30 et 39 sont mes données). Voici la formule que j’ai utilisée
    =NB.SI(F2:F7416;”=30″)+NB.SI(F2:F7416;”=31″)+NB.SI(F2:F7416;”=32″)+NB.SI(F2:F7416;”=33″)+NB.SI(F2:F7416;”=34″)+NB.SI(F2:F7416;”=35″)+NB.SI(F2:F7416;”=36″)+NB.SI(F2:F7416;”=37″)+NB.SI(F2:F7416;”=38″)+NB.SI(F2:F7416;”=39″) Ma question est la suivante. Est-ce qu’il existe une formule avec nb.si.ens qui me permettrait de connaître le nb de cellules dont les résultats sont compris entre 30 et 39. J’ai essayé celle-ci mais ça ne fonctionne pas : =nb.si.ens(f2:f7416;”>=30″;f2:f7416;”<=39")

    1. Bonjour Claudine,

      La fonction Nb.si.ens est utile lorsque vous avez plusieurs critères sur plusieurs champs. Ici, vous avez plusieurs critères (soit, plus grand que 30 et plus petit que 39) mais sur le même champs. Donc, vous devez plutôt utiliser une fonction ET (pour mentionner que ces deux critères doivent être rencontrés avant de faire le compte).

      VOus pouvez trouver un exemple sur la page suivante: https://www.lecfomasque.com/excel-si-et-ou/.

      Au plaisir,

      Sophie

  17. Bonjour,
    J’ai un problème je vous expose le problème:
    j’ai sur plusieurs colonnes des prix qui correspondent à plusieurs secteurs d’activité
    j’ai sur chaque ligne des années
    j’ai crée dans une autre feuille une liste pour les années et une pour les secteurs d’activité
    je souhaiterais pouvoir obtenir un tableau représentant la somme des prix en fonction à la fois de l’année et du secteur d’activité, sur cinq années afin de tracer un graphique modulable, j’ai essayé plusieurs fois des somme.si.ens mais la mention “valeur” apparait toujours
    Pouvez vous m’aider?

  18. Bonjour Sophie,

    Est-il possible de faire un sous total de somme.si.ens ? En effet, je ne souhaite pas compter les sous-totaux calculés au milieu de ma liste pour faire cette somme si.ens)…

    Cordialement,

    Rrradassse

  19. Bonjour,

    La fonction Agregat permet de faire différentes opérations sur des données, sans considérer les sous-totaux. Voir article suivant: https://www.lecfomasque.com/excel-2010-la-nouvelle-fonction-aggregate-agregat/.

    Toutefois, le somme.si.ens ne figure pas dans la liste des opérations disponibles. Il faudrait donc que vous ajoutiez une colonne à vos données, qui ramènerait la valeur si tous les critères sont rencontrés et 0 si les critères ne sont pas rencontrés et que vous utilisiez ensuite la fonction Agregat avec l’opération de somme, sur cette colonne.

    Au plaisir,

    Sophie

  20. Bonjour,

    J’aimerai savoir si on peut insérer une recherchev pour déterminer la plage somme ?

    J’ai un fichier ou je dois faire une SOMME.SI.ENS ou la plage somme doit changer en fonction de mon critère magasin.

    J’ai donc remplacé la plage somme par une recherchev pour qu’elle renvoit la plage somme du magasin sélectionné. Chaque plage somme des magasins est saisie dans un tableau index dans un autre onglet et c’est dans ce tableau que se fait ma recherchev.

    Le problème c’est qu’excel ne semble pas accepter une formule en lieu et place d’une plage somme sélectionnée normalement.

    Merci pour votre aide ?

  21. Bonjour,
    J’ai une question sur les fonctions nb.si.ens ou somme.si.ens, peut-on mettre un critère d’exclusion ? si oui, comment l’écrire.

  22. Bonjour,

    Je tente désespérément de compter le nombre de ligne comportant des dates un mois donné dans une colonne. Par exemple :
    NB.SI(MOIS(A4:A93);”=2″)
    pour compter le nombre de cellules de la colonne A indiquant une date en Février.

    Pour des raisons qui m’échappent complétement cette formule ne fonctionne pas et je ne sais pas où cela cloche. Par ailleurs je tiens absolument à utiliser la fonction NB.SI (ou NB.SI.ENS) et non la fonction SOMMEPROD parce qu’ultérieurement j’ajouterai beaucoup d’autres conditions à formule ci-dessus.

    Merci pour votre aide

    1. Bonjour,

      Il faudrait plutôt ajouter une colonne dans votre base de données avec la fonction MOIS() (par exemple, la colonne B) et ensuite construire votre formule comme suit NB.SI(B4:B93),2).

      Au plaisir,

      Sophie

Laisser un commentaire

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

Scroll to Top