Somme si contient

Résumé

Pour faire la somme si certaines cellules contiennent un texte spécifique, on peut utiliser la fonction SOMME.SI ou la fonction SOMME.SI.ENS grâce aux caractères génériques. Dans l’exemple ci-dessus, la formule en G3 est :

= SOMME.SI.ENS( D3:D10 ; C3:C10 ; "*bleu*" )

Cette formule permet d’additionner les valeurs des stocks (cellules D3:D10) dont les caractéristiques (colonne C) contiennent la valeur « bleu ». Cette solution n’est pas sensible à la casse (majuscules, minuscules). On verra plus loin dans l’article une solution sensible à la casse.

Formule Excel – Somme si contient

= SOMME.SI.ENS( plage_somme ; plage_critère1 ; "*valeur*" ; ... )

Explications

Dans l’exemple, l’objectif est de calculer le stock total des articles dont les caractéristiques contiennent la valeur « bleu ». La difficulté est de rechercher une partie du texte. Heureusement, la fonction SOMME.SI.ENS et la fonction SOMME.SI supportent l’utilisation des caractères génériques.

Si l’on veut utiliser une solution sensible à la casse, il faudra se tourner vers une formule combinant la fonction SOMMEPROD, la fonction TROUVE et la fonction ESTNUM.

Les caractères génériques

Les caractères génériques sont des caractères qui permettent de réaliser des critères avancés comme « commence par », « se termine par », « contient », « contient x caractères »,… Voici quelques exemples d’utilisation des caractères génériques :

Formule	Signification	Exemple
*	Chaîne de caractères	"abc", "a", "aaaaaa"
?	Un caractère	"a", "B"
???	Trois caractères	"aBC", "AAA"
*euros	Se terminant par euros	"4 euros"
Bureau*	Commençant par	"Bureau d'études"
?*	Au moins un caractère	"A", "AAA"
salari?s	Un caractère remplacé	"salaries", "salariés"
?????-?????	5 caractères + tirets + 5 caractères	AB123-BC456
*~?	Se terminant par ?	"ça va ?"
*prod*	Contient "prod"	"Service production"

A noter qu’il faut utiliser les guillemets pour spécifier les critères.

= SOMME.SI.ENS( D3:D10 ; C3:C10 ; "*bleu*" )  --> contient "bleu"

= SOMME.SI.ENS( D3:D10 ; C3:C10 ; "bleu*" ) --> commence par "bleu"

= SOMME.SI.ENS( D3:D10 ; C3:C10 ; "*bleu" ) --> se termine par "bleu"

Fonction SOMME.SI.ENS

La fonction SOMME.SI.ENS permet de cumuler des valeurs sur la base d’un ou plusieurs critères. Sa syntaxe est la suivante :

= SOMME.SI.ENS( plage_somme ; plage_critère1 ; critères1 ; ... )

Dans notre exemple, nous voulons effectuer la somme des quantités de la plage D3:D10 si les cellules en C3:C10 contiennent la valeur « bleu ». On va se servir des caractères génériques.

= SOMME.SI.ENS( D3:D10 ; C3:C10 ; "*bleu*" )

Le résultat est 1478 pièces. C’est-à-dire que 1478 articles ont la caractéristique « bleu ».

Fonction SOMME.SI

La fonction SOMME.SI permet de cumuler des valeurs sur la base d’un seul critère. Sa syntaxe est la suivante :

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

L’ordre des arguments est légèrement différent mais la solution reste la même qu’avec la fonction SOMME.SI.ENS.

= SOMME.SI( B3:B10 ; "*bleu*" ; D3:D10 )

Sensibilité à la casse

Les solutions exposées précédemment ne sont pas sensibles à la casse. C’est-à-dire qu’elles ignorent la présence ou l’absence des majuscules ou minuscules. Pour cela, il faut se servir d’une formule combinant la fonction SOMMEPROD, la fonction TROUVE et la fonction ESTNUM.

= SOMMEPROD( --ESTNUM( TROUVE("bleu";C3:C10) ) * D3:D10 )

La fonction TROUVE permet d’identifier la position d’une chaine de caractères dans un texte en étant sensible à la casse. Si on indique une plage de cellule en tant que second argument, elle renverra une liste de valeurs avec la position pour chaque valeur. Si la fonction TROUVE ne trouve pas la chaine de caractères dans la texte, elle renverra une erreur.

= TROUVE("bleu";C3:C10)

--> Résultat: {9;#VALEUR!;#VALEUR!;9;#VALEUR!;#VALEUR!;9;9}

La fonction ESTNUM vérifie si une valeur est numérique. Si tel est le cas, elle renverra VRAI sinon elle renverra FAUX.

= --ESTNUM ( {9;#VALEUR!;#VALEUR!;9;#VALEUR!;#VALEUR!;9;9} )

--> Résultat: {VRAI;FAUX;FAUX;VRAI;FAUX;FAUX;VRAI;VRAI}

La double négative permet de convertir une liste de VRAI/FAUX en 1/0.

= --({VRAI;FAUX;FAUX;VRAI;FAUX;FAUX;VRAI;VRAI})

--> Résultat: {1;0;0;1;0;0;1;1}

La fonction SOMMEPROD permet de multiplier des matrices entre elles. Nous allons multiplier la liste des valeurs obtenues par les quantités en stock. Les valeurs des stocks des articles n’ayant pas la caractéristique « bleu » seront multipliées par 0 tandis que les autres seront multipliées par 1.

= SOMMEPROD( {1;0;0;1;0;0;1;1} * D3:D10 )

= SOMMEPROD( {433;0;0;442;0;0;249;354} )

--> Résultat: 1478

Formules en lien

• Compter le nombre de cellules contenant une formule
• SOMME.SI plusieurs critères
• Faire une soustraction
• Somme si vide
• Compter le nombre de cellules égales à un texte
• Compter le nombre de cellules contenant une partie du texte (mot, caractère)
• Compter nombre de valeurs texte différentes
• Compter le nombre de cellules non vides
• NB.SI avec plusieurs critères
• Trouver des doublons avec NB.SI
• Somme si différent de
• Somme si contient
• Somme si date est entre deux dates
• Compter le nombre de cellules contenant une date
• Arrondir à 0,5
• Arrondir à la centaine