Un guide étape par étape pour utiliser les sumifs dans Microsoft Excel

La fonction SUMIFS de Microsoft Excel calcule la somme des valeurs dans une plage de cellules basées sur plusieurs conditions. Il évite le besoin de filtrage complexe, et ses conditions peuvent être des nombres, du texte ou des dates, ce qui le rend idéal pour l'analyse dynamique des données.

La fonction SUMIFS est disponible pour ceux qui utilisent Excel 2016 ou version ultérieure, Excel pour Microsoft 365, Excel pour le Web ou les applications mobiles et tablettes Excel.

La différence entre Sumif et Sumifs

La meilleure façon de voir comment Sumif et Sumifs diffèrent est de comparer leurs syntaxes.

Voici la syntaxe Sumif:

=SUMIF(a,b,(c))

où

• un (requis) est la plage à tester,
• b (requis) est le critère de la plage ci-dessus, et
• c (Facultatif) est la plage contenant les valeurs à résumer.

Vous n'avez qu'à inclure l'argument c Si la plage contenant les valeurs à résoudre diffère de la plage contenant les valeurs à tester. Si vous omettez l'argument cExcel résumera les valeurs de la plage identifiées en argument un.

Maintenant, voyons en quoi cela diffère de la syntaxe Sumifs:

=SUMIFS(a,b¹,b²,(c¹,c²,...))

où

• un (requis) est la gamme de cellules à résumer,
• B¹ et b² (requis) Formez le premier couple de critère de gamme, où B¹ est la plage à tester, et b² est le critère, et
• C¹ et c² (Facultatif) Formez le premier de 126 appariements supplémentaires de critère de plage.

Si vous constatez que vous n'avez qu'à utiliser les arguments un, B¹et b²vous pourriez envisager d'utiliser la fonction SUMIF à la place. Cependant, l'avantage de s'en tenir aux Sumifs est que la formule est prête à d'autres critères que vous pourriez ajouter en fin de compte.

Ainsi, il existe trois différences clés entre SUMIF et SUMIFS:

• SUMIF vous permet de spécifier une seule condition, tandis que SUMIFS vous permet d'en spécifier beaucoup.

• L'argument contenant la plage à résumer est facultatif à la fin de la syntaxe Sumif, tandis que le même argument est obligatoire au début de la syntaxe Sumifs.

• Les plages testées pour chaque critère doivent être de la même taille que la plage de cellules à résumer lors de l'utilisation de la fonction SUMIFS, alors qu'il n'y a pas une telle exigence avec la fonction SUMIF.

Utilisation de Sumifs avec des nombres

La fonction SUMIFS dans Excel est idéale pour additionner les valeurs dans une colonne basée sur plusieurs critères numériques.

Dans cette table Excel, nommée T_staffla colonne A contient des numéros d'identification des employés, la colonne B contient des âges des employés, la colonne C contient le nombre d'années que chaque employé a travaillé pour l'entreprise et la colonne D détaille le nombre de tâches terminées par chaque employé.

Imaginons que vous souhaitez agréger les tâches effectuées en fonction de l'âge (colonne B) et du nombre d'années de service (colonne C). Plus précisément, vous souhaitez déterminer le nombre total de tâches accomplies par les employés de plus de 40 ans avec plus de trois ans d'expérience.

Pour ce faire, dans une cellule vierge, type:

=SUMIFS(T_Staff(Tasks_completed),T_Staff(Age),">40",T_Staff(Years),">3")

où

• T_staff (tasks_completed) dit à Excel que vous souhaitez résumer les valeurs dans la colonne Tasks_Completed de la table T_Staff,
• T_staff (âge), « >40 «  dit à Excel de rechercher dans la colonne d'âge des valeurs supérieures à 40
• T_staff (années), « >3 «  Indique à Excel de rechercher la colonne des années des valeurs supérieures à 3.

Les expressions logiques avec les opérateurs de comparaison (comme>, <,> =, <= et <>) doivent toujours être enfermées en doubles citations («  »). Sinon, si les critères sont des correspondances numériques exactes, vous pouvez simplement taper le nombre.

Ce n'est que sur les lignes où les deux critères sont remplis que les valeurs de la colonne TASKS_COMPLETED seront additionnées.

Voici ce que vous obtenez lorsque vous appuyez sur Entrée:

C'est génial, mais pour le moment, les conditions sont codées, ce qui signifie que si vous voulez les changer, vous devez modifier la formule. C'est pourquoi vous devriez placer les critères dans des cellules séparées et référencer ces cellules dans la formule à la place:

