Tutoriel Excel : Somme basée sur plusieurs conditions

Ce tutoriel explique la différence entre les fonctions SOMME.SI et SOMME.SI.ENS en termes de syntaxe et d'utilisation. Il fournit également un certain nombre d'exemples de formules pour additionner des valeurs avec plusieurs critères ET / OU dans Excel 2016, 2013, 2010, 2007, 2003 et versions antérieures.

La fonction SOMME.SI est conçue pour additionner des nombres en fonction d'un critère. Par défaut, elle considère une plage, un critère et une Somme_plage selon sa syntaxe. Cependant, si vous souhaitez ajouter des nombres en fonction de plusieurs critères, vous disposez des options suivantes :

  • ·Additionner plusieurs fonctions SOMME.SI basées sur le OU logique appliquées pour chaque critère séparément.
  • ·Utiliser la fonction SOMME.SI.ENS qui est par défaut conçue pour additionner des nombres avec plusieurs critères basés sur le ET logique.
  • ·Vous pouvez également utiliser la fonction SOMME.SI.ENS pour additionner un nombre avec plusieurs critères, basés sur le OU logique, avec une constante de tableau.

Dans ce tutoriel, vous apprendrez à additionner des nombres en fonction de plusieurs critères en utilisant trois méthodes simples avec des exemples pratiques.

Table des matières

Fonction Excel SOMME.SI - syntaxe et utilisation

Fonction Excel SOMME.SI.ENS - syntaxe et utilisation

Fonction SOMME.SI avec plusieurs critères basés sur le OU logique

Méthode 1 : Ajout de plusieurs fonctions SOMME.SI

Méthode 2 : Utilisation de SOMME et SOMME.SI avec une constante de tableau

Méthode 3 : Utilisation de SOMME.SI avec la fonction SOMMEPROD

Fonction SOMME.SI.ENS avec plusieurs critères basés sur le OU logique

Méthode 1 : SOMME.SI.ENS + SOMME.SI.ENS

Méthode 2 : SOMME et SOMME.SI.ENS avec un tableau de critères

Utiliser la fonction SOMME dans des formules matricielles

Exemple 1 : Somme avec plusieurs critères ET dans Excel 2003 et versions antérieures

Exemple 2 : Formule matricielle avec SOMME dans les versions Excel modernes

Fonction Excel SOMME.SI - syntaxe et utilisation

=SOMME.SI(plage; critères; [somme_plage])

La formule utilise les arguments suivants :

  • ·Plage (argument requis) - Il s'agit de la plage de cellules sur laquelle vous souhaitez appliquer les critères.
  • ·Critères (argument requis) - Ce sont les critères qui sont utilisés pour déterminer quelles cellules doivent être additionnées. Lorsque vous fournissez l'argument critère, il peut être:
  • Une valeur numérique (qui peut être un entier, décimal, date, heure ou valeur logique) (par exemple 10, 01/01/2018, VRAI).
  • Une chaîne de texte (par exemple, "Texte", "Jeudi").
  • Une expression (par exemple «> 12», « 0»).
  • ·Somme_plage (argument facultatif) - Il s'agit d'un tableau de valeurs numériques (ou de cellules contenant des valeurs numériques) qui doivent être additionnées si l'entrée de plage correspondante satisfait les critères fournis. Si l'argument [Somme_plage] est omis, les valeurs de l'argument range sont additionnées à la place.

Comme vous le voyez, la syntaxe de la fonction Excel SOMME.SI autorise une seule condition. Cependant, SOMME.SI peut être utilisée pour additionner des valeurs avec plusieurs critères. Comment est-ce possible ? Eh bien, en ajoutant les résultats de plusieurs fonctions SOMME.SI et en utilisant des formules SOMME.SI avec des critères de tableau, comme illustré dans les exemples ci-dessous.

Fonction Excel SOMME.SI.ENS - syntaxe et utilisation

SOMME.SI.ENS est une fonction qui permet d’additionner des cellules qui répondent à plusieurs critères. SOMME.SI.ENS peut être utilisé pour additionner des valeurs lorsque les cellules adjacentes répondent à des critères basés sur des dates, des nombres et du texte. Elle prend en charge les opérateurs logiques (>, , =) et les caractères génériques (* ,?) pour la correspondance partielle.

Par rapport à SOMME.SI, la syntaxe SOMME.SI.ENS est un peu plus complexe:

=SOMME.SI.ENS(somme_plage; plage1; critère1; [plage2]; [critère2]; ...)

