Exemple Somme.Si.Ens

SOMME.SI.ENS : la fonction Excel tant attendue

Je n’ai découvert qu’il y a peu seulement la fonction SOMME.SI.ENS ! Il semblerait que ce soit la fonction si longtemps attendue pour se simplifier la vie. Celle que beaucoup cherchent sans savoir qu’elle existe. Cette fonction a pourtant été initiée avec Office 2007 (d’ailleurs, voici la liste de toutes les fonctions Excel).

Fonction Somme.Si.Ens en Excel

Je vous vois déjà en train de dire : « On connaît SOMME.SI, mais pas avec .ENS… ça doit être un truc compliqué avec des matrices ou des ensembles de données, voir des codes que l’on ne connaît pas et dans lesquels on ne veut surtout pas rentrer.« . Et bien non. il s’agit tout simplement de la fonction qui vous permet de faire une somme avec plusieurs critères.

Quand la SOMME.SI ne vous permet qu’un seul critère, SOMME.SI.ENS vous en offre plusieurs, jusqu’à 127 possibilités de plages et de critères. Ça laisse de la marge.

Curieux finalement ? Voici comment elle fonctionne :

Plan de site

Syntaxe de la fonction SOMME.SI.ENS

= SOMME.SI.ENS (somme de la plage ; plage du critère 1 ; critère 1 ; plage du critère 2 ; critère 2 ; ... )

En clair, on sélectionne d’abord la plage de cellules que l’on souhaite additionner, puis la plage de données où peut se trouver le critère 1 et donc le critère 1 (une cellule contenant cette donnée ou la saisie directe du critère)… et on recommence avec le nombre de critères dont nous avons besoin.

Espérons que les formules plus longues restent lisibles…

Exemple pratique de l’utilisation de SOMME.SI.ENS

Prenons un exemple rapide d’un tableau dans lequel nous trouvons des ventes par villes de produits A, B et C.

Exemple Somme.Si.Ens

Pour la première somme, nous saisirons comme formule :

Somme.Si.Ens formule 1

et pour la seconde somme :

Somme.Si.Ens formule 2

Vous l’aurez compris, les plages de cellules inscrites dans les formules ci-dessus correspondent aux zones du tableau que j’ai créé. Cela fonctionne tout aussi bien avec des références types A2:A10.

Avec ces formules, vous obtiendrez les résultats suivants, correspondants à vos attentes :

Résultat Somme.Si.Ens formule 3

 

Et voilà ! Vous voyez ? C’est pratique pour les tris dans des tableaux de données et cela vous évitera de saisir des formules complexes de conditions (SI combinées avec des RECHERCHEV par exemple). Il ne vous reste plus qu’à tester dans vos propres tableaux.

Bonne somme conditionnelle !

 