=SUMIFS(T_Staff(Tasks_completed),T_Staff(Age),G1,T_Staff(Years),G2)

Cette formule produit le même résultat que la formule à code dur, mais maintenant, vous pouvez facilement modifier les paramètres des cellules G1 et G2. De plus, placer les opérateurs logiques dans les cellules référencées évite la nécessité d'utiliser des citations doubles.

Allons plus loin et rendons la formule encore plus dynamique en séparant les opérateurs logiques des conditions numériques.

Maintenant, vous pouvez utiliser le symbole ampère et (&) dans la formule pour concaténer les opérateurs logiques et les conditions numériques:

=SUMIFS(T_Staff(Tasks_completed),T_Staff(Age),G1&H1,T_Staff(Years),G2&H2)

En conséquence, non seulement vous pouvez changer rapidement les paramètres numériques dans les cellules J9 et J10, mais vous pouvez également changer les opérateurs logiques dans les cellules I9 et I10. Dans cet exemple, la formule renvoie le nombre de tâches accomplies par les employés de 60 ans ou moins avec moins de trois ans d'expérience:

Enfin, vous pouvez utiliser SUMIFS pour résumer les valeurs dont les cellules correspondantes se situent dans une plage. Par exemple, taper:

=SUMIFS(T_Staff(Tasks_completed),T_Staff(Age),G1&H1,T_Staff(Age),G2&H2)

Somme les valeurs dans la colonne TASKS_COMPLETED pour le personnel âgé de 20 à 50 ans, inclusif.

Utilisation de Sumifs avec du texte (et des caractères génériques)

La fonction SUMIFS fonctionne de manière similaire avec du texte comme avec les nombres, mais il existe deux différences clés. Tout d'abord, le texte dans les formules Excel doit toujours entrer à l'intérieur des citations doubles, et deuxièmement, vous pouvez utiliser les caractères génériques pour des matchs partiels.

Supposons que vous créiez une liste de lecture à partir d'une liste de chansons de la table Excel nommée T_songs.

Plus précisément, vous voulez déterminer la durée d'une liste de lecture qui comprend des chansons cotées A qui ont vendu le plus d'empières aux États-Unis

Pour ce faire, tapez:

=SUMIFS(T_Songs(Length),T_Songs(Rating),"A",T_Songs(Most_sales),"US")

où

• T_songs (longueur) dit à Excel de résumer les valeurs dans la colonne de longueur de la table T_Songs,
• T_songs (note), « a » Indique à Excel de rechercher la colonne de notation pour la valeur du texte UNet
• T_songs (la plupart_sales), « nous » Indique à Excel de rechercher la colonne la plus_sales ou la valeur du texte NOUS.

Remarquez comment toutes les valeurs de texte dans la formule sont enveloppées en doubles guillemets. Sinon, Excel supposera qu'il recherche des valeurs numériques, il renverra donc une erreur.

Voici le résultat lorsque vous appuyez sur Entrée:

N'oubliez pas, cependant, que les formules sont plus dynamiques lors de la référence des cellules contenant les conditions. Cette formule renvoie le même résultat:

=SUMIFS(T_Songs(Length),T_Songs(Rating),H1,T_Songs(Most_sales),H2)

Comme vous pouvez le voir dans la formule ci-dessus, un autre avantage de référencer les cellules au lieu de coder dur les critères est que, la plupart du temps, vous pouvez omettre les doubles citations, ce qui rend la formule plus rapide à produire et plus facile à lire.

Dans un deuxième exemple, imaginons que vous voulez déterminer la longueur d'une playlist de chansons contenant la chaîne de texte noir qui a vendu le plus d'empières aux États-Unis, c'est là que les jokers entrent en jeu:

=SUMIFS(T_Songs(Length),T_Songs(Song),"*black*",T_Songs(Most_sales),"US")

