Résumé
Pour additionner des cellules dont les cellules adjacentes ne sont pas vides, vous pouvez utiliser la fonction SOMME.SI.ENS. Dans l’exemple ci-dessus, la formule somme si non vide en G4 est :
= SOMME.SI.ENS( C3:C14 ; D3:D14 ; "<>" )
Cette formule permet de faire la somme des cellules dans la plage C3:C14 si les cellules dans la plage D3:D14 ne sont pas vides.
Formule Excel – Somme si non vide
= SOMME.SI.ENS( plage_somme ; plage_critère ; "<>" )
Explications
Dans l’exemple, l’objectif est d’additionner uniquement les cellules dont les cellules adjacentes ne sont pas vides. C’est-à-dire que l’on souhaite faire une addition en appliquant un critère. Il existe plusieurs solutions pour cela :
- La fonction SOMME.SI ;
- La fonction SOMME.SI.ENS ;
- La fonction SOMMEPROD ;
- La fonction FILTRE associée à la fonction SOMME.
La première solution qui sera mise en avant est celle avec la fonction SOMME.SI.ENS. C’est la solution que je privilégie car elle est simple et permet d’ajouter d’autres critères si vous le souhaitez.
Fonction SOMME.SI.ENS
La fonction SOMME.SI.ENS est une fonction Excel permettant d’additionner des cellules qui remplissent ou un plusieurs critères. Dans notre cas, notre seul critère est de vouloir limiter la somme aux cellules dont les cellules adjacentes ne sont pas vides. Pour cela nous allons utiliser un opérateur logique (>, <, <>, =). La fonction SOMME.SI.ENS s’écrit de la façon suivante :
= SOMME.SI.ENS( plage_somme ; plage_critère1 ; critères1 ; ... )
Dans notre cas, nous voulons tester une seule condition, à savoir limiter la somme aux cellules non vides. Pour cela, le critère s’écrit de la façon suivante « <> ». Par cette syntaxe particulière nous indiquons à Excel vouloir les cellules qui ne sont pas vides.
= SOMME.SI.ENS( C3:C14 ; D3:D14 ; "<>" )
Le résultat sera l’addition des valeurs dans C3:C14 dont les cellules en D3:D14 ne sont pas vides.
Fonction SOMME.SI
La fonction SOMME.SI a un mode de fonctionnement similaire à la fonction SOMME.SI.ENS. Les seules différences sont que ses arguments sont inversés (attention à la position des plages de valeurs) et qu’elle n’accepte qu’une seule condition. J’utilise relativement peu cette fonction car je veux me laisser la liberté d’ajouter plus tard d’autres critères et donc utilise plus fréquemment la fonction SOMME.SI.ENS. Voici la syntaxe de la fonction SOMME.SI :
= SOMME.SI( plage ; critère ; [somme_plage] )
Le dernier argument est optionnel. S’il est manquant, c’est sur le 1er argument que sera utilisé pour faire la somme. Dans notre cas voici la formule qu’il faudrait utiliser avec la fonction SOMME.SI :
= SOMME.SI( D3:D14 ; "<>" ; C3:C14 )
Fonction SOMMEPROD
La fonction SOMMEPROD permet également d’obtenir le résultat souhaité. Elle est plus complexe à utiliser mais se révèle très intéressante dans un certain nombre de cas. La formule qu’il faudrait utiliser serait :
= SOMMEPROD( ( D3:D14<>"" ) * C3:C14 )
Cette formule est à valider avec ctrl+alt+entrée si vous avez une version d’Excel antérieure à Office 365.
La fonction SOMMEPROD permet de multiplier des matrices entre elles. Dans la formule présentée, la première matrice générée est issue d’un test :
= ( D3:D14 <> "" )
Celle-ci renverra une liste de valeurs composée de VRAI et FAUX (visible uniquement sur Office 365).
= {VRAI;VRAI;VRAI;FAUX;VRAI;FAUX;VRAI;VRAI;FAUX;VRAI;VRAI;FAUX}
En multipliant cette matrice par une autre matrice, Excel va convertir les VRAI et FAUX par 1 et 0. On obtiendra la logique suivante :
= {1;1;1;0;1;0;1;1;0;1;1;0}*{60;70;80;120;140;65;95;125;145;48;66;129}
équivalent à
= 1*60 + 1*70 + 1*80 + 0*120 + 1*140 + 0*165 + 1*95 + 1*125
+ 0*145 + 1*48 + 1*66 + 0*129
ce qui donne
= {60;70;80;0;140;0;95;125;0;48;66;0}
Grâce à la fonction SOMMEPROD, on obtient le résultat facilement :
= SOMMEPROD( {60;70;80;0;140;0;95;125;0;48;66;0} )
= 684
Fonction FILTRE
Avec la version 365 d’Excel, vous disposez de la fonction FILTRE. Elle permet de générer un tableau à partir d’un ou plusieurs critères. Associée à la fonction SOMME, on peut générer le tableau des valeurs et les additionner. La formule sera la suivante :
= SOMME( FILTRE( C3:C14 ; D3:D14 <> "" ; 0) )
Dans cette formule, on filtre la plage de cellules C3:C14 à partir des cellules non vides en D3:D14. Le résultat renvoyé sera une liste de valeurs. La fonction SOMME permettra de les additionner.
= SOMME( {60;70;80;140;95;125;48;66} )
Formules en lien
- Compter le nombre de cellules contenant une formule
- SOMME.SI plusieurs critères
- 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