25 réflexions sur “SOMME.SI.ENS : la fonction Excel tant attendue”

  1. Bonjour,

    Cette fonction est belle, mais j’aimerais que ma PLAGE_SOMME soit sur plusieurs colonne et non pas que sur 1 seule. Genre g3:i109 et non pas g3:g109 comme accepté. Sinon l’erreur VALEUR apparaît.

    D’avance merci

  2. Très pratique, et vos explications très explicites. Je vous suis très reconnaissant. J’ai trouvé ce qui m’aidera dans la gestion de mes stocks. Encore merci.

  3. Petite question…

    comment faire la somme des ventes à Reims pour LES mois de janvier et février ?

    Soit mettre deux possibilités comme réponses possible à un critère.

    Merci d’avance,
    Macnoel

    1. Je ne vois pas d’autre solution que de faire appel à une fonction matricielle, je viens de tenter sans succès de la récrire. si quelqu’un se souvient comment faire…
      Personnellement depuis que somme.si.ens existe, je n’y joue plus.

      Rappel ne pas oublier Ctrl+Maj+Entrée pour valider

      1. Ok, merci.

        Pour ma part, j’ai fait une somme de somme.is.ens. Un terme avec janvier, l’autre avec février.

        Heureusement dans mon cas réel je n’ai pas plus de 6 critères différents 😉
        Dommage de ne pas pouvoir faire une liste de critère comme argument.

        A+

        1. Bonjour,

          Je ne vois pas l’utilité de cette fonction sachant qu’on avait deja tout à disposition… Soit avec la fonction « Somme » en mode matricielle soit plus tard avec « SOMMEPROD ».
          On va utiliser cette derniere d’ailleur pour répondre à la question.
          Ce que tu cherches à faire est un janvier OU février et non un ET 🙂
          Pour le OU on utilise + et pour le ET *.

          =SOMMEPROD(D2:D8*(A2:A8= »PARIS »)*(B2:B8= »PROD A »)*((C2:C8= »JANVIER »)+(C2:C8= »FEVRIER »)))

          Ce qui se lit : On somme D2:D8 pour les lignes ou A2:A8= »PARIS » ET B2:B8= »PROD A ET ( C2:C8= »JANVIER » OU C2:C8= »FEVRIER ») !!!

          Cordialement

          Cartman

          1. C’est exact et j’ai très souvent utilisé SOMMEPROD de la même façon que vous. Mais la fonction SOMME.SI.ENS a pour intérêt de simplifier l’utilisation et de permettre aux utilisateurs peu expérimentés de se rapprocher de la fonction SOMME.SI (très connue). Je pense que leur idée est d’aller vers la simplification des tâches, ce qui n’est pas sans doute pas plus mal.

          2. Bonjour Excel-Plus,

            Je suis tout à fait d’accord qu’il est bon de simplifier, mais pour moi cette fonction ne simplifie rien du tout. Mettre des ; à la place de * ou des =…. et surtout elle ne permet pas à l’utilisateur de comprendre ce qu’il cherche à faire en l’occurence une somme matricielle.
            Alors oui l’utilisation de la fonction SOMME en matricielle était un peu tordu avec la validation par ctrl+entree, d’ou l’arrivée de SOMMEPROD mais la SOMME.SI.ENS… faut s’avoir s’arréter !

          3. mieux encore, histoire d’utiliser les 2 formules … et éviter des formules qui peuvent vite atteindre 10lignes …
            =SOMMEPROD(somme.si.ens(D2:D8;A2:A8;»PARIS »;B2:B8;»PROD A »;C2:C8;{« JANVIER »; »FEVRIER »}))

            ce qui permet d’ajouter uniquement le critère à la liste et ne recopier toute la formule
            et bien entendu, cela se décline également avec une plage de cellules

            perso, somme.si.ens + rapide d’exécution que sommeprod ==> dans un contexte de bdd lourdes à traiter !!

            mais j’utilise les 2 selon les contextes ! 3 ans que je pratique excel avec complexité et cette formule, et c’est royal.

            Mais tout le monde n’est pas au même niveau (dans ma boite de 200 personnes, personne d’autre ne maitrise autant). Donc la simplification automatisée est très bien pour ceux qui ne sont pas très aventuriers !

      2. SOMMEPROD et LA formule magique d’EXCEL
        Elle s’utilise comme une fonction « normale » et non matricielle :

        SommeProd((Range1=Critère1) * (Range2=Critère2) * … * RangeAAditionner)

        Exemple
        SommeProd((Tableau[Mois]= »Janvier ») * (Tableau[Produits]= »Produits A ») * Tableau[CA]) + SommeProd((Tableau[Mois]= »Février ») * (Tableau[Produits]= »Produits A ») * Tableau[CA])

        retourne les ventes de Janvier et de Février.

        Remarquez que le « * » à l’intérieur de SommeProd correspond à un « ET » sur les critères et le « + » entre les SommeProd correspondent à des OU, tout comme en algèbre booléenne.

        Avantages :
        Très puissante
        Très logique et simple
        Utilisation aisée des références relatives, absolues ou « semi-absolues »

        Inconvénient :
        Peu conduire à des formules très longues en cas de plusieurs « OU ».

  4. Adieu jolies fonctions matricielles pleines de d’étoiles, chapeaux pointues et accolades…
    Quoique.
    Ces dernières deviendrons encore plus imbuvables.

    Je vais de ce pas tenter de comprendre sommeprod que je ne connais pas.

  5. Merci pour cette astuce… Cette fonction parait très pratique effectivement
    Je vais m’en servir pour calculer les sommes dues par mes débiteurs et à recouvrer par moi…

Répondre à pedro Annuler la réponse

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.