Ici, « *noir* » dit à Excel que le critère de la colonne de la chanson du tableau T_Songs est toute valeur de texte (d'où les doubles guillemets) contenant noir. Chaque astérisque représente n'importe quel nombre de caractères – en d'autres termes, la chaîne de texte noir

Peut avoir n'importe quel nombre de caractères avant ou après, ce qui signifie qu'il peut apparaître dans n'importe quelle partie du titre d'une chanson.D'un autre côté, il n'y a pas besoin de personnages génériques pour le deuxième critère (NOUS

) Parce que nous cherchons à retourner un match entier, pas partiel.

=SUMIFS(T_Songs(Length),T_Songs(Song),"*"&H1&"*",T_Songs(Most_sales),H2)

Voici l'alternative de style référence: Cette fois, les caractères génériques d'astérisque sont chacun placés à l'intérieur des citations doubles et concaténées avec la référence cellulaire en utilisant des signes AmperSand (&), représentant finalement la chaîne *noir*

Comme dans la formule précédente.

L'autre caractère générique dans Excel est le point d'interrogation (?), Qui représente un seul caractère. De plus, si vous souhaitez inclure des astérisques ou des marques d'interrogation en critères en tant que personnages à part entière, placez un tilde (~) devant eux.

Utilisation de Sumifs avec des dates

Une utilisation négligée de la fonction SUMIFS résume une plage en fonction des critères de date. Cette table Excel, nomméeT_tasques

contient divers projets, leur coût et leurs dates d'échéance d'achèvement.

Votre objectif est d'élaborer le coût de tous les projets dus en février et mars 2025.

Comme je l'ai expliqué dans tous les exemples ci-dessus, référencer les cellules plutôt que des conditions de codage dur est souvent la meilleure méthode, non seulement parce qu'elle rend la formule dynamique, mais aussi parce que les formules sont plus propres. C'est particulièrement le cas avec les dates, où Excel est très particulier sur le format dans lequel ils sont entrés.

=SUMIFS(T_Tasks(Cost),T_Tasks(Due),F1&G1,T_Tasks(Due),F2&G2)

Voici donc la formule basée sur les références que vous devez insérer pour ce scénario:

• où T_tasks (coût)
• La colonne contenant les valeurs à résumer, T_tasks (due), F1 et G1 concaténe l'opérateur logique plus grand que ou égal à la cellule F1 (qui représenteplus tard que ou le même jour que
• ) et la date de début dans Cell G1 pour définir la date la plus ancienne à rechercher dans la colonne due, et T_tasks (due), F2 & G2 concatène l'opérateur logique moins que ou égal à la cellule F2 (qui représenteplus tôt ou le même jour que

) et la date de fin de Cell G2 pour définir la dernière date à rechercher dans la colonne due.

Dans la capture d'écran ci-dessus, un format de numéro de date personnalisé est appliqué aux cellules G1 et G2, et le format de numéro comptable est appliqué à la cellule F3.

Utilisation de sumifs avec ou logique Par défaut, la fonction SUMIFS fonctionne avec ET Logique – en d'autres termes, les valeurs de la plage identifiées par argument un ne sont additionnées que lorsque tous

Les critères des arguments suivants sont remplis. Cependant, avec quelques ajustements à la formule, vous pouvez introduire OU logique, où les valeurs de la plage spécifiées sont additionnées lorsque un

de plusieurs critères est rempli. Revenons à la table T_Songs, mais cette fois, vous voulez calculer la longueur totale de toutes les chansons dont les titres contiennent les cordes de texte rouge ou

noir.

=SUMIFS(T_Songs(Length),T_Songs(Song),{"*black*","*red*"})

Une façon d'y parvenir est d'utiliser des constantes de tableau, que vous devez enfermer en accolades bouclées:

• où T_songs (longueur)
• La colonne contenant les valeurs à résumer, T_songs (chanson)
• est la plage contenant les valeurs que vous souhaitez évaluer, et {« * noir * », « * rouge * »} recherche cette colonne pour les chaînes de texte noir ourouge

. Lorsque vous appuyez sur Entrée, vous obtenez un tableau dynamique renversé de deux résultats, car Excel évalue efficacement la colonne de la chanson deux fois – une fois pour la chaîne de textenoir et une fois pour la chaîne de texterouge

– Retourner chaque résultat dans des cellules séparées.

=SUM(SUMIFS(T_Songs(Length),T_Songs(Song),{"*black*","*red*"}))

Cependant, puisque l'objectif est de produire un seul résultat, vous devez nicher toute la formule à l'intérieur de la fonction de somme:

L'inconvénient de cette méthode est que vous ne pouvez pas utiliser de références de cellules à l'intérieur des constantes de tableau, ce qui signifie que vous devez coder dur les critères.

=SUMIFS(T_Songs(Length),T_Songs(Song),"*"&H1&"*")
+
SUMIFS(T_Songs(Length),T_Songs(Song),"*"&J1&"*")

Pour contourner cela, vous pouvez combiner plusieurs formules SUMIFS à l'aide du signe Add (+). J'ai divisé la formule ci-dessous sur des lignes distinctes afin que vous puissiez voir clairement comment chaque partie fonctionne: Cependant, cette structure se traduit par une longue formule si vous en avez plusieurs OU

---

Critères, gardez donc cela à l'esprit avant de décider quelle méthode utiliser.