Les 3 premiers arguments sont obligatoires, les plages supplémentaires et leurs critères associés sont facultatifs.

  • Somme_plage - La plage à additionner.
  • plage1 - La première plage de cellules à évaluer.
  • critères1 - Les critères à utiliser sur plage1.
  • plage2 - [facultatif] La deuxième plage à évaluer.
  • critères2 - [facultatif] Les critères à utiliser sur plage2.

La fonction SOMME.SI.ENS d'Excel additionne les cellules dans une plage en utilisant les critères fournis. Contrairement à la fonction SOMME.SI, SOMME.SI.ENS peut appliquer plusieurs ensembles de critères, avec plusieurs plages. La première plage est la plage à additionner. Les critères sont fournis par paires (plage / critères) et seule la première paire est requise. Pour des critères supplémentaires, fournissez une nouvelle paire plage / critères. Jusqu'à 127 paires plage / critères sont autorisées.

Prenons un exemple pour comprendre comment utiliser la fonction SOMME.SI.ENS.

Supposons que vous ayez un tableau répertoriant les lots de fruits provenant de différents fournisseurs. Vous avez les noms des fruits dans la colonne A, les noms des fournisseurs dans la colonne B et la quantité dans la colonne C. L’objectif est de trouver une somme des montants relatifs à un fruit et à un fournisseur donnés, par ex. toutes les bananes fournies par Ahmed.

Donc, pour commencer, définissons tous les arguments de notre formule SOMME.SI.ENS:

  • somme_plage : C2: C8.
  • plage1 : A2: A8.
  •  critère1 : "bananes".
  • plage2 : B2: B8.
  • critère2 : "Ahmed".

Assemblez maintenant les paramètres ci-dessus et vous obtiendrez la formule SOMME.SI.ENS suivante:

=SOMME.SI.ENS(C2:C8; A2:A8; "bananes"; B2:B8; "Ahmed")

Fonction SOMME.SI avec plusieurs critères basés sur le OU logique

Comment additionner des valeurs égales à l'un des nombreux critères potentiels ? Vous devez utiliser une condition OU.

Pour une telle tâche vous ne pouvez pas utiliser SOMME.SI.ENS car cette fonction additionne uniquement les valeurs qui remplissent toutes les conditions qu'elle contient.

Méthode 1 : Ajout de plusieurs fonctions SOMME.SI

Si vous souhaitez additionner des nombres qui répondent à l'un des critères (OU logique) de plusieurs critères, vous devez résumer deux ou plusieurs fonctions SOMME.SI dans une seule formule. Supposons que vous souhaitiez additionner les montants relatifs aux produits « Pommes » et « Bananes » en utilisant le OU logique, alors vous devez résumer deux fonctions SOMME.SI dans une seule formule en utilisant le modèle suivant :

=SOMME.SI(plage; critères1; somme_plage) + SOMME.SI(plage; critères2; somme_plage)

Notre formule est donc comme suit :

=SOMME.SI(A2:A8; "bananes"; C2:C8) + SOMME.SI(A2:A8; "pommes"; C2:C8)

Vous pouvez également utiliser les références des cellules E1 (bananes) et E2 (pommes):

=SOMME.SI(A2:A8; E1; C2:C8) + SOMME.SI(A2:A8;E2; C2:C8)

Comme vous le voyez, la première fonction SOMME.SI ajoute les quantités correspondant à "bananes", l'autre fonction SOMME.SI renvoie les montants relatifs à "pommes" puis vous ajoutez ces 2 nombres.

Méthode 2 : Utilisation de SOMME et SOMME.SI avec une constante de tableau

La solution ci-dessus est très simple et peut effectuer le travail souhaité rapidement lorsqu'il n'y a que quelques critères. Mais une formule SOMME.SI + SOMME.SI peut grandir énormément si vous voulez additionner des valeurs avec plusieurs conditions OU. Dans ce cas, une meilleure approche consiste à utiliser un tableau de critères dans la fonction SOMME.SI.

Ici, vous fournissez plus d'une valeur de critères en utilisant une constante de tableau dans l'argument critère de la fonction SOMME.SI. Une constante de tableau est une liste de valeurs de critères, séparées par des points-virgules, et cette liste de valeurs est placée entre crochets.

Comme vous avez plus d'une valeur comme critère dans la constante du tableau, la fonction SOMME.SI renverra un résultat pour chaque valeur de critère dans le tableau. Par conséquent, nous encapsulons enfin la fonction SOMME.SI dans la fonction SOMME pour additionner les résultats de chaque critère basé sur les critères SOMME.SI.

Dans l'exemple précédent, si vous souhaitez additionner les quantités des Oranges, Kiwi et Fraise, votre tableau de critères ressemblera à {"Oranges" ; "Kiwi"; "Fraise"}. Et la fonction SOMME.SI complète est:

