Comment utiliser la formule SOMME.SI avec des critères

(Last Updated On: 20 janvier 2018)

Avec une seule formule, vous pouvez résumer tout type de données financières, en additionnant les nombres selon une liste de critères. Cela facilite la synthèse des données financières pour construire un bilan ou un rapport de ventes par exemple.

J’expliquai brièvement dans cet article l’utilisation des formules SOMME.SI pour synthétiser des données. Dans cet article, je vais donner plusieurs exemples détaillés d’utilisation des formules SOMME.SI et SOMME.SI.ENS.

Somme avec un seul critère

Pour additionner des valeurs selon un critère, utilisez la fonction SOMME.SI.

La syntaxe est la suivante :

=SOMME.SI(plage critère ; critère ; plage somme)

Le critère peut être une valeur numérique, du texte, une date, la référence à une cellule, une condition avec opérateur ‘<‘, ‘>’, ‘=’, ‘<>’, etc.

Voici quelques exemples :

total des ventes dont la région est renseignée en cellule D9 =SOMME.SI(DimVentes[Région];D9;DimVentes[Montant ventes])
total des ventes pour la région centre =SOMME.SI(DimVentes[Région]; »Centre »;DimVentes[Montant ventes])
total des ventes pour la date 25/11/2015 =SOMME.SI(DimVentes[Date]; »25/11/2015″;DimVentes[Montant ventes])
total des ventes depuis le 01/01/2016 =SOMME.SI(DimVentes[Date]; »>01/01/2016″;DimVentes[Montant ventes])
totales des ventes supérieures ou égales à 1000 € =SOMME.SI(DimVentes[Montant ventes]; »>=1000″;DimVentes[Montant ventes])

Somme avec plusieurs critères

Pour additionner des cellules en fonction de plusieurs critères, utilisez la formule SOMME.SI.ENS.

La syntaxe est :

=SOMME.SI.ENS(plage somme ; plage critère 1 ; critère 1 ; plage critère 2 ; critère 2 ; etc)

Exemple :

=SOMME.SI.ENS(DimVentes[Montant ventes];DimVentes[Région]; »Est »;DimVentes[Responsable]; »Hugues »)

 Somme avec un critère de correspondance sur le texte

On peut additionner des cellules avec un critère sur le contenu de la cellule.

Dans l’exemple ci-dessous, toutes les commandes de Stylo et Pack stylos seront additionnées, car elles contiennent la chaîne « stylo ».

Voici la syntaxe :

=SOMME.SI(E34:E40; »*stylo* »;H34:H40)

En utilisant le critère « *stylo* », toutes les cellules de la colonne Item avec un texte contenant la chaîne « stylo » seront additionnées. Il est important de mettre des astéristiques « * » en début et fin de « stylo », sinon la correspondance se fera de manière exacte et seules les ventes d’item « Stylo » seront additionnées.

Somme avec une liste extensible de critères

Au lieu d’additionner des valeurs suivant des critères prédéfinis comme avec SOMME.SI.ENS, il est aussi possible d’entrer une liste de critères et d’ajouter plus tard des critères à la liste sans modifier la formule.

Dans l’exemple ci-dessous, on calcule le total des ventes pour la région centre (1er critère défini) et pour la liste d’item Crayon et Classeurs renseignés en cellules H51:H52.

Pour calculer le total :

  1. Entrez la formule SOMME.SI.ENS avec la plage à sommer « Ventes », suivie de la première plage de critères « Région », puis le premier critère « Centre »
  2. Entrez la deuxième plage de critères « Item », puis sélectionner une plage de cellules comme critère (ici H51:H54)
  3. Encadrer la formule SOMME.SI.ENS par la fonction SOMME et valider avec Ctrl+Shift+Entrée, car il s’agit d’une formule matricielle
  4. Alternativement, vous pouvez aussi utiliser SOMMEPROD au lieu de SOMME pour encadrer la formule SOMME.SI.ENS, puis valider simplement par Entrée

Si on ajoute des critères supplémentaires sous la cellule H52, la formule recalculera automatiquement le total des ventes suivant les nouveaux critères. Il en va de même si on enlève des critères.

{=SOMME(SOMME.SI.ENS(Ventes2[Ventes];Ventes2[Région]; »Centre »;Ventes2[Item];ListeCritères[Critères]))}

ou bien :

=SOMMEPROD(SOMME.SI.ENS(Ventes2[Ventes];Ventes2[Région]; »Centre »;Ventes2[Item];ListeCritères[Critères]))

Somme pour un intervalle de dates

Nous utiliserons une formule SOMME.SI.ENS pour obtenir le total des ventes à partir d’une date de départ et avant une date de fin. Deux colonnes sont nécessaires pour cela, une colonnes de dates de ventes et une colonnes de montant des ventes.

Voici un exemple :

On souhaite obtenir le total des ventes sur le premier semestre de l’année 2016

Entrez la formule suivante :

  1. Le premier critère sur les dates contient l’opérateur « >= » suivi de la date de début
  2. Le deuxième critère sur les dates contient l’opérateur « < » suivi de la date de fin
=SOMME.SI.ENS(DimVentes[Montant ventes];DimVentes[Date]; »>=01/01/2016″;DimVentes[Date]; »<01/07/2016″)

Somme pour une liste filtrée

Après avoir filtré les lignes dans un tableau de valeurs, vous pouvez utiliser la fonction AGREGAT au lieu de la fonction SOMME pour additionner des nombres seulement sur les lignes visibles. Cette fonction a été introduite dans Excel 2010.

Similaire à la fonction SOUS-TOTAL, AGREGAT ignore les lignes cachées ou les erreurs et propose plusieurs opérations, telles que SOMME ou MOYENNE sur les données sélectionnées.

Pour en savoir plus sur la fonction AGREGAT, vous pouvez vous référer à l’article détaillé que j’ai écrit sur l’utilisation de cette formule.

 

Avez-vous d’autres exemples d’utilisation des formules SOMME.SI et SOMME.SI.ENS ? Vous pouvez les partager en  commentaires.

 

TELECHARGER LE FICHIER D’EXEMPLE

Télécharger le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *