SOMME.SI.ENS() : Réaliser une somme en fonction de plusieurs critères de filtres

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Après les fonctions SOMME et SOMME.SI, nous allons maintenant découvrir la fonction SOMME.SI.ENS. Cette dernière ressemble beaucoup à la fonction SOMME.SI, mais elle va nous permettre d'effectuer un tri en fonction d'une infinité de paramètres (enfin presque puisque la limite est de 127 critères de sélection !!).

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

 

 

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

 

1. La fonction SOMME.SI.ENS

La fonction SOMME.SI additionne les valeurs d'une plage de cellules donnée en paramètre lorsqu'une ou plusieurs conditions sont remplies. Ces paramètres peuvent être des chiffres, des textes ou des références de cellules.

2. Les paramètres

La fonction SOMME.SI s'utilise de la manière suivante :

=SOMME.SI.ENS(Plage_somme;Plage_critère1;Critère1[;Plage_critère2;Critère2;...;Plage_critèreN;CritèreN])

Où :

  • Plage_somme est la plage des cellules sur lesquelles la somme doit être appliquée lorsque les critères définis juste après son remplis,
  • Plage_critère1...Plage_critèreN représentent les plage de cellules pour lesquelles un test est effectué
  • Critère1...CritèreN est la condition à respecter pour effectuer la somme. Ce critère peut être un nombre (exemple : « 12 »), une expression (exemple : « <12 ») ou un texte (exemple : « France »).

Comme pour toutes les fonctions sur Excel, chaque paramètre est séparé par un point virgule, et les plages de données sont définies par deux points

3. Compatibilités

La fonction SOMME.SI fonctionne sur les version suivantes d'Excel :

  • Excel 2016
  • Excel 2013
  • Excel 2011 for Mac
  • Excel 2010
  • Excel 2007

4. Utilisation

Excel formation - la fonction somme si ens

Dans cet exemple, nous voulons connaître les ventes réalisés par Lucien pour les pays dans lesquels il réalise plus de 1000€ d'affaires. Nous voulons donc que deux critères soient réunis en même temps : 

  • Les ventes doivent être réalisées par Lucien,
  • Les ventes doivent être de plus de 1000€
=SOMME.SI.ENS(C4:C15;C4:C15;">1000";B4:B15;"Lucien")
  • $C$4:$C$15 est la plage des cellules qui reprend le montant des ventes. Cette plage va nous servir pour deux chose : effectuer la somme, et contrôler que les ventes soient de plus de 1000€
  • $B$4:$B$15 est la plage des cellules qui liste tous les prénoms,

Note : le paramètre « Critère1 » peut prendre les formes suivantes :

  • Sous forme numérique : dans ce cas la somme sera effectué sur toutes les valeurs de cellules de la « Plage » qui sont strictement égales au critère. Le critère est alors saisie directement (sans guillements). 
=SOMME.SI.ENS(A1:A10;B1:B10;1000)
=SOMME.SI.ENS(A1:A10;B1:B10;B1) → « B1 » vaut 1000
  • Sous la forme d'une expression : dans ce cas la somme sera effectué sur toutes les valeurs de cellules de la « Plage » qui répondent à l'expression. Le critère est alors saisie directement entre guillemets :
=SOMME.SI.ENS(A1:A10;B1:B10;"<1000")
=SOMME.SI.ENS(A1:A10;B1:B10;"<"&B1) → « B1 » vaut 1000

Notez la manière d'utiliser une cellule dans le second exemple : la condition est entre guillement, puis l'ésperluette (« & ») permet de rattacher la valeur de cellule qui contient la valeur à utiliser.

Les expressions disponibles sont les suivante

=SOMME.SI.ENS(A1:A10;B1:B10;"=1000") → Lorsque la cellule vaut 1000
=SOMME.SI.ENS(A1:A10;B1:B10;"<1000") → Lorsque la cellule est inférieure à 1000
=SOMME.SI.ENS(A1:A10;B1:B10;"<=1000") → Lorsque la cellule est inférieure ou égale à 1000
=SOMME.SI.ENS(A1:A10;B1:B10;">1000") → Lorsque la cellule est supérieure à  1000
=SOMME.SI.ENS(A1:A10;B1:B10;">=1000") → Lorsque la cellule est supérieure ou égale à  1000
=SOMME.SI.ENS(A1:A10;B1:B10;"<>1000") → Lorsque la cellule est différente de 1000
  • Sous la forme d'un texte: dans ce cas la somme sera effectuée sur toutes les valeurs de cellules de la « Somme_plage» pour lesquelles l'équivalent de la « Plage » répondent à l'expression « Critère ». Le critère est alors saisie directement entre guillemets et le dernier argument est obligatoire (impossible de sommer des textes :
=SOMME.SI.ENS(A1:A10;B1:B10;"France")

Note bis : Comme vous avez dû le remarquer, n'utiliser qu'un seul argument dans la fonction SOMME.SI.ENS est équivalent à utiliser la fonctionne SOMME.SI

=SOMME.SI.ENS(A1:A10;B1:B10;"France")
=SOMME.SI(B1:B10;"France";A1:A10)

Attention toutesfois, les arguments ne sont plus dans le même ordre, la plage des cellules à additionner est en première position dans la formule SOMME.SI.ENS, tandis que nous la trouvions en dernière position dans la fonction SOMME.SI.

 



Articles qui pourraient vous intéresser

Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !
Les erreurs de concaténation qui vous font perdre du temps sur Excel
Découvrez la nouvelle fonction Excel que 90% des experts ne connaissent pas !
Boostez vos investissements : maîtrisez le calcul du taux de rentabilité avec Excel en 10 minutes !
Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.