=SOMME(SOMME.SI(A2:A8; {"Oranges" ; "Kiwi"; "Fraise"};C2:C8))

Notez que les valeurs des critères dans la constante du tableau sont fournies directement et non pas comme référence de cellule. Les valeurs de texte sont fournies entre guillemets doubles et les valeurs numériques sont fournies sans guillemets doubles.

Comme vous le voyez, un tableau de critères rend la formule beaucoup plus compacte que SOMME.SI + SOMME.SI et permet d'ajouter autant de valeurs que vous le souhaitez dans le tableau.

Cette méthode fonctionne avec les nombres ainsi qu'avec les valeurs de texte. Par exemple, si au lieu des noms des fruits dans la colonne A, vous aviez des ID produits comme 1, 2, 3, etc., alors votre formule SOMME.SI ressemblerait à ceci:

=SOMME(SOMME.SI(A2:A8; {1;2;3}; C2:C8))

Note : Contrairement aux valeurs de texte, les nombres n'ont pas besoin d'être placés entre guillemets doubles dans les arguments de tableau.

Méthode 3 : Utilisation de SOMME.SI avec la fonction SOMMEPROD

Si vous souhaitez répertorier les valeurs des critères dans les références de cellule plutôt que de les fournir directement dans la formule, vous pouvez encapsuler la fonction SOMME.SI dans la fonction SOMMEPROD. Les valeurs des critères sont répertoriées dans la plage de cellules. Vous fournissez donc cette plage dans l'argument critère de la fonction SOMME.SI.

La fonction SOMME.SI renvoie le résultat pour chaque valeur de critère sous la forme d'un tableau de valeurs et la fonction SOMMEPROD renvoie la somme de ces valeurs résultantes en fonction de SOMME.SI.

Pour le même exemple, les valeurs des produits sont répertoriées dans la plage E1:E3. Cette plage est fournie comme argument de critère à la fonction SOMME.SI :

=SOMMEPROD(SOMME.SI(A2:A8;E1:E3; C2:C8))

Fonction SOMME.SI.ENS avec plusieurs critères basés sur le OU logique

Si vous souhaitez additionner conditionnellement des valeurs dans Excel, non seulement avec plusieurs conditions OU, mais avec plusieurs ensembles de conditions, vous devrez utiliser SOMME.SI.ENS au lieu de SOMME.SI. Les formules seront très similaires à ce que nous venons de voir.

Dans notre tableau des fournisseurs de fruits, ajoutons la date de livraison (colonne D) et trouvons la « Quantité » totale livrée par « Ahmed, Karim et Hicham » en « Mai ».

Méthode 1: SOMME.SI.ENS + SOMME.SI.ENS

La formule produite par cette méthode comprend beaucoup de répétitions et semble lourde, mais elle est facile à comprendre et, surtout, elle fonctionne !

