Vous devez apprendre à utiliser SOMME.SI ou SOMME.SI.ENS dans Google Sheets si vous souhaitez faire la somme de nombres en fonction d'un critère.
Le problème avec la fonction SOMME.SI est qu'elle ne vous permet de ne préciser qu'un seul critère.
Et dans de nombreux cas, un seul critère ne suffit pas. Nous devons souvent calculer des totaux basés sur plusieurs critères. Dans de tels cas, parmi toutes les formules disponibles dans Google Sheets, la fonction SOMME.SI.ENS s'avère très pratique.
Dans ce guide, je vais vous montrer comment utiliser la fonction SOMME.SI.ENS dans Google Sheets à l'aide de quelques exemples pratiques.
À quoi sert la fonction SOMME.SI.ENS de Google Sheets ?
En termes simples, la fonction SOMME.SI.ENS calcule la somme des valeurs qui satisfont plusieurs critères. Contrairement à la fonction SOMME.SI de Google Sheets qui ne peut traiter qu'un seul critère.
La fonction SOMME.SI.ENS parcourt une plage de cellules et récupère celles qui correspondent à tous les critères spécifiés.
Une fois la plage parcourue, la fonction fait la somme des valeurs récupérées et affiche le résultat.
Pour vous donner un exemple, si vous avez une liste des ventes qui ont été réalisées et que vous souhaitez connaître la somme de toutes les transactions dans une plage de dates précises, vous pouvez le faire avec SOMME.SI.ENS.
Syntaxe de SOMME.SI.ENS dans Google Sheets
La syntaxe de la fonction SOMME.SI.ENS est :
=SOMME.SI.ENS(somme_plage; plage_critères1; critère1; [ plage_critères2; …]; [critère2; ...])
Voici une explication des paramètres de la syntaxe :
- somme_plage est la plage de cellules contenant les valeurs que vous souhaitez tester.
- plage_critères1 est la plage pour vérifier le critère1.
- critère1 est le critère que plage_critères1 doit satisfaire.
- plage_critères2, critère2, etc. sont des plages et les critères supplémentaires à vérifier.
Vous pouvez ajouter autant de critères que nécessaire.
Cependant, il va sans dire qu'il doit y avoir au moins une plage de critères et un paramètre de critère dans la fonction.
Fonctionnement de SOMME.SI.ENS dans Google Sheets
Voici quelques éléments à garder à l’esprit lorsque vous faites un calcul avec SOMME.SI.ENS :
- SOMME.SI.ENS est destiné à être utilisé avec plusieurs critères. Utilisez la fonction SOMME.SI pour les calculs avec une seule critères ;
- vous pouvez utiliser du texte, des dates et des chiffres comme critères ;
- vous pouvez utiliser des caractères génériques (?,*,~) avec les calculs SOMME.SI.ENS ;
- vous pouvez utiliser des opérateurs avec la fonction SOMME.SI.ENS ;
- les plages supplémentaires doivent avoir des lignes et des colonnes correspondantes comme somme_plage ;
- vous ne pouvez pas utiliser la fonction ARRAYFORMULA avec une fonction SOMME.SI.ENS ;
- les chaînes de caractère doivent être entre guillemets, les références de cellules ne doivent pas l'être.
Comment utiliser la fonction SOMME.SI.ENS dans Google Sheets
La syntaxe de la fonction deviendra plus claire une fois que je vous aurai montré quelques exemples.
Dans le tableau ci-dessous, il y a 9 salariés, ainsi que les départements (ou services) dans lesquels ils exercent leur activité, leur département géographique, leur date d'embauche, le nombre d'heures travaillées et le montant des commandes qui proviennent de leurs ventes.
Je vais vous montrer quelques scénarios à partir de ces données.
SOMME.SI.ENS dans Google Sheets : additionner avec plusieurs critères
Vous pouvez faire des opérations avec plusieurs critères dans Google Sheet avec la fonction SOMME.SI.ENS. Supposons que vous souhaitez calculer le nombre total d'heures travaillées par tous les employés du département commercial dans le Morbihan.
Dans ce cas, nous avons deux critères :
- le département dans l’entreprise : commercial
- le département géographique : le morbihan
Les paramètres de la fonction SOMME.SI.ENS seront donc les suivants :
- somme_plage inclut les cellules aux emplacements E2:E10 : les heures travaillées ;
- plage_critères1 inclut la plage de cellules B2:B10 : le département dans la société ;
- le critère1 est “Commercial”, puisque nous voulons sélectionner les cellules où le département géographique = “Commercial” ;
- plage_critères2 inclut la plage de cellules C2:C10 : le département géographique ;
- Le critère2 est “Morbihan”, puisque nous voulons sélectionner les cellules où le département géographique = “Morbihan”.
Par conséquent, vous pouvez taper la formule suivante dans la barre de formule :
=SOMME.SI.ENS(E2:E10; B2:B10; "Commercial"; C2:C10; "Morbihan")
Voici ce que vous obtenez comme résultat :
Explication de la formule avec SOMME.SI.ENS dans Google Sheets
Dans le cas ci-dessus, la fonction SOMME.SI.ENS a vérifié chaque cellule de B2 à B10 et C2 à C10 pour trouver des cellules qui satisfont aux deux critères : “Commercial” et “Morbihan”.
Pour chaque ligne correspondante, la fonction a sélectionné la valeur des heures travaillées correspondante dans la colonne E.
Il a ensuite additionné toutes les nombres d'heures travaillées sélectionnées et a affiché le résultat total dans la cellule D14.
Utiliser SOMME.SI.ENS avec un critère de date
Je vous montre maintenant comment ajouter un critère supplémentaire. Je souhaite que la date d’embauche de l’employé soit antérieure au 1er juillet 2023.
Nous avons donc maintenant trois critères :
- département = “Commercial” ;
- département géographique = “Morbihan” ;
- date d'embauche < 01/07/2023.
Cela signifie que nous devons ajouter deux paramètres (ou arguments) supplémentaires à la fonction SOMME.SI.ENS :
- plage_critères3 inclut la plage de cellules D2:D10 : la date d'embauche ;
- le critère3 va être "<01/07/2023", puisque nous voulons sélectionner les cellules dont la date d'adhésion < "01/07/2023"
Vous pouvez donc saisir la formule suivante dans la barre de formule (notez les 2 derniers paramètres qui ont maintenant été ajoutés) :
=SOMME.SI.ENS(E2:E10; B2:B10; “Commercial”; C2:C10; “Morbihan”; D2:D10; “< 01/07/2020”)
Voici le résultat que vous obtenez :
Explication de la formule SOMME.SI.ENS avec 3 critères
Dans l'exemple ci-dessus avec des critères multiples dans SOMME.SI.ENS de Google Sheets, la fonction a vérifié chaque cellule de B2 à B10, C2 à C10 et D2 à D10 pour trouver les cellules qui satisfont aux trois critères : “Commercial”, “Morbihan” et “<01/07/2023” respectivement.
Pour chaque ligne correspondante, la fonction a sélectionné la valeur des heures travaillées correspondante dans la colonne E.
Elle a ensuite additionné le nombre d'heures travaillées sélectionnées et affiché le résultat dans la cellule D15.
Utiliser SOMME.SI.ENS avec un critère numérique
Admettons que vous souhaitez connaître les ventes totales réalisées par les employés du département commercial qui ont travaillé 20 heures ou plus.
Vous avez deux critères :
- département = “Commercial” ;
- heures travaillées >= 20.
Dans ce cas, les paramètres de la fonction SOMME.SI.ENS seront les suivants :
- puisque vous voulez maintenant obtenir le total des ventes, somme_plage inclura les cellules aux emplacements F2:F10 ;
- plage_critères1 inclura les emplacements de cellules B2:B10 (le département) ;
- le critère1 sera “Commercial”, puisque nous voulons sélectionner les cellules où département = “Commercial” ;
- plage_critères2 inclura les emplacements de cellules E2:E10 (les heures travaillées) ;
- le critère2 sera “>=20”, puisque nous voulons sélectionner des cellules où les heures travaillées >= 20.
Il vous suffit maintenant de saisir la formule suivante dans la barre de formule :
=SOMME.SI.ENS(F2:F10; B2:B10; "Commercial"; E2:E10; ">=20")
Voici le résultat que vous obtenez :
Explication de la formule
Dans le cas ci-dessus, la fonction SOMME.SI.ENS a vérifié chaque cellule de B2 à B10 et E2 à E10 pour trouver les cellules qui satisfont aux deux critères : Commercial et >=20 respectivement.
Pour chaque ligne correspondante, la fonction a sélectionné la données des ventes correspondante dans la colonne F.
Elle a ensuite additionné toutes les données de ventes sélectionnées et a affiché le résultat dans la cellule D14.
Plusieurs critères dans une même colonne avec SOMME.SI.ENS
Malheureusement, Google Sheets ne permet pas d’utiliser SOMME.SI.ENS avec plusieurs critères dans une seule et même colonne. Ce n’est pas un problème : je vais vous montrer une solution de contournement.
Je prends l’exemple où vous avez prévu d’envoyer un bon d’achat aux clients qui étaient présents à un événement. Mais vous avez constaté qu’il y a eu au moins une erreur dans l’envoi des bons d’achats. Vous voulez donc vérifier quels clients ont reçu le cadeau par erreur. Vous avez donc créé une feuille de calcul similaire à celle que vous voyez ci-dessous.
Vous souhaitez inclure « Oui » et « A vérifier » comme critères pour votre calcul SOMME.SI.ENS. Mais si vous le faites, cela va générer un message d’erreur.
La façon la plus simple est d'utiliser la fonction SOMME.SI.ENS avec plusieurs critères dans une même colonne consiste à additionner deux formules différentes avec SOMME.SI.ENS. Dans cet exemple, il vous suffit de copier le même calcul deux fois mais avec une variante pour chaque calcul : un des calculs utilise « Oui » comme critère et l'autre calcul utilise « A vérifier » comme dans l’exemple ci-dessous :
=SOMME.SI.ENS(C2:C8; B2:B8; "Oui"; D2:D8; "Non") + SOMME.SI.ENS(C2:C8; B2:B8; "A vérifier"; D2:D8; "Non")
La fonction Google SOMME.SI.ENS gère plusieurs critères pour obtenir une réponse correcte finale avec cette méthode
Utiliser la fonction SOMME.SI.ENS avec des cellules vides et non vides
Vous pouvez utiliser la fonction SOMME.SI.ENS avec des opérateurs pour additionner les cellules si elles ne contiennent aucune donnée ou si elles en contiennent. Voici un exemple de formule pour rechercher des cellules vides en utilisant un espace entre guillemets :
=SOMME.SI.ENS(A3:A20; B3:B20; "Envoyé"; C3:C20; " ")
Cette formule fait le total de la plage de la colonne A (de la ligne 3 à la ligne 20) à condition que les 2 critères suivants soient remplis :
- la cellule de la colonne C (pour la ligne correspondante) doit être vide ;
- l’information saisie dans la colonne B doit être Envoyé.
Vous pouvez aussi appliquer la même logique et utiliser l'opérateur non vide (<>) pour additionner les cellules, comme ceci :
=SOMME.SI.ENS(A3:A20; B3:B20; "Envoyé"; C3:C20; "<>")
Utiliser des caractères génériques avec SOMME.SI.ENS
Comme je l'ai évoqué plus tôt, vous pouvez utiliser des caractères génériques dans les calculs SOMME.SI.ENS. Les caractères génériques sont des opérateurs que vous pouvez utiliser pour représenter n'importe quoi. Les deux caractères génériques les plus courants sont l’astérisque et le point d’interrogation.
Un astérisque indique une correspondance de zéro ou plus, tandis qu'un point d'interrogation représente exactement 1.
Par exemple, quand il est utilisé dans chaîne de texte, F* peut s’avérer être François, Feuille, Fruit, F, F6 ou tout autre texte qui commence par la lettre F.
Voilà comment cela fonctionne avec les points d’interrogations : je prends l’exemple de F??. Cela signifie que le texte doit comporter trois caractères et commencer par F. Cela peut correspondre à Fac, Fan ou F16.
Je vous montre quelques exemples de formules SOMME.SI.ENS avec des caractères génériques.
La formule suivante permet de rechercher et additionner les articles vendus avec une référence de produit commençant par ABC :
=SOMME.SI.ENS(A2:A15; B2:B15; "Vendus"; C2:C15; "ABC*")
Voici à quoi ressemble cette même recherche, mais que vous souhaitez que la référence du produit vendu commence par ABC et se compose d’exactement 5 caractères :
=SOMME.SI.ENS(A2:A15; B2:B15; "Vendus"; C2:C15; "ABC??")
Comment résoudre le message d'erreur le plus courant avec SOMME.SI.ENS
Vous recevez un message d’erreur si les plages que vous utilisez n’ont pas la même taille. La plage SOMME doit correspondre aux plages de recherche.
Je reprends l'exemple ci-dessus. La formule =SOMME.SI.ENS(A2:A15; B2:B15; "Vendus"; C2:C15; "ABC??") va fonctionner parce que les plages A2:A15, B2:B15 et C2:C15 sont toutes identiques en longueur.
Si je modifie l’une des plages en A1:A13, B2:B20 et C4:C10, la formule ne fonctionnerait plus.
Utiliser SOMME.SI.ENS avec des cases à cocher
Les cases à cocher représentent généralement 2 expressions logiques : VRAI ou FAUX. C'est pourquoi vous pouvez aussi appliquer SOMME.SI.ENS aux cases à cocher dans Google Sheets.
Admettons que vous souhaitez utiliser SOMME.SI.ENS si les cases qui concernent le département commercial sont cochées (voir l'exemple ci-dessous).
Dans ce cas, il faut comprendre que si une case est cochée la cellule qui contient cette case a une valeur “VRAI” et si la case est décochée la valeur est “FAUX”.
Voilà la formule que vous allez utiliser :
=SOMME.SI.ENS(C2:C10; B2:B10; "Commercial"; D2:D10; VRAI)
Cette formule donne le résultat suivant :
La formule comporte 2 critères, le premier critère concerne le département commercial dans la colonne B et le deuxième critère concerne les cases cochées et utilise l'expression “VRAI”.
A noter : une case qui est cochée est marquée avec la valeur “VRAI”. Ce n’est pas une donnée textuelle. Dans la formule SOMME.SI.ENS vous ne devez donc pas saisir de guillemets (regardez bien les copies d’écran pour voir comment la formule fonctionne).
Utiliser un opérateur logique
Vous pouvez utiliser les opérateurs logiques NON(), ET() et OU() pour travailler avec SOMME.SI.ENS. Ces 3 opérateurs logiques sont assez explicites :
- utilisez l'opérateur logique ET() si vous devez inclure une ou plusieurs correspondances à additionner ;
- utilisez l'opérateur logique OU() s'il peut s'agir d'une sélection ;
- utilisez NON() si vous souhaitez exclure les phrases sélectionnées.
Questions fréquentes sur SOMME.SI.ENS
Est-ce que SOMME.SI.ENS fonctionne dans Google Sheets ?
Oui, saisissez simplement =SOMME.SI.ENS( dans une cellule vide pour commencer à utiliser la fonction SOMME.SI.ENS dans Google Sheets.
A quoi sert la fonction SOMME.SI.ENS dans Google Sheets ?
SOMME.SI.ENS permet d’additionner les données lorsque plusieurs critères sont remplis.
La fonction SOMME.SI.ENS vous permet, par exemple, de faire le total d’un produit dans votre inventaire s'il a un stock faible et s’il n’y a pas de commande en cours (chaque critère doit être dans une colonne distincte).
Combien de critères SOMME.SI.ENS peut-il avoir dans Google Sheets ?
SOMME.SI.ENS peut avoir jusqu'à 127 critères dans Google Sheets.
Peut-on ajouter deux SOMME.SI.ENS ensemble ?
Au lieu d'utiliser l’opérateur OU dans SOMME.SI.ENS, vous devez utiliser deux fois la fonction SOMME.SI.ENS et les additionner. La réponse à cette question est donc oui ! Voici un exemple de la façon dont vous pouvez procéder :
=SOMME.SI.ENS(C2:C10; B2:B10; "Oui"; D2:D10; "Oui") + SOMME.SI.ENS(C2:C10; B2:B10; "A vérifier"; D2:D10; "Oui")
Comment utiliser SOMME.SI avec plusieurs critères dans Google Sheets ?
S'il y a plusieurs critères dans une colonne, vous devez additionner deux calculs SOMME.SI.ENS comme dans l'exemple ci-dessus. Si vos critères se trouvent dans différentes colonnes, vous pouvez ajouter chaque nouveau critère et plage à un calcul SOMME.SI.ENS comme ceci :
=SOMME.SI.ENS(E2:E10; B2:B10; "Commercial"; C2:C10; "Morbihan")
Les critères utilisés ici sont “Commercial” et “Morbihan”, et les plages à leur gauche sont les cellules à partir desquelles vous souhaitez que la formule SOMME.SI.ENS soit extraite.
Les points à retenir sur SOMME.SI.ENS
Lorsque vous utilisez la fonction SOMME.SI.ENS de Google Sheets, vous devez garder à l'esprit quelques points importants :
- la fonction SOMME.SI.ENS n'est pas sensible à la casse ;
- si le critère contient une valeur de texte, une date ou des caractères génériques, il doit alors être placé entre guillemets ;
- les références de cellules dans les critères ne sont pas placées entre guillemets ;
- vous devez avoir au moins 1 paire de paramètres plage_critère et critère dans votre fonction SOMME.SI.ENS.
La fonction SOMME.SI.ENS est si polyvalente et personnalisable que vous pouvez utiliser un très grand nombre de critères.
Je vous encourage à utiliser la fonction SOMME.SI.ENS de Google Sheets dans vos feuilles de calcul chaque fois que vous avez besoin de trouver la somme basée sur plusieurs critères.
