La fonction SOMME.SI est l’une des fonctions les plus intéressantes sur Excel. Elle permet d’effectuer la somme sur une plage de données en fonction d’un critère sur cette même plage ou sur une autre plage de même dimension.
Dès que l’on veut faire une SOMME.SI plusieurs critères, il existe d’autres solutions. La première qui vient à l’esprit est la solution SOMME.SI.ENS. Cette fonction permet de faire la somme d’une plage de données en fonction de plusieurs critères que l’on vient cumuler. Elle répond à la majorité des besoins rencontrés. Cependant, il reste quelques cas où il faudra aller plus loin.
Dans ce tutoriel, je vais aborder les solutions pour faire des sommes avec plusieurs critères. Nous verrons notamment comment mettre des conditions OU dans nos formules.
La fonction SOMME.SI
Syntaxe
La fonction SOMME.SI permet de faire la somme d’une plage de données en fonction d’un seul et unique critère. Ce critère peut s’appliquer sur les cellules qui nous voulons additionner ou sur d’autres cellules du même tableau.
La syntaxe de la fonction SOMME.SI est la suivante :
= SOMME.SI ( plage ; critère ; somme_plage )
Les arguments sont :
- plage : les coordonnées des cellules sur lesquelles nous voulons appliquer un critère
- critère : la condition que l’on souhaite appliquer
- somme_plage : plage de données que l’on souhaite additionner
Exemple simple
Tout au long de ce tutoriel, nous allons travailler sur un tableau comprenant une liste de salariés avec leurs rémunérations, leurs dates d’entrée dans l’entreprise et le service.
Dans ce premier exemple, nous voulons la somme des salaires du service production. Nous allons utiliser la fonction SOMME.SI :
= SOMME.SI ( E3:E27 ; “Production” ; C3:C27 )
Nous voulons faire la somme des cellules dans C3:C27
en fonction de leurs valeurs en E3:E27
. Pour être précis, nous voulons les lignes où apparaissent les valeurs “Production”.
La fonction SOMME.SI.ENS
Syntaxe
La fonction SOMME.SI.ENS permet de faire la somme d’une plage de données en fonction d’un ou plusieurs critères. Sa syntaxe est légèrement différente de celle de la SOMME.SI mais reste très simple à lire et à utiliser :
= SOMME.SI.ENS ( plage_somme ; plage_critère1 ; critère1 ; … )
Les 3 premiers arguments sont obligatoires tandis que les suivants sont optionnels :
- plage_somme : plage de cellules que nous souhaitons additionner
- plage_critère1 : la première plage de cellules sur laquelle nous souhaitons appliquer un critère
- critère1 : le critère que nous souhaitons appliquer
Ensuite, pour ajouter d’autres critères, il suffit d’ajouter tour à tour une plage de cellules sur laquelle nous souhaitons appliquer une nouvelle condition puis la condition.
Attention, il faut que toutes vos plages de cellules aient la même dimension (même nombre de lignes).
La fonction SOMME.SI.ENS fonctionne en considérant que tous les critères sont cumulatifs. C’est-à-dire qu’elle n’additionne les valeurs dont les lignes remplissent tous les critères.
Exemple simple
Reprenons notre liste de salariés. Nous souhaitons à présent obtenir la rémunération totale des salariés arrivés après le 1er Janvier 2022 et faisant partie du service Production. La syntaxe à saisir dans la barre de formule sera la suivante :
= SOMME.SI.ENS ( C3:C27 ; D3:D27 ; ">=" & DATE(2022;1;1) ; E3:E27 ; "Production" )
Regardons de plus près les arguments saisis :
- plage_somme : C3:C27
- plage_critère1 : D3:D27
- critère1 : “>=”&DATE(2022;1;1)
- plage_critère2 : E3:E27
- critère2 : “Production”
Syntaxe des conditions
Voici un rappel que je trouve bien pratique. Il s’agit d’orthographier correctement les critères que vous allez taper dans les SOMME.SI et les SOMME.SI.ENS.
Les conditions peuvent s’appliquer sur du texte, des nombres et des dates.
Les conditions pour du texte :
- “Production” : la valeur est égale à Production
- “<>Production” : la valeur est différente de Production
- “*Production*” : la valeur contient Production
- “Production*” : la valeur commence par Production
- “*Production” : la valeur se termine par Production
Les conditions pour des dates :
- “>”&DATE(2022;1;1) : Supérieur au 01/01/2022
- “>=”&DATE(2022;1;1) : Supérieur ou égal au 01/01/2022
- “<”&DATE(2022;1;1) : Inférieur au 01/01/2022
- “<=”&DATE(2022;1;1) : Inférieur ou égalau 01/01/2022
- DATE(2022;1;1) : date exacte
Les conditions pour des nombres :
- “>”&2000 : Supérieur à 2000
- “>=”&2000 : Supérieur ou égal à 2000
- “<”&2000 : Inférieur à 2000
- “<=”&2000 : Inférieur ou égal à 2000
- 2000 : montant exact
Les autres conditions :
- “=” : cellule vide (pas de formule ni de valeur nulle)
- “” : valeur nulle (peut être le résultat d’une formule)
- “<>” : cellule non vide (inclus zéro)
Comment utiliser la fonction SOMME.SI.ENS pour obtenir une SOMME.SI plusieurs critères ?
Comme dans l’exemple cité plus haut, nous allons voir d’autres exemples de l’utilisation de la fonction SOMME.SI.ENS avec d’autres types de format et des conditions plus complexes.
Exemple 1 – Avec des conditions sur des nombres
Nous allons faire la somme dans notre tableau en fonction des deux critères suivants :
- Rémunération supérieure ou égale à 1800€
- Appartenant au service Production
Pour cela, la syntaxe de la formule sera la suivante :
=SOMME.SI.ENS(C3:C27;C3:C27;">="&1800;E3:E27;"Production")
A noter que le critère appliqué sur le montant a été écrit de la façon suivante “>=”&1800
. J’aurais pu égalemen l’écrire comme ceci “>=1800”.
Cependant, je préfère la première syntaxe car elle invite plus facilement à appeler la valeur contenue dans une cellule. Ex: “>=” &C1
où C1 contriendrait la valeur 1800.
Exemple 2 – Avec des critères sur des dates
Nous avons déjà pris un exemple sur les dates. Nous allons le complexifier en additionnant les salaires de tous les salariés arrivés en 2022. C’est-à-dire que je veux appliquer les 2 conditions suivantes :
- date d’entrée supérieure ou égale au 01/01/2022
- date d’entrée inférieure ou égale au 31/12/2022
La syntaxe de la formule sera la suivante :
=SOMME.SI.ENS(C3:C27;D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31))
A noter que si vous souhaitez indiquer des critères en comparant les valeurs par rapport à la date du jour, il vous faudra utiliser la fonction AUJOURDHUI().
Exemple 3 – Avec des cellules vides
Il peut arriver que vous deviez agir avec des plages de données comportant des cellules vides. Pour cela, je vais supprimer quelques Services dans ma base de données. Mon objectif sera alors de faire une SOMME.SI.ENS sur les salaires des individus n’ayant pas de service et arrivés en 2022.
Voici la syntaxe de la formule :
=SOMME.SI.ENS(C3:C27;E3:E27;"";D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31))
Faire une SOMME.SI avec plusieurs critères (critère 1 ou critère 2 ou…)
Jusqu’à présent nous avons vu des solutions pour cumuler les critères entre eux. Mais que se passe-t-il si nous voulons le critère 1 ou le critère 2. Par exemple, que puis-je faire sur je veux les rémunérations du service Production ou du service Logistique. Heureusement, plusieurs options s’offrent à nous.
Cumuler les SOMME.SI
La solution la plus simple reste de placer plusieurs fonctions SOMME.SI ou SOMME.SI.ENS dans votre barre de formule. Par exemple, si je veux calculer la somme des salaires des individus faisant partie du service logistique et production :
=SOMME.SI(E3:E27;"Production";C3:C27)+SOMME.SI(E3:E27;"Logistique";C3:C27)
Utiliser la fonction SOMME.SI et SOMME avec un argument matriciel
La solution précédente permet d’obtenir facilement le résultat escompté. Cependant, dès lors que les critères s’accumulent, elle devient moins facile à lire.
Il existe une solution pour indiquer tous les critères directement dans la formule. La solution est l’utilisation d’un argument matriciel. Pour cela, réunissez l’ensemble de vos conditions en les séparant par des points virgules. Le tout doit être placé entre crochets. C’est la façon qu’à Excel de comprendre qu’il s’agit d’un élément matriciel :
{“Production”;”Logistique”}
Le tout doit être placé dans une formule avec la syntaxe suivante :
=SOMME(SOMME.SI(E3:E27; {"Production";"Logistique"};C3:C27))
Je dois vous avouer que je me suis personnellement peu servi de cette solution. Si la première solution présentée ne suffit pas, je vais directement appliquer la fonction SOMMEPROD. C’est justement la solution que je vais aborder à présent.
Utiliser SOMMEPROD pour faire une SOMME.SI plusieurs critères
La solution SOMMEPROD permet de faire appel aux différents critères lorsqu’ils sont contenus dans des cellules spécifiques. La syntaxe de la formule sera :
=SOMMEPROD(SOMME.SI(E3:E27;G9:G10;C3:C27))
Dans cette formule, les coordonnées G9:G10
sont les cellules dans lesquelles j’ai indiqué les différents services (“production” et “logistique”). De plus, contrairement à la solution précédente, vous n’avez pas besoin de chercher comme faire les crochets “{ }” avec votre clavier.
Voici le résultat :
Faire une SOMME.SI.ENS avec critère 1 ou critère 2 ou… (plusieurs conditions OU)
Maintenant que nous avons vu comment faire des SOMME.SI avec des conditions OU, voyons comment cela se passe avec la fonction SOMME.SI.ENS.
Cumuler les SOMME.SI.ENS
Les solutions les plus simples sont toujours les meilleures. C’est effectivement ce que je vous conseille, si vous voulez cumuler les conditions OU, préférez la solution de simplicité. Par exemple, si je veux la somme des rémunérations des salariés arrivés en 2022 et faisant partie du service Logistique ou Production, il me suffit de mettre dans la même formule deux fonctions SOMME.SI.ENS.
= SOMME.SI.ENS(C3:C27;D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;"Logistique")
+SOMME.SI.ENS(C3:C27;D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;"Production")
Dans ce cas, nous n’avons que deux possibilités et il est plus simple d’utiliser ce type de technique. Dès lors que nous aurons 3-4-5 voire bien plus de critères possibles, il vaudra mieux passer sur les solutions suivantes.
Utiliser la fonction SOMME.SI.ENS et SOMME avec un argument matriciel
Ici on vient appliquer la même solution qu’exposé dans la section “Utiliser la fonction SOMME.SI et SOMME avec un argument matriciel”. La lecture de la formule devient plus simple même si, j’en conviens, il faut un peu d’expérience pour l’interpréter…
=SOMME.SI.ENS(C3:C27;D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;"Logistique")
Utiliser SOMMEPROD pour faire une SOMME.SI.ENS avec des conditions OU
Tout comme avec la fonction SOMME.SI, il est possible d’utiliser la fonction SOMMEPROD avec la fonction SOMME.SI.ENS de façon à appeler une liste de conditions directement détenues dans des cellules. Voici la syntaxe :
=SOMMEPROD(SOMME.SI.ENS(C3:C27;D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;I7:I8))
En résumé
Il est parfaitement faisable de faire une SOMME.SI avec plusieurs critères. La solution la plus simple est alors de faire une SOMME.SI.ENS. Les choses viennent se complexifier seulement quand vous voulez ajouter des conditions de type OU. Fort heureusement, la solution de facilité est de mettre bout à bout plusieurs SOMME.SI ou plusieurs SOMME.SI.ENS.
Formules en lien
- SOMME de RECHERCHEV
- Additionner les cellules contenant une formule
- Compter nombre de valeurs texte différentes
- Compter le nombre de cellules égales à un texte
- Compter le nombre de cellules contenant du texte
- Compter le nombre de cellules non vides
- NB.SI avec plusieurs critères
- Somme si non vide
- Somme si vide
- SOMME.SI.ENS avec plusieurs critères sur la même colonne
- SOMMEPROD avec SI
- Somme si différent de
- Somme si contient
- Somme si date est entre deux dates
- MIN.SI.ENS sensible à la casse
- MAX sensible à la casse
- MAX.SI.ENS sensible à la casse
- Arrondir à la dizaine