=SOMME.SI.ENS(C2:C8;B2:B8; "Ahmed"; D2:D8; ">=01/05/2020"; D2:D8; "

SOMME.SI.ENS(C2:C8;B2:B8; "Karim"; D2:D8; ">=01/05/2020"; D2:D8; "

SOMME.SI.ENS(C2:C8;B2:B8; "Hicham"; D2:D8; ">=01/05/2020"; D2:D8; "

Comme vous le voyez, vous écrivez une fonction SOMME.SI.ENS distincte pour chacun des fournisseurs et incluez deux conditions - égales ou supérieures à 1-Mai (">=01/05/2020",) et inférieures ou égales à 31-Mai ("

Méthode 2 : SOMME et SOMME.SI.ENS avec un tableau de critères

La fonction SOMME.SI.ENS par défaut contient plusieurs critères basés sur le ET logique. Cependant, pour additionner les nombres basés sur plusieurs critères à l'aide du OU logique, vous devez utiliser la fonction SOMME.SI.ENS avec une constante de tableau.

Une constante de tableau est un ensemble de critères multiples fournis entre accolades {} dans une formule, comme : {"Critères1" ; "critères2"; "critères3"}.

La constante de tableau à l'aide du OU logique force la fonction SOMME.SI.ENS à additionner les nombres en fonction de l'un ou l'autre des multiples critères et enfin la fonction SOMME additionne ces résultats. La formule résultante est plus compacte que SOMME.SI.ENS + SOMME.SI.ENS:

=SOMME(SOMME.SI.ENS(somme_plage; critères_plage ; {"critères1" ; "critères2" ; "critères3"})))

Supposons que vous souhaitiez additionner les montants des livraisons pour l'un des produits «Oranges» et «Bananes» fournis en tant que critères dans la constante du tableau, vous devez alors fournir plusieurs critères dans la fonction SOMME.SI.ENS comme suit :

=SOMME(SOMME.SI.ENS(C2:C8; A2:A8; {"Oranges"; "Bananes"}))

N'oubliez pas que vous ne pouvez pas utiliser une expression ou une référence de cellule dans une constante de tableau.

Ici, la constante de tableau force la fonction SOMME.SI.ENS à générer le résultat dans un tableau, comme suit :

=SOMME({250 ; 520})

Enfin, la fonction SOMME additionne ces résultats de tableau pour obtenir le chiffre total comme indiqué ci-dessus.

Utiliser la fonction SOMME dans des formules matricielles

Comme vous vous en souvenez, Microsoft a implémenté la fonction SOMME.SI.ENS dans Excel 2007. Si vous utilisez toujours Excel 2003, 2000 ou une version antérieure, vous devrez utiliser une formule matricielle SOMME pour additionner des valeurs avec plusieurs critères ET. Naturellement, cette approche fonctionne également dans les versions modernes d'Excel 2013-2007 et peut être considérée comme une contrepartie à l'ancienne de la fonction SOMME.SI.ENS.

Dans les formules SOMME.SI décrites ci-dessus, vous avez déjà utilisé des arguments de tableau, mais une formule matricielle est quelque chose de différent.

Exemple 1 : Somme avec plusieurs critères ET dans Excel 2003 et versions antérieures

Revenons au tout premier exemple où nous avons découvert une somme de montants relatifs à un fruit et à un fournisseur donnés:

Comme vous le savez déjà, cette tâche est facilement accomplie en utilisant une formule « SOMME.SI.ENS » ordinaire :

=SOMME.SI.ENS(C2:C8; A2:A8; "bananes"; B2:B8; "Ahmed")

Et maintenant, voyons comment la même tâche peut être accomplie dans les premières versions d'Excel (sans SOMME.SI.ENS).

  1. Tout d'abord, notez toutes les conditions qui doivent être remplies sous la forme de plage = "condition". Dans cet exemple, nous avons deux paires plage / condition:
  • Condition 1 : A2: A8 = "Bananes".
  • Condition 2 : B2: B8 = "Ahmed".
  1. Ensuite, écrivez une formule SOMME qui "multiplie" toutes vos paires plage / condition, chacune entre guillemets. Le dernier multiplicateur est la plage de somme, C2:C8 dans notre cas :

=SOMME((A2:A8="Bananes") * (B2:B8= "Ahmed") * (C2:C8))

Comme illustré dans la capture d'écran ci-dessus, la formule fonctionne parfaitement dans la dernière version d'Excel 2013.

Lorsque vous entrez une formule matricielle, vous devez appuyer sur Ctrl + Shift + Entrée. Une fois que vous le faites, votre formule est placée entre {accolades}, qui est une indication visuelle qu'une formule matricielle est entrée correctement. Si vous essayez de taper les accolades manuellement, votre formule sera convertie en chaîne de texte et ne fonctionnera pas comme fonction.

Exemple 2 : Formule matricielle avec SOMME dans les versions Excel modernes

Même dans les versions modernes d'Excel 2016, 2013, 2010 ou 2007, la puissance de la fonction SOMME ne doit pas être sous-estimée. La formule matricielle SOMME n'est pas simplement une gymnastique de l'esprit, mais a une valeur pratique, comme le montre l'exemple suivant.

Supposons que vous ayez deux colonnes, A et B, et que vous devez compter le nombre de fois où la colonne B est supérieure à la colonne A, lorsqu'une valeur dans la colonne B est supérieure ou égale à 10. Une solution immédiate qui vient à l'esprit est d'utiliser la Formule matricielle SOMME :

=SOMME((C1:C9>= 10) * (C1:C9 > B1:B9))

Vous ne voyez pas encore le côté pratique de la formule ci-dessus ? Examinons-là dans un autre exemple.

Supposons que vous ayez une liste des commandes (comme indiqué dans la capture d'écran ci-dessous) et que vous souhaitiez savoir combien de produits n'ont pas été entièrement livrés à une date donnée. En traduisant cela dans la langue d'Excel, nous avons les conditions suivantes :

  • Condition 1 : une valeur dans la colonne B (articles commandés) qui doit être supérieure à 0.
  • Condition 2 : une valeur dans la colonne C (livrés) qui doit être inférieure à celle de la colonne B.
  • Condition 3 : une date dans la colonne D (date d'échéance) qui doit être inférieure au 25/06/2020.

En réunissant les trois paires « plage / condition », vous obtenez la formule suivante :

=SOMME((B2:B8>0)*( C2:C8

Télécharger le fichier Excel de ce tutoriel

Article publié le 20 Juillet 2020par Hanane Mouqqadim