Ce tutoriel montre comment utiliser le Excel SUMIF et SUMIFS Fonctiontion dans Excel et Google Sheets pour additionner les données qui répondent à certains critères.
Présentation de la fonction SUMIF
Vous pouvez utiliser la fonction SUMIF dans Excel pour additionner des cellules contenant une valeur spécifique, additionner des cellules supérieures ou égales à une valeur, etc.
(Remarquez comment les entrées de formule apparaissent)
Syntaxe et arguments de la fonction SUMIF :
| 1 | =SUMIF (plage, critères, [sum_plage]) |
gamme - La plage de cellules contre laquelle vous souhaitez appliquer les critères.
Critères - Les critères utilisés pour déterminer les cellules à ajouter.
sum_range - [optionnel] Les cellules à additionner. Si sum_range est omis, les cellules de la plage sont additionnées à la place.
Qu'est-ce que la fonction SUMIF ?
La fonction SUMIF est l'une des fonctions les plus anciennes utilisées dans les feuilles de calcul. Il est utilisé pour parcourir une plage de cellules en vérifiant un critère spécifique, puis en additionnant les valeurs d'une plage qui correspondent à ces valeurs. La fonction SUMIF d'origine était limitée à un seul critère. Après 2007, la fonction SUMIFS a été créée qui permet une multitude de critères. La plupart de l'utilisation générale reste la même entre les deux, mais il existe des différences critiques dans la syntaxe que nous aborderons tout au long de cet article.
Si vous ne l'avez pas déjà fait, vous pouvez consulter une grande partie de la structure et des exemples similaires dans l'article COUNTIFS .
Exemple de base
Considérons cette liste de ventes enregistrées, et nous voulons connaître le revenu total.
Parce que nous avons eu une dépense, la valeur négative, nous ne pouvons pas simplement faire une somme de base. Au lieu de cela, nous voulons additionner uniquement les valeurs supérieures à 0. Le "supérieur à 0" est ce qui sera notre critère dans une fonction SUMIF. Notre formule pour énoncer ceci est
| 1 | =SOMMEIF(A2:A7, ">0") |
Exemple à deux colonnes
Alors que la fonction SUMIF d'origine a été conçue pour vous permettre d'appliquer un critère à la plage de nombres que vous souhaitez additionner, la plupart du temps, vous devrez appliquer un ou plusieurs critères à d'autres colonnes. Considérons ce tableau :
Maintenant, si nous utilisons la fonction SUMIF d'origine pour savoir combien de bananes nous avons (répertoriées dans la cellule D1), nous devrons donner la plage que nous voulons somme comme dernier argument, et donc notre formule serait
| 1 | =SOMMEIF(A2:A7, D1, B2:B7) |
Cependant, lorsque les programmeurs ont finalement réalisé que les utilisateurs voulaient donner plus d'un critère, la fonction SUMIFS a été créée. Afin de créer une structure qui fonctionnerait pour n'importe quel nombre de critères, le SUMIFS exige que la plage de somme soit répertoriée en premier. Dans notre exemple, cela signifie que la formule doit être
| 1 | =SUMIFS(B2:B7, A2:A7, D1) |
REMARQUE : Ces deux formules obtiennent le même résultat et peuvent se ressembler. Faites donc très attention à la fonction utilisée pour vous assurer de répertorier tous les arguments dans le bon ordre.
Travailler avec des dates, plusieurs critères
Lorsque vous travaillez avec des dates dans une feuille de calcul, bien qu'il soit possible de saisir la date directement dans la formule, il est préférable d'avoir la date dans une cellule afin que vous puissiez simplement référencer la cellule dans une formule. Par exemple, cela aide l'ordinateur à savoir que vous souhaitez utiliser la date du 27/05/2020, et non le nombre 5 divisé par 27 divisé par 2022.
Regardons notre prochain tableau enregistrant le nombre de visiteurs sur un site toutes les deux semaines.
Nous pouvons spécifier les points de début et de fin de la plage que nous voulons regarder dans D2 et E2. Notre formule alors pour additionner le nombre de visiteurs dans cette gamme pourrait être :
| 1 | =SUMIFS(B2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2) |
Notez comment nous avons pu concaténer les comparaisons de « =" aux références de cellules pour créer les critères. De plus, même si les deux critères étaient appliqués à la même plage de cellules (A2:A7), vous devez écrire la plage deux fois, une fois pour chaque critère.
Plusieurs colonnes
Lorsque vous utilisez plusieurs critères, vous pouvez les appliquer à la même plage que nous l'avons fait avec l'exemple précédent, ou vous pouvez les appliquer à différentes plages. Combinons nos exemples de données dans ce tableau :
Nous avons configuré certaines cellules pour que l'utilisateur entre ce qu'il souhaite rechercher dans les cellules E2 à G2. Il nous faut donc une formule qui additionnera le nombre total de pommes cueillies en février. Notre formule ressemble à ceci :
| 1 | =SUMIFS(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, A2:A7, E2) |
SUMIFS avec logique de type OU
Jusqu'à présent, les exemples que nous avons utilisés ont tous été basés sur une comparaison ET, où nous recherchons des lignes qui répondent à tous nos critères. Maintenant, nous allons considérer le cas où vous souhaitez rechercher la possibilité d'une ligne répondant à l'un ou l'autre critère.
Regardons cette liste de ventes:
Nous aimerions additionner les ventes totales d'Adam et de Bob. Pour ce faire, vous avez plusieurs options. Le plus simple est d'ajouter deux SUMIFS ensemble, comme ceci :
| 1 | =SUMIFS(B2:B7, A2:A7, "Adam")+SUMIFS(B2:B7, A2:A7, "Bob") |
Ici, nous avons demandé à l'ordinateur de calculer nos scores individuels, puis nous les avons additionnés.
Notre prochaine option est bonne lorsque vous avez plus de plages de critères, de sorte que vous ne voulez pas avoir à réécrire la formule entière à plusieurs reprises. Dans la formule précédente, nous avons demandé manuellement à l'ordinateur d'ajouter deux SUMIFS différents ensemble. Cependant, vous pouvez également le faire en écrivant vos critères dans un tableau, comme ceci :
| 1 | =SOMME(SOMMES(B2:B7, A2:A7, {"Adam", "Bob"})) |
Regardez comment le tableau est construit à l'intérieur des accolades. Lorsque l'ordinateur évaluera cette formule, il saura que nous voulons calculer une fonction SUMIFS pour chaque élément de notre tableau, créant ainsi un tableau de nombres. La fonction SUM externe prendra alors ce tableau de nombres et le transformera en un nombre unique. En parcourant l'évaluation de la formule, cela ressemblerait à ceci :
| 123 | =SOMME(SOMMES(B2:B7, A2:A7, {"Adam", "Bob"}))=SOMME(27401, 43470)=70871 |
Nous obtenons le même résultat, mais nous avons pu écrire la formule un peu plus succinctement.
Traiter les blancs
Parfois, votre ensemble de données contient des cellules vides que vous devez rechercher ou éviter. La configuration des critères pour ceux-ci peut être un peu délicate, alors regardons un autre exemple.
Notez que la cellule A3 est vraiment vide, tandis que la cellule A5 a une formule renvoyant une chaîne de longueur nulle de "". Si nous voulons trouver la somme totale de vraiment cellules vides, nous utiliserions un critère de « =" et notre formule ressemblerait à ceci :
| 1 | =SOMMES(B2:B7,A2:A7,"=") |
D'un autre côté, si nous voulons obtenir la somme pour toutes les cellules qui semblent vides visuellement, nous allons changer les critères en "", et la formule ressemble à
| 1 | =SUMIFS(B2:B7,A2:A7,"") |
Retournons les choses : et si vous voulez trouver la somme des cellules non vides ? Malheureusement, la conception actuelle ne vous permettra pas d'éviter la chaîne de longueur nulle. Vous pouvez utiliser un critère de "", mais comme vous pouvez le voir dans l'exemple, il inclut toujours la valeur de la ligne 5.
| 1 | =SUMIFS(B2:B7,A2:A7,"") |
Si vous ne devez pas compter les cellules contenant des chaînes de longueur nulle, vous devrez envisager d'utiliser la fonction LEN dans un SUMPRODUCT.
SUMIF dans Google Sheets
La fonction SUMIF fonctionne exactement de la même manière dans Google Sheets que dans Excel :
