Tutoriel Excel : comment faire une somme avec condition

Ce tutoriel Excel explique comment utiliser la fonction SOMME.SI avec la syntaxe et des exemples pratiques.

Si vous êtes confronté à une tâche qui nécessite une somme conditionnelle dans Excel, la fonction SOMME.SI est ce dont vous avez besoin. Cette fonction est classée sous les fonctions Excel Math et trigonométrie. Elle résume les cellules qui répondent aux critères donnés. Les critères sont identifiés sur des dates, des nombres et du texte. Elle prend en charge les opérateurs logiques tels que (>, <, <>, =) ainsi que les caractères génériques (* ,?).

Les instructions de ce tutoriel concernent Excel 2013 pour Windows. Or, si vous avez une version différente, vous pouvez toujours suivre les mêmes étapes.

Table des matières

Fonction Excel SOMME.SI  - syntaxe et utilisation

Comment utiliser SOMME.SI  dans Excel - exemples de formules ?

Calculer la somme si la valeur est supérieures à

Comment additionner si la valeur est inférieure à ... ?

Formules SOMME.SI  avec des caractères génériques

Exemple 1. Somme des valeurs basées sur une correspondance partielle

Exemple 2. Somme des cellules correspondant aux valeurs de texte

Calculer la somme pour les cellules non vides

Comment additionner des cellules qui correspondent à des cellules vides ?

Comment additionner des valeurs dans plusieurs colonnes ?

Additionner les plus grands ou les plus petits nombres d'une plage

Fonction Excel SOMME.SI  - syntaxe et utilisation

La fonction SOMME.SI est utilisée pour résumer les valeurs de certaines cellules spécifiques d'une colonne, les cellules qui répondent à certains critères.

En tant qu'analyste financier, SOMME.SI est une fonction fréquemment utilisée. Supposons que vous recevez un tableau répertoriant les lots de légumes provenant de différents fournisseurs. Le nom du légume, le nom du fournisseur et la quantité respectivement dans la colonne A, la colonne B et la colonne C. Dans un tel scénario, vous pouvez utiliser la fonction SOMME.SI pour connaître la somme de la quantité liée à un légume particulier auprès d'un fournisseur spécifique.

La syntaxe de la fonction SOMME.SI est :

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

Ainsi, la fonction SOMME.SI comprend trois parties principales :

  • ·Plage :fait référence à la plage de cellules que vous souhaitez évaluer pour sélectionner les cellules qui répondent aux critères donnés.
  • ·Critères :il s'agit de conditions qui indiquent quelles cellules doivent être additionnées. Il peut s'agir d'un nombre ou d'un texte.
  • ·Somme_plage: fournit les cellules réelles à additionner. Il s'agit d'un argument facultatif. Si vous omettez cette partie de la fonction, la fonction SOMME.SI traite « Plage » comme « Somme_plage » et additionne ainsi les cellules de l'argument « Plage ».

Pour mieux illustrer la syntaxe de la fonction SOMME.SI, considérons l'exemple suivant. Supposons que vous ayez une liste de produits dans la colonne A, le fournisseur dans la colonne B et les quantités correspondantes dans la colonne C. Vous voulez connaître la somme de toutes les quantités relatives à un produit donné : Pommes, par ex.

Définissons maintenant les arguments de notre formule SOMME.SI :

  • ·Plage : A2:A8
  • ·Critères : "Pommes"
  • ·Somme_plage: C2:C8

Vous obtenez la formule ci-dessous:

 =SOMME.SI(A2:A8;"Pommes"; C2:C8)



Cet exemple de formule illustre l'utilisation la plus simple de la fonction SOMME.SI avec les critères de texte. Au lieu de texte, vous pouvez inclure un nombre, une date ou une référence de cellule dans vos critères. Par exemple, vous pouvez réécrire la formule ci-dessus afin qu'elle fasse référence à la cellule contenant le nom du produit à additionner :

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

Le paramètre Somme_plage ne spécifie en fait que la cellule supérieure la plus à gauche de la plage à additionner. La zone restante est définie par les dimensions de l'argument Plage.

