Ce didacticiel fournit une démonstration détaillée de l'utilisation des fonctions SUMIF et SUMIFS dans Google Sheets avec des formules et des exemples.
SUMIF est l'une des fonctions mathématiques de Google Sheets, qui est utilisée pour additionner des cellules de manière conditionnelle. Fondamentalement, la fonction SUMIF recherche une condition spécifique dans une plage de cellules, puis additionne les valeurs qui répondent à la condition donnée.
Par exemple, vous avez une liste de dépenses dans Google Sheets et vous souhaitez uniquement résumer les dépenses supérieures à une certaine valeur maximale. Ou vous avez une liste d'articles de commande et leurs montants correspondants, et vous voulez seulement connaître le montant total de la commande d'un article spécifique. C'est là que la fonction SUMIF est utile.
Le SUMIF peut être utilisé pour additionner des valeurs basées sur une condition de nombre, une condition de texte, une condition de date, des caractères génériques ainsi que sur des cellules vides et non vides. Google Sheets a deux fonctions pour additionner des valeurs en fonction de critères : SUMIF et SUMIFS. La fonction SUMIF additionne les nombres en fonction d'une condition, tandis que SUMIFS additionne les nombres en fonction de plusieurs conditions.
Dans ce didacticiel, nous expliquerons comment utiliser les fonctions SUMIF et SUMIFS dans Google Sheets pour additionner des nombres remplissant certaines conditions.
Fonction SUMIF dans Google Sheets - Syntaxe et arguments
La fonction SUMIF est juste une combinaison des fonctions SUM et IF. La fonction SI parcourt la plage de cellules pour une condition donnée, puis la fonction SOMME additionne les nombres correspondant aux cellules qui remplissent la condition.
Syntaxe de la fonction SUMIF:
La syntaxe de la fonction SUMIF dans Google Sheets est la suivante :
=SUMIF(plage, critères, [sum_plage])Arguments:
intervalle - La plage de cellules où nous recherchons les cellules qui répondent aux critères.
Critères – Les critères qui déterminent quelles cellules doivent être ajoutées. Vous pouvez baser le critère sur le nombre, la chaîne de texte, la date, la référence de cellule, l'expression, l'opérateur logique, le caractère générique ainsi que d'autres fonctions.
sum_range – Cet argument est facultatif. Il s'agit de la plage de données avec des valeurs à additionner si l'entrée de plage correspondante correspond à la condition. Si vous n'incluez pas cet argument, alors la « plage » est additionnée à la place.
Voyons maintenant comment utiliser la fonction SUMIF pour additionner des valeurs avec différents critères.
Fonction SUMIF avec des critères numériques
Vous pouvez additionner des nombres qui répondent à certains critères dans une plage de cellules, en utilisant l'un des opérateurs de comparaison suivants pour créer des critères.
- supérieur à (>)
- moins de (<)
- supérieur ou égal à (>=)
- inférieur ou égal à (<=)
- égal à (=)
- pas égal à ()
Supposons que vous ayez la feuille de calcul suivante et que vous soyez intéressé par les ventes totales de 1000 ou plus.
Voici comment accéder à la fonction SUMIF :
Tout d'abord, sélectionnez la cellule dans laquelle vous souhaitez que la sortie de la somme apparaisse (D3). Pour additionner les nombres dans B2:B12 qui sont supérieurs ou égaux à 1000, tapez cette formule et appuyez sur « Entrée » :
=SOMMEIF(B2:B12,">=1000",B2:B12)Dans cet exemple de formule, les arguments range et sum_range (B2:B12) sont les mêmes, car les numéros de vente et les critères sont appliqués sur la même plage. Et nous avons entré le nombre avant l'opérateur de comparaison et l'avons mis entre guillemets car les critères doivent toujours être mis entre guillemets doubles, sauf pour une référence de cellule.
La formule recherchait des nombres supérieurs ou égaux à 1000, puis additionnait toutes les valeurs correspondantes et montrait le résultat dans la cellule D3.
Étant donné que les arguments range et sum_range sont les mêmes, vous pouvez obtenir le même résultat sans les arguments sum_range dans la formule, comme ceci :
=SOMMEIF(B2:B12,">=1000")
Ou vous pouvez fournir la référence de cellule (D2) qui contient le nombre au lieu du critère numérique, et joindre l'opérateur de comparaison à cette référence de cellule dans l'argument critère :
=SOMMEIF(B2:B12,">="&D2)Comme vous pouvez le voir, l'opérateur de comparaison est toujours entré entre guillemets doubles et l'opérateur et la référence de cellule sont concaténés par une esperluette (&). Et vous n'avez pas besoin de mettre la référence de cellule entre guillemets.
Noter: Lorsque vous faites référence à la cellule qui contient des critères, assurez-vous de ne laisser aucun espace de début ou de fin dans la valeur de la cellule. Si votre valeur contient un espace inutile avant ou après la valeur dans la cellule référencée, la formule renverra « 0 » en conséquence.
Vous pouvez également utiliser d'autres opérateurs logiques de la même manière pour créer des conditions dans l'argument critère. Par exemple, pour additionner des valeurs inférieures à 500 :
=SOMMEIF(B2:B12,"<500")
Somme si nombres égaux à
Si vous souhaitez ajouter des nombres égaux à un certain nombre, vous pouvez soit saisir uniquement le nombre, soit saisir le nombre avec le signe égal dans l'argument critère.
Par exemple, pour additionner les montants des ventes correspondants (colonne B) pour les quantités (colonne C) dont les valeurs sont égales à 20, essayez l'une de ces formules :
=SOMMEIF(C2:C12,"=20",B2:B12)
=SOMMEIF(C2:C12,"20",B2:B12)
=SOMMEIF(C2:C12,E2,B2:B12)
Pour additionner les nombres de la colonne B avec une quantité différente de 20 dans la colonne C, essayez cette formule :
=SOMMEIF(C2:C12,"20",B2:B12)
Fonction SUMIF avec critères de texte
Si vous souhaitez ajouter des nombres dans une plage de cellules (colonne ou ligne) correspondant aux cellules qui contiennent un texte spécifique, vous pouvez simplement inclure ce texte ou la cellule qui contient le texte dans l'argument critère de votre formule SUMIF. Veuillez noter que la chaîne de texte doit toujours être entourée de guillemets (" ").
Par exemple, si vous voulez le montant total des ventes dans la région « Ouest », vous pouvez utiliser la formule ci-dessous :
=SUMIF(C2:C13,"Ouest",B2:B13)Dans cette formule, la fonction SUMIF recherche la valeur « Ouest » dans la plage de cellules C2:C13 et additionne la valeur des ventes correspondante dans la colonne B. Affiche ensuite le résultat dans la cellule E3.
Vous pouvez également faire référence à la cellule qui contient du texte au lieu d'utiliser le texte dans l'argument critère :
=SOMMEIF(C2:C12,E2,B2:B12)
Maintenant, obtenons le revenu total de toutes les régions sauf « Ouest ». Pour ce faire, nous utiliserons pas égal à l'opérateur () dans la formule :
=SOMMEIF(C2:C12,""&E2,B2:B12)
SUMIF avec WildCards
Dans la méthode ci-dessus, la fonction SUMIF avec des critères de texte vérifie la plage par rapport au texte spécifié exact. Ensuite, il additionne les nombres par rapport au texte exact et ignore tous les autres nombres, y compris la chaîne de texte partiellement correspondante. Pour additionner les nombres avec des chaînes de texte partiellement correspondantes, vous devez personnaliser l'un des caractères génériques suivants dans vos critères :
?(point d'interrogation) est utilisé pour faire correspondre n'importe quel caractère, n'importe où dans la chaîne de texte.*(astérisque) est utilisé pour trouver les mots correspondants avec n'importe quelle séquence de caractères.~(tilde) est utilisé pour faire correspondre les textes avec un point d'interrogation (?) ou un astérisque (*).
Nous allons utiliser cet exemple de feuille de calcul pour les produits et leurs quantités pour additionner des nombres avec des caractères génériques :
Astérisque (*) Caractère générique
Par exemple, si vous voulez additionner les quantités de tous les produits Apple, utilisez cette formule :
=SUMIF(A2:A14,"Pomme*",B2:B14)Cette formule SUMIF trouve tous les produits avec le mot « Apple » au début et un nombre quelconque de caractères après celui-ci (indiqué par « * »). Une fois la correspondance trouvée, elle résume le Quantité nombres correspondant aux chaînes de texte correspondantes.
Il est également possible d'utiliser plusieurs caractères génériques dans les critères. Et vous pouvez également saisir des caractères génériques avec des références de cellule au lieu de texte direct.
Pour ce faire, les caractères génériques doivent être placés entre guillemets doubles (" ") et concaténés avec la ou les références de cellule :
=SOMMEIF(A2:A14,"*"&D2&"*",B2:B14)Cette formule additionne les quantités de tous les produits qui contiennent le mot « Redmi », peu importe où le mot se trouve dans la chaîne.
Point d'interrogation (?) Caractère générique
Vous pouvez utiliser le caractère générique du point d'interrogation (?) pour faire correspondre des chaînes de texte avec n'importe quel caractère unique.
Par exemple, si vous souhaitez trouver des quantités de toutes les variantes de Xiaomi Redmi 9, vous pouvez utiliser cette formule :
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)La formule ci-dessus recherche des chaînes de texte avec le mot "Xiaomi Redmi 9" suivi de tout caractère unique et additionne les Quantité Nombres.
Tilde (~) Caractère générique
Si vous souhaitez faire correspondre un point d'interrogation (?) ou un astérisque (*), insérez le caractère tilde (~) avant le caractère générique dans la partie condition de la formule.
Pour ajouter les quantités dans la colonne B avec la chaîne correspondante qui se termine par un astérisque, entrez la formule ci-dessous :
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Pour ajouter des quantités dans la colonne B qui ont un point d'interrogation (?) dans la colonne A de la même ligne, essayez la formule ci-dessous :
=SOMMEIF(A2:A14,"~?",B2:B14)
Fonction SUMIF avec critères de date
La fonction SUMIF peut également vous aider à additionner conditionnellement des valeurs basées sur des critères de date - par exemple, des nombres correspondant à une certaine date, ou avant une date, ou après une date. Vous pouvez également utiliser l'un des opérateurs de comparaison avec une valeur de date pour créer des critères de date pour la somme des nombres.
La date doit être saisie au format de date pris en charge par Google Sheets, ou en tant que référence de cellule contenant une date, ou à l'aide d'une fonction de date telle que DATE () ou AUJOURD'HUI ().
Nous utiliserons cet exemple de feuille de calcul pour vous montrer comment fonctionne la fonction SUMIF avec des critères de date :
Supposons que vous souhaitiez additionner les montants des ventes qui ont eu lieu le ou avant (<=) le 29 novembre 2019 dans l'ensemble de données ci-dessus, vous pouvez ajouter ces chiffres de ventes à l'aide de la fonction SUMIF de l'une des manières suivantes :
=SUMIF(C2:C13,"<=29 novembre 2019",B2:B13)La formule ci-dessus vérifie chaque cellule de C2 à C13 et ne correspond qu'aux cellules contenant des dates au plus tard le 29 novembre 2019 (29/11/2019). Puis additionne le montant des ventes correspondant aux cellules correspondantes de la plage de cellules B2:B13 et affiche le résultat dans les cellules E3.
La date peut être fournie à la formule dans n'importe quel format reconnu par Google Sheets, comme "29 novembre 2019", "29 novembre 2019" ou "29/11/2019", etc. Souvenez-vous de la valeur de la date et l'opérateur doit toujours être entouré de guillemets doubles.
Vous pouvez également utiliser la fonction DATE() dans les critères à la place de la valeur de date directe :
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Ou, vous pouvez utiliser la référence de cellule au lieu de la date dans la partie critère de la formule :
=SOMMEIF(C2:C13,"<="&E2,B2:B13)
Si vous souhaitez additionner les montants des ventes en fonction de la date du jour, vous pouvez utiliser la fonction AUJOURD'HUI () dans l'argument critère.
Par exemple, pour additionner tous les montants des ventes pour la date d'aujourd'hui, utilisez cette formule :
=SOMMEIF(C2:C13,AUJOURD'HUI(),B2:B13)
Fonction SUMIF avec des cellules vides ou non vides
Parfois, vous devrez peut-être additionner les nombres dans une plage de cellules avec des cellules vides ou non vides dans la même ligne. Dans de tels cas, vous pouvez utiliser la fonction SUMIF pour additionner les valeurs en fonction de critères où les cellules sont vides ou non.
Somme si vide
Il existe deux critères dans Google Sheets pour trouver des cellules vides : " " ou " =".
Par exemple, si vous souhaitez additionner tous les montants des ventes contenant des chaînes de longueur nulle (visuellement vides) dans la colonne C, utilisez des guillemets doubles sans espace entre les deux dans la formule :
=SOMMEIF(C2:C13,"",B2:B13)
Pour additionner tout le montant des ventes dans la colonne B avec des cellules vides complètes dans la colonne C, incluez « =" comme critère :
=SOMMEIF(C2:C13,"=",B2:B13)
Somme si non vide :
Si vous souhaitez additionner des cellules contenant une valeur (non vide), vous pouvez utiliser "" comme critère dans la formule :
Par exemple, pour obtenir le montant total des ventes avec n'importe quelle date, utilisez cette formule :
=SOMMEIF(C2:C13,"",B2:B13)
SUMIF basé sur plusieurs critères avec logique OU
Comme nous l'avons vu jusqu'à présent, la fonction SUMIF est conçue pour additionner des nombres en fonction d'un seul critère, mais il est possible de sommer des valeurs en fonction de plusieurs critères avec la fonction SUMIF dans Google Sheets. Cela peut être fait en joignant plus d'une fonction SUMIF dans une seule formule avec une logique OU.
Par exemple, si vous souhaitez additionner le montant des ventes dans la région « Ouest » ou la région « Sud » (logique OU) dans la plage spécifiée (B2 : B13), utilisez cette formule :
=SUMIF(C2:C13,"Ouest",B2:B13)+SUMIF(C2:C13,"Sud",B2:B13)Cette formule additionne les cellules lorsqu'au moins une des conditions est VRAIE. C'est pourquoi on l'appelle « logique OU ». Il additionnera également les valeurs lorsque toutes les conditions sont remplies.
La première partie de la formule vérifie la plage C2:C13 pour le texte « Ouest » et additionne les valeurs de la plage B2:B13 lorsque la correspondance est rencontrée. La partie secondes des vérifications pour la valeur de texte « Sud » dans la même plage C2:C13, puis additionne les valeurs avec le texte correspondant dans la même plage sum_B2:B13. Ensuite, les deux sommes sont additionnées et affichées dans la cellule E3.
Dans les cas où un seul critère est rempli, il ne retournera que cette valeur de somme.
Vous pouvez également utiliser plusieurs critères au lieu d'un ou deux. Et si vous utilisez plusieurs critères, il est préférable d'utiliser une référence de cellule comme critère au lieu d'écrire la valeur directe dans la formule.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF avec logique OR ajoute des valeurs lorsqu'au moins un des critères spécifiés est rempli, mais si vous ne souhaitez additionner les valeurs que lorsque toutes les conditions spécifiées sont remplies, vous devez utiliser sa nouvelle fonction sœur SUMIFS().
Fonction SUMIFS dans Google Sheets (critères multiples)
Lorsque vous utilisez la fonction SUMIF pour additionner des valeurs en fonction de plusieurs critères, la formule peut devenir trop longue et compliquée et vous êtes susceptible de faire des erreurs. De plus, SUMIF vous permettra de faire la somme des valeurs uniquement sur une seule plage et lorsque l'une des conditions est VRAIE. C'est là qu'intervient la fonction SUMIFS.
La fonction SUMIFS vous aide à additionner des valeurs en fonction de plusieurs critères de correspondance dans une ou plusieurs plages. Et cela fonctionne sur la logique AND, ce qui signifie qu'il ne peut additionner des valeurs que lorsque toutes les conditions données sont remplies. Même si une condition est fausse, elle renverra « 0 » comme résultat.
Syntaxe et arguments de la fonction SUMIFS
La syntaxe de la fonction SUMIFS est la suivante :
=SUMIFS(sum_range, criteres_range1, critère1, [criteria_range2, ...], [criterion2, ...])Où,
- sum_range – La plage de cellules contenant les valeurs que vous souhaitez additionner lorsque toutes les conditions sont remplies.
- critères_plage1 – Il s'agit de la plage de cellules où vous vérifiez les critères1.
- critère1 – C'est la condition que vous devez vérifier par rapport à criteres_range1.
- criteria_range2, critère2, …– Les plages et critères supplémentaires à évaluer. Et vous pouvez ajouter plus de plages et de conditions à la formule.
Nous utiliserons l'ensemble de données dans la capture d'écran suivante pour montrer comment la fonction SUMIFS fonctionne avec différents critères.
SUMIFS avec conditions de texte
Vous pouvez additionner des valeurs en fonction de deux critères de texte différents dans des plages différentes. Par exemple, supposons que vous souhaitiez connaître le montant total des ventes de l'article Tente livré. Pour cela, utilisez cette formule :
=SUMIFS(D2:D13,A2:A13,"Tente",C2:C13,"Livré")Dans cette formule, nous avons deux critères : « Tente » et « Livrée ». La fonction SUMIFS vérifie l'élément « Tente » (critères1) dans la plage A2:A13 (critères_plage1) et vérifie le statut « Livré » (critères2) dans la plage C2:C13 (critères_plage2). Lorsque les deux conditions sont remplies, il additionne la valeur correspondante dans la plage de cellules D2:D13 (sum_range).
SUMIFS avec critères numériques et opérateurs logiques
Vous pouvez utiliser des opérateurs conditionnels pour créer des conditions avec des nombres pour la fonction SUMIFS.
Pour trouver les ventes totales de plus de 5 quantités d'un article dans l'État de Californie (CA), utilisez cette formule :
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Cette formule a deux conditions : « >5 » et « CA ».
Cette formule vérifie les quantités (Qté) supérieures à 5 dans la plage D2:D13 et vérifie l'état "CA" dans la plage B2:B13. Et lorsque les deux conditions sont remplies (c'est-à-dire qu'il y en a dans la même ligne), il additionne le montant dans E2:E13.
SUMIFS avec critères de date
La fonction SUMIFS vous permet également de vérifier plusieurs conditions dans la même plage ainsi que des plages différentes.
Supposons que vous souhaitiez vérifier le montant total des ventes des articles livrés après le 31/5/2021 et avant la date du 10/6/2021, puis utilisez cette formule :
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)La formule ci-dessus a trois conditions : 31/5/2021, 10/5/2021 et Livré. Au lieu d'utiliser des valeurs de date et de texte directes, nous nous sommes référés aux cellules contenant ces critères.
La formule vérifie les dates après le 31/5/2021 (G1) et les dates avant le 10/6/2021 (G2) dans la même plage D2:D13, et vérifie le statut « Livré » entre ces deux dates. Ensuite, additionne le montant connexe dans la plage E2:E13.
SUMIFS avec cellules vides et non vides
Parfois, vous souhaiterez peut-être trouver la somme des valeurs lorsqu'une cellule correspondante est vide ou non. Pour ce faire, vous pouvez utiliser l'un des trois critères dont nous avons parlé précédemment : "="", "" et "".
Par exemple, si vous souhaitez additionner uniquement le montant des articles « Tente » pour lesquels la date de livraison n'a pas encore été confirmée (cellules vides), vous pouvez utiliser le critère « =" :
=SUMIFS(D2:D13,A2:A13,"Tente",C2:C13,"=")La formule recherche l'élément « Tente » (critères1) dans la colonne A avec les cellules vides correspondantes (critères2) dans la colonne C, puis additionne le montant correspondant dans la colonne D. Le « =" représente une cellule complètement vide.
Pour trouver le montant total des articles « Tente » pour lesquels la date de livraison a été confirmée (pas de cellules vides), utilisez « » comme critère :
=SUMIFS(D2:D13,A2:A13,"Tente",C2:C13,"")Nous venons d'échanger "=" pour "" dans cette formule. Il trouve la somme des éléments de la tente avec des cellules non vides dans la colonne C.
SUMIFS avec OU Logique
Étant donné que la fonction SUMIFS fonctionne sur la logique ET, elle ne fait la somme que lorsque toutes les conditions sont remplies. Mais que se passe-t-il si vous souhaitez additionner la valeur en fonction de plusieurs critères lorsque l'un des critères est rempli. L'astuce consiste à utiliser plusieurs fonctions SUMIFS.
Par exemple, si vous souhaitez additionner le montant des ventes pour « Porte-vélos » OU « Sac à dos » lorsque leur statut est « Commandé », essayez cette formule :
=SUMIFS(D2:D13,A2:A13,"Porte-vélos",C2:C13,"Commandé") +SUMIFS(D2:D13,A2:A13,"Sac à dos",C2:C13,"Commandé")La première fonction SUMIFS vérifie deux critères « Porte-vélos » et « Commandé » et additionne les valeurs des montants dans la colonne D. Ensuite, la deuxième SUMIFS vérifie deux critères « Sac à dos » et « Commandé » et additionne les valeurs des montants dans la colonne D. Et puis , les deux sommes sont additionnées et affichées sur F3. En termes simples, cette formule s'additionne lorsque « Porte-vélos » ou « Sac à dos » est commandé.
C'est tout ce que vous devez savoir sur les fonctions SUMIF et SUMIFS dans Google Sheets.