Contact: 514-605-7112 / info@lecfomasque.com

Excel: Somme.si.ens et nb.si.ens (sumifs et countifs)

Publié le : 24 juillet 2012

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.

.

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.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Consulter la liste des formations offertes

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, et cumule plus de 12 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • répondre Le Fan ,

    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

    • répondre moncherwatson ,

      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

    • répondre Victor Verrier ,

      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é.

      • répondre Emmanuel Challier ,

        Bonjour Sophie,
        Si je souhaite calculer le nombre de couleur (=3 dans votre exemple), y a-t-il une formule pour cela ? MERCI

      • répondre aloha ,

        Bonjour,

        Est ce que peux faire référence a une cellule comme critere?
        par exemple: =COUNTIFS(B8:B19, »>B20″, « <=B21")

        • répondre moncherwatson ,

          Bonjour,

          Merci pour votre question. Vous pouvez le faire, mais vous devez utiliser la formule suivante: =COUNTIFS(B8:B19, »> »&B20,B8:B19, »<"&B21).

          Au plaisir,

          Sophie

          • répondre aloha ,

            Merci de votre réponse rapide.

            • répondre moncherwatson ,

              Vous aurez compris que WordPress a de la difficulté avec mes guillemets…

          • répondre aloha ,

            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

            • répondre moncherwatson ,

              Bonjour Samuel,

              Je ne suis pas certaine de bien saisir votre question. POurriez-vous me faire parvenir le fichier que vous utilisez en ce moment et à partir de ce fichier, me dire ce que vous souhaiteriez changer/améliorer?

              Merci,

              Sophie

              Mon courriel : marchandsophie@hotmail.com

            • répondre gg ,

              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

              • répondre moncherwatson ,

                Bonjour,

                Oui, vous pouvez sans problème poser la formule min(A1,C1,E1,etc.).

                Au plaisir,

                Sophie

                • répondre gg ,

                  oui mais j’ai qqs centaine de cellules…
                  pourrais-je le faire sans avoir a cliquer sur chaque cellule?

                  • répondre moncherwatson ,

                    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

              • répondre tom ,

                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š

              • répondre gui ,

                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?

                • répondre moncherwatson ,

                  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

                • répondre jeanjean ,

                  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

                • répondre Glenn ,

                  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

                  • répondre moncherwatson ,

                    Bonjour Glenn,

                    Ce que vous m’indiquez est en effet possible. Pourriez-vous me faire pavenir votre fichier à marchandsophie@hotmail.com afin que je puisse comprendre où se trouve le problème?

                    Merci,

                    Sophie

                  • répondre Chocky ,

                    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

                    • répondre moncherwatson ,

                      Bonjour Annie,

                      Je ne suis pas certaine de comprendre votre question. Pourriez-vous me faire parvenir votre fichier par courriel et m’indiquer votre problématique?

                      Merci,

                      Sophie

                      smarchand@lecfomasque.com

                    • répondre Loïc ,

                      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

                    • répondre Laila Loum ,

                      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.

                      • répondre moncherwatson ,

                        Bonjour Laila,

                        Je ne suis pas certaine de comprendre votre question, pourriez-vous me fournir un exemple plus concret?

                        Merci,

                        Sophie

                        • répondre Laila Loum ,

                          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.

                          • répondre moncherwatson ,

                            Bonjour Laila,

                            Pourquoi ne pas simplement faire un recherchev (vlookup) pour la portion devise?

                            Sophie

                            • Laila loum ,

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

                      • répondre Elodie ,

                        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

                        • répondre moncherwatson ,

                          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

                        • répondre Elodie ,

                          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

                          • répondre moncherwatson ,

                            Bonjour Élodie,

                            Faites-moi parvenir le fichier à smarchand@lecfomasque.com.

                            La formule peut très bien comprendre 4 critères donc il doit y avoir une erreur dans la composition de celle-ci.

                            Merci,

                            Sophie

                          • répondre Elodie ,

                            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

                            • répondre Un oeil sur le web | Excel: Calculer un min ou un max conditionnel ,

                              […] connaissez déjà les fonctions conditionnelles d’Excel, comme Somme.si (Sumif) et Somme.si.ens (Sumifs), Nb.si (Countif) et Nb.si.ens (Countifs) et Moyenne.si (Averageif) et Moyenne.si.ens (Averageifs). […]

                              Écrire un commentaire


                              • *