En pratique, cela signifie que l'argument Somme_plage ne doit pas nécessairement être de la même taille que l'argument Plage. Il peut avoir un nombre différent de lignes et de colonnes. Cependant, la première cellule (c'est-à-dire la cellule en haut à gauche) de la plage de somme doit toujours être la bonne. Par exemple, dans la formule SOMME.SI ci-dessus, vous pouvez fournir C2, ou C2:C5, ou même C2:C200 comme argument Somme_Plage, et le résultat sera toujours le même. Cependant, la meilleure pratique consiste à fournir une « Plage » et une « Somme_plage » de tailles égales.

 

Comment utiliser SOMME.SI  dans Excel - exemples de formules ?

J'espère que l'exemple ci-dessus vous a aidé à acquérir une compréhension de base du fonctionnement de la fonction SOMME.SI. Vous trouverez ci-dessous quelques formules supplémentaires qui montrent comment utiliser SOMME.SI dans Excel avec différents critères et sur différents ensembles de données.

 

Calculer la somme si la valeur est supérieure à …

Excel vous permet d’additionner toutes les valeurs d'une table qui sont supérieures à la valeur sélectionnée en utilisant la fonction SOMME.SI.

Le tableau ci-dessous se compose de 3 colonnes : «Numéro de livraison» (colonne A), «Date de livraison» (colonne B) et «Montant» (colonne C). Dans la cellule E1, j’ai spécifié une limite inférieure, tandis que dans la cellule E2, je souhaite obtenir une somme de tous les montants supérieurs à la limite inférieure.

Pour additionner tous les montants de la colonne C qui sont supérieurs à la limite inférieure (500 DH), utilisez la formule ci-dessous :

=SOMME.SI(C2:C8; ">"&E1)

La plage est C2:C8, tandis que les critères sont ">"&E1.

Pour appliquer la fonction « SOMME.SI », vous devez suivre ces étapes:

  1. Sélectionnez la cellule E2 et cliquez dessus.
  2. Insérez la formule: =SOMME.SI(C2:C8; ">"&E1).
  3. Cliquezsur Entrée.

Comme vous pouvez le voir dans cet exemple, la formule additionne tous les montants supérieurs à 500 DH. Ainsi, les lignes 2 (1 000 DH), 4 (700 DH), 5 (650 DH) et 6 (1 200 DH) remplissent la condition, de sorte que les montants correspondants sont additionnés. Enfin, la somme dans la cellule E2 est de 3 550 DH.

Comment additionner si la valeur est inférieure à ?

Parfois, vous devez trouver la somme uniquement pour les valeurs qui sont inférieures à une limite particulière, ou souhaitez additionner des valeurs où la valeur des données correspondantes est inférieure à une limite donnée.

Pour une telle tâche, la formule à utiliser est comme suit :

=SOMME.SI(plage; "<=" &valeur; Somme_plage)

  • Plage: ensemble de valeurs
  • &: opérateur utilisé pour concaténer avec un autre opérateur.
  • Somme_plage: plage (facultative) où la somme est requise

Pour mieux comprendre cette fonction, utilisons-la dans un exemple.

Dans cet exemple, nous devons trouver la somme « Prix total » si la quantité est inférieure à 40.

Pour calculer la somme des prix qui répondent à cette condition, vous devez utiliser la formule suivante :

=SOMME.SI(B2:B7; "<="&F1; D2:D7)

  • B2:B7 : plage où la quantité est inférieure à 40.
  • "<=" &F1: critères, inférieurs à la valeur de F1 qui est 40.
  • D2: D7: la plage à additionner.

Saisissez la formule indiquée ci-dessus et appuyez sur Entrée.



Comme vous pouvez le voir, la formule renvoie 1388, le montant total du prix total.

Si les critères doivent être appliqués sur la plage de la somme, alors vous devez utiliser la formule suivante :

=SOMME.SI(D2:D7; "<=" & F1)

La plage est identique à Somme_plage, alors l'argument Somme_plage qui est facultatif n'est pas requis.

Formules SOMME.SI avec des caractères génériques

Si vous visez à additionner conditionnellement des cellules sur la base d'un critère de "texte" et que vous souhaitez additionner par correspondance partielle, vous devez utiliser des caractères génériques dans votre formule SOMME.SI.

Les caractères génériques qui sont à notre disposition sont les suivants :

  • Astérisque (*) - représente n'importe quel nombre de caractères
  • Point d'interrogation (?) - représente un seul caractère dans une position spécifique

Exemple 1. Somme des valeurs basées sur une correspondance partielle

Supposons que vous souhaitiez additionner les montants relatifs à toutes sortes de pommes. Les formules SOMME.SI  suivantes fonctionneront pour vous :

=SOMME.SI(A2:A8; "*pommes*"; C2:C8) - les critères incluent le texte entouré d'astérisques (*).

=SOMME.SI(A2:A8; "*"&E1&"*"; C2:C8) - les critères incluent une référence de cellule entourée d'astérisques. Veuillez noter l'utilisation de l'esperluette (&) avant et après une référence de cellule pour concaténer une chaîne.

Si vous souhaitez compter uniquement les cellules qui commencent ou se terminent par un certain texte, ajoutez un seul * avant ou après le texte:

=SOMME.SI(A2: A8; "pommes*" ;C2:C8) - somme des valeurs dans C2:C8 si une cellule correspondante dans la colonne "A" commence par le mot "pommes".

=SOMME.SI(A2:A8; "*pommes"; C2:C8) - somme des valeurs dans C2:C8 si une cellule correspondante de la colonne "A" se termine par le mot "pommes".

 

Exemple 2. Somme des cellules correspondant aux valeurs de texte

Si votre feuille de calcul contient différents types de données et que vous souhaitiez uniquement additionner les cellules correspondant aux valeurs de texte, les formules SOMME.SI  suivantes vous seront utiles:

=SOMME.SI(A2:A8; "?*" ; C2:C8) - additionne les valeurs dans les cellules C2:C8 si une cellule correspondante dans la colonne A contient au moins 1 symbole.

=SOMME.SI(A2:A8; "*"; C2:C8) - inclut des cellules apparemment vides qui contiennent des chaînes de longueur nulle renvoyées par d'autres formules, par exemple ="".

Les deux formules ci-dessus ignorent les valeurs non textuelles telles que les erreurs, les booléens, les nombres et les dates.

Calculer la somme pour les cellules non vides

Parfois, vous pouvez obtenir un ensemble de données qui contient des entrées vides. Cependant, vous avez une valeur correspondante qui lui est affectée. Dans de tels cas, vous pouvez vouloir exclure les cellules qui contiennent des entrées vides et faire la somme totale. Pour ce faire, utilisez le critère non vide de SOMME.SI.

Dans cet exemple, vous devez additionner les montants dont les dates de livraison sont mentionnées dans le champ « Date de livraison ».

Les critères de la fonction SOMME.SI sont fournis en utilisant l'un des opérateurs de comparaison "Non égale à" (<>) :

=SOMME.SI(B2:B8; "<>"; C2:C8)

"<>" : signifie « Non égale à Vide ».

Cette formule additionnera tous les montants dans la plage C2: C8 où les cellules ne sont pas vides dans la plage B2:B8 (toutes ces cellules qui contiennent au moins une longueur de caractère, signifient « non vide »). Si une cellule contient même un espace, elle répondra aux critères et sera considérée comme une cellule non vide.

Comment additionner des cellules qui correspondent à des cellules vides ?

Si "vide" signifie des cellules qui ne contiennent absolument rien (pas de formule, pas de chaîne de longueur nulle renvoyée par une autre fonction Excel), utilisez "=" comme critère, comme dans la formule SOMME.SI suivante:



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

Si "vide" inclut des chaînes de longueur nulle (par exemple, des cellules avec une formule =""), utilisez "" comme critère:

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

Les deux formules ci-dessus évaluent les cellules de la colonne A et si des cellules vides sont trouvées, les valeurs correspondantes de la colonne C sont additionnées.

Comment additionner des valeurs dans plusieurs colonnes ?

Pour mieux comprendre la problématique, considérons l'exemple suivant. Supposons que vous ayez un tableau récapitulatif des ventes mensuelles. Puisqu'il a été consolidé à partir d'un certain nombre de référentiels régionaux, il existe quelques enregistrements pour le même produit:

Alors, comment trouvez-vous le total des pommes vendues au cours des trois derniers mois?

Comme vous vous en souvenez, les dimensions de « Somme_plage » sont déterminées par les dimensions du paramètre « Plage ». C'est pourquoi vous ne pouvez pas utiliser la formule, comme =SOMME.SI(A2:A8; "pommes"; C2:E8), car elle additionnera les valeurs correspondant à "Pommes" dans la colonne C uniquement. Ce n'est pas ce que vous recherchez !

La solution la plus logique et la plus simple qui se propose consiste à créer une colonne d'assistance qui calcule des sous-totaux individuels pour chaque ligne, puis référence cette colonne dans les critères « Somme_plage ».

  1. Allez-y et placez une simple formule SOMMEdans la cellule F2, puis remplissez la colonne H : =SOMME(C2:E2) :

  1. Ensuite, vous pouvez écrire une formule SOMME.SIhabituelle comme suit :

=SOMME.SI(A2:A8; "pommes"; F2:F8)

Ou :

=SOMME.SI(A2:A8; H1; F2:F8)

Dans les deux formules ci-dessus, « Somme_plage » a exactement la même taille que « Plage », c'est-à-dire 1 colonne et 7 lignes. Elles retournent donc le résultat correct.

Si vous préférez passer par une colonne d'assistance, vous pouvez écrire une formule SOMME.SI  distincte pour chacune des colonnes que vous souhaitez additionner, puis ajouter les nombres renvoyés à l'aide de la fonction SOMME:

=SOMME(SOMME.SI(A2:A8; H1; C2:C8); SOMME.SI(A2:A8; H1; D2:D8); SOMME.SI(A2:A8; H1; E2:E8))

Une autre méthode consiste à utiliser une formule matricielle plus complexe (n'oubliez pas d'appuyer sur Ctrl + Shift + Entrée):

=SOMME((C2:C8 + D2:D8 + E2:E8) * (-- (A2:A8 = H1)))

Les deux formules ci-dessus renverront 2250 pour cet exemple.

Additionner les plus grands ou les plus petits nombres d'une plage

Pour additionner les plus grands ou les plus petits nombres de la plage, utilisez la fonction SOMME avec la fonction GRANDE.VALEUR ou PETITE.VALEUR, respectivement.

Admettons cet exemple dans lequel on va calculer la somme des 4 plus grands nombres parmi les nombres de la colonne A :

  1. Pour trouver le deuxième plus grand nombre, utilisez la fonction suivante :

  1. Pour additionner les 4 plus grands nombres (ne soyez pas submergé), ajoutez la fonction SOMME et remplacez 2 par {1,2,3,4}.

=SOMME(GRANDE.VALEUR(A1:A11;{1;2;3;4}))

  1. Terminez en appuyant sur CTRL + SHIFT + ENTRÉE.

Remarque: La barre de formule indique qu'il s'agit d'une formule matricielle en la mettant entre accolades {}. Ne les tapez pas vous-même. Ils disparaissent lorsque vous modifiez la formule.

La plage (constante de tableau) créée par la fonction GRANDE.VALEUR est stockée dans la mémoire d'Excel, pas dans une plage. La constante du tableau se présente comme suit : {22 ;10 ;8 ;6}

Cette constante de tableau est utilisée comme argument pour la fonction SOMME, donnant un résultat de 46.

Pour calculer la somme des 4 petits nombres, vous pouvez utiliser la même formule en modifiant la fonction GRANDE.VALEUR par PETITE.VALEUR :



=SOMME(PETITE.VALEUR(A1:A11;{1;2;3;4}))

À retenir :

  • Les caractères génériques suivants peuvent être utilisés dans les critères liés au texte:
  • Lorsque l’argument « Somme_plage » est omis, les cellules de la plage seront additionnées.
  • Comme indiqué au début de ce didacticiel, dans les versions modernes de Microsoft Excel, les paramètres « Plage » et « Somme_plage » ne doivent pas nécessairement être de taille égale. Dans Excel 2000 et les versions antérieures, une plage de taille inégale à une somme_plage peuvent provoquer des problèmes. Cependant, même dans les versions les plus récentes d'Excel 2010 et d'Excel 2016, les formules SOMME.SI complexes où « somme_plage » a moins de lignes et / ou de colonnes que « plage » sont capricieuses. C'est pourquoi, il est considéré comme une bonne pratique de toujours avoir ces deux arguments de la même taille et de la même forme.
  • Les premier et troisième paramètres (Plage et Somme_plage) de votre formule SOMME.SI doivent toujours être une référence de plage comme A1:A10. Si vous essayez de transmettre autre chose, par exemple un tableau comme {1 ;2 ;3}, Excel lancera un message d'erreur.
    • ·? : correspond à n'importe quel caractère.
    • ·* : correspond à n'importe quelle séquence de caractères.
  •  Pour trouver un point d'interrogation littéral ou un astérisque, utilisez un tilde (~) devant le point d'interrogation ou l'astérisque (c'est-à-dire ~ ?, ~ *).

Télécharger le fichier Excel de ce tutoriel

Tutoriel Excel