Faites-moi confiance, je vais partager avec vous une formule SUMIF très utile ! Si vous utilisez fréquemment la fonction SOMME.SI dans Google Sheets, vous allez adorer ce tutoriel et la formule. Découvrez comment inclure des cellules vides adjacentes dans la plage SUMIF dans Google Sheets.
Je suis sûr que vous n’avez jamais vu une telle formule SUMIF auparavant ! Je suis ravi de partager cette formule avec vous. Continuez à lire.
Comme vous le savez peut-être, la fonction SOMME.SI dans Google Sheets prend une plage, un critère (critères avec ArrayFormula) et une plage_somme en argument.
SOMME.SI(plage, critère, [plage_somme])
Cela nous permet de faire une somme conditionnelle (critère) sur une plage (plage).
Parfois, la plage peut contenir des cellules vides, mais la plage_somme peut contenir des valeurs. Qu’arrive-t-il alors à ces valeurs dans la somme conditionnelle ?
=sumif(A2:A9, » »,C2:C9)
Cette formule est basée sur les données d’exemple ci-dessous (capture d’écran n°1). Elle renverrait 7000,00 car le critère est une chaîne vide « ».
C’est le total des valeurs de la colonne C où la colonne A contient des cellules vides. Mais je ne veux pas ça.
Je veux que les cellules vides de la colonne A (plage) prennent la valeur des cellules non vides situées au-dessus dans la SOMME.SI. Je ne veux pas utiliser une valeur vide comme critère.
Inclure toutes les valeurs dans plage_somme même si la plage contient des cellules vides dans SUMIF
Capture d’écran n°1 :
Dans cet exemple, comme vous pouvez le voir, les cellules A3, A5, A6 et A9 sont vides, mais les cellules correspondantes C3, C5, C6 et C9 contiennent des valeurs.
La formule normale SOMME.SI ci-dessous renverrait la valeur 4500 car la formule ne trouve que le critère « Kim Robinson » dans la cellule A2. Elle renvoie donc la valeur de la cellule C2.
=sumif(A2:A9, »Kim Robinson »,C2:C9)
Je veux que la formule lise également la cellule A3 comme « Kim Robinson » et renvoie le total des cellules C2 et C3, soit 9000. J’en veux de même avec plusieurs critères dans SOMME.SI.
Ma formule SOMME.SI avec formule matricielle dans la cellule F3 prend les critères multiples dans E3:E6 (veuillez consulter l’image ci-dessus). La formule prend en compte les cellules vides dans la plage A2:A9 et leur attribue les valeurs des cellules non vides situées au-dessus. Elle effectue la somme de la plage_somme C2:C9 en conséquence. Vous trouverez cette formule dans ce tutoriel.
Avant de continuer, je vais vous montrer une autre image. Dans celle-ci, je ne vais pas spécifier les critères séparément. Dans l’exemple précédent (capture d’écran n°1), la plage et le critère se réfèrent aux mêmes données, à savoir A2:A9. Ici, je veux le résultat dans une colonne totale (colonne D, cellule D2). Je ne veux donc pas spécifier les critères séparément.
Capture d’écran n°2 :
Si vous comparez les captures d’écran 1 et 2, vous pouvez comprendre la différence. Dans le premier exemple, j’ai spécifié les critères de SOMME.SI séparément, mais pas dans le deuxième exemple.
Ma formule qui inclut des cellules vides adjacentes dans la plage SUMIF sera la même pour les deux exemples. Vous n’avez qu’à changer la référence des critères dans la formule.
Formule pour inclure des cellules vides adjacentes dans la plage SUMIF dans Google Sheets
Au lieu d’expliquer la formule, je vais d’abord partager les formules.
Formule n°1 utilisée dans l’exemple 1 dans la cellule F3 (capture d’écran n°1) :
=ArrayFormula(if(len(E3:E),sumif(lookup(row(A2:A),row(A2:A)/if(A2:A<> » »,TRUE,FALSE),A2:A),E3:E,C2:C),))
Formule n°2 utilisée dans l’exemple 2 dans la cellule D2 (capture d’écran n°2) :
=ArrayFormula(if(len(A2:A),sumif(lookup(row(A2:A),row(A2:A)/if(A2:A<> » »,TRUE,FALSE),A2:A),A2:A,C2:C),))
Comme je l’ai dit, les différences entre ces formules sont minimes. J’ai changé la plage de critères de E3:E à A2:A dans le deuxième exemple. De plus, pour limiter la sortie de la formule aux cellules non vides dans la plage de critères, j’ai utilisé la fonction LEN.
J’ai ajusté LEN en fonction de la plage de critères. C’est len(E3:E) dans la première formule et len(A2:A) dans la deuxième formule.
Maintenant, je vais attirer votre attention sur la partie la plus importante de ce tutoriel, c’est-à-dire expliquer la formule ci-dessus. Pour cela, je vais prendre la deuxième formule (veuillez vous référer à la capture d’écran n°2).
Explication de la formule SOMME.SI n°2
Je peux facilement vous faire comprendre la formule 2 ci-dessus. Tout d’abord, entrez cette formule SOMME.SI matricielle dans la cellule D2.
=ArrayFormula(if(len(A2:A),sumif(A2:A,A2:A,C2:C),))
La formule SOMME.SI ci-dessus ignore les cellules mises en évidence dans la somme totale car les cellules correspondantes de la colonne A contiennent des cellules vides.
Donc, pour inclure les valeurs correspondant aux cellules vides dans la somme totale de SOMME.SI, remplacez la plage A2:A par une plage virtuelle. Je veux dire que vous remplacez simplement A2:A dans la formule ci-dessus par la formule ci-dessous.
lookup(row(A2:A),row(A2:A)/if(A2:A<> » »,TRUE,FALSE),A2:A)
Consultez l’image ci-dessous pour comprendre comment apporter les modifications nécessaires à SOMME.SI.
Cette formule de recherche remplit les cellules vides dans la plage SOMME.SI avec les valeurs des cellules situées au-dessus.
Voici un tutoriel très détaillé sur cette formule de recherche particulière, qui est le pilier de la formule SOMME.SI ci-dessus – Formule matricielle pour remplir les cellules vides avec les valeurs supérieures dans Google Sheets.
Voilà tout ce qu’il faut savoir sur la façon d’inclure des cellules vides adjacentes dans la plage SUMIF dans Google Sheets. Merci de votre attention. Profitez-en !
Tutoriels avancés sur SOMME.SI :
- Somme de plusieurs colonnes avec des critères – Cela fonctionne dans Google Sheets.
- Formule SOMME.SI avec plusieurs critères dans Google Sheets.
- SOMME.SI pour faire la somme par semaine de travail en cours dans Google Sheets.
- Comment inclure plusieurs colonnes de sommes dans SOMME.SI dans Google Sheets.
- Comment utiliser des plages dynamiques dans la formule SOMME.SI dans Google Sheets.
- SOMME.SI en excluant les lignes masquées dans Google Sheets.
- Comment faire une SOMME.SI sensible à la casse dans Google Sheets.
- Comment faire la somme de chaque N-ème ligne dans Google Sheets en utilisant SOMME.SI.
- MMULT au lieu de SOMME.SI dans Google Sheets pour un résultat matriciel.
- Comment faire une SOMME.SI sensible à la casse dans Google Sheets.
