Fonction SUMIFS() dans Excel : Somme de données avec plusieurs critères

La fonction SUMIFS() d'Excel additionne des nombres en fonction de plusieurs conditions, ce qui nous permet de contrôler précisément les calculs. Contrairement à SUMIF(), qui vérifie une seule condition, SUMIFS() traite plusieurs conditions à la fois.

Dans cet article, je vous montrerai comment utiliser SUMIFS() avec des exemples avancés et des conseils de dépannage. À la fin, vous saurez comment utiliser SUMIFS() pour rendre vos calculs plus précis et plus efficaces.

La réponse rapide

Pour utiliser la fonction SUMIFS() dans Excel :

• Tapez =SUMIFS( pour lancer la fonction.

• Sélectionnez la plage qui contient les valeurs à additionner.

• Sélectionnez la plage dans laquelle la condition sera appliquée.

• Saisissez la condition à remplir.

• Fermez les parenthèses et appuyez sur Entrée.

Ici, j'ai récapitulé toutes les ventes supérieures à 200.

=SUMIFS(B2:B8, B2:B8, ">200")

Additionnez les valeurs à l'aide de la fonction SUMIFS(). Image par l'auteur.

Comprendre la fonction SUMIFS()

La fonction SUMIFS() d'Excel n'additionne les valeurs que lorsque toutes les conditions spécifiées sont remplies. Il prend en charge les opérateurs logiques tels que supérieur à >, inférieur à <, égal à =, et différent de <>, ainsi que les caractères génériques pour les correspondances partielles. La fonction fonctionne avec des nombres, du texte et des dates.

Syntaxe de SUMIFS()

La fonction SUMIFS() définit d'abord la plage de valeurs à additionner, suivie de paires de plages de conditions et de critères. Notez que SUMIFS() peut gérer plus d'une condition logique.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Ici :

• sum_range est la plage de cellules que vous souhaitez ajouter.

• criteria_range1 est la première plage où nous vérifions la présence d'une condition.

• criteria1 est la condition appliquée à criteria_range1.

• [criteria_range2, criteria2], … sont facultatifs. Vous pouvez ajouter d'autres paires de plages et de conditions. Chaque paire ajoute une condition supplémentaire à respecter.

Notez que le site criteria_range (la plage où les conditions sont vérifiées) doit avoir le même nombre de lignes et de colonnes que le site sum_range (la plage à additionner).

Exemple de SUMIFS()

Prenons un exemple simple. Je dispose d'un ensemble de données sur les fruits avec leurs quantités et leurs ventes. Et je veux trouver les ventes totales de pommes dont la quantité est supérieure à 5. Ma formule serait donc la suivante :

=SUMIFS(C2:C8, A2:A8, "Apples", B2:B8, ">5")

Ici, C2:C8 est la plage de valeurs à additionner (Sales ($)). A2:A8, "Apples" est la condition pour n'inclure que les lignes où le produit est Pommes. Et B2:B8, ">5" est la condition pour inclure uniquement les lignes où la quantité est supérieure à 5.

La formule SUMIFS() permet d'additionner les ventes d'une condition. Image par l'auteur.

SUMIF() vs. SUMIFS()

La principale différence entre SUMIF() et SUMIFS() est le nombre de conditions qu'ils utilisent. Le site SUMIF() traite une seule condition. Mais SUMIFS() peut gérer plusieurs conditions. 

Cas d'utilisation de SUMIFS() dans Excel

Maintenant que vous savez comment fonctionne la fonction SUMIFS(), voyons quelques cas avancés où nous pouvons l'utiliser dans le monde réel.

Utilisation de SUMIFS() avec différents types de données

SUMIFS() peut travailler avec différents types de données tels que des nombres, du texte et des dates. Supposons que je dispose d'un rapport sur les ventes, mais qu'il manque des noms de clients dans certaines lignes. Et je ne veux récapituler que ceux pour lesquels nous disposons du nom du client. Pour cela, j'utilise la formule suivante :

=SUMIFS(C2:C5, A2:A5, "<>")

Cette formule examine la colonne Client, vérifie les cellules non vides et additionne les ventes correspondantes.

Additionnez toutes les cellules non vides à l'aide de la fonction SUMIFS(). Image par l'auteur.

Pour récapituler les ventes pour lesquelles il manque des noms de clients, j'utilise la formule suivante :  

=SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

Additionnez les cellules vides à l'aide de la fonction SUMIFS(). Image par l'auteur.

Au lieu de coder en dur la condition, nous pouvons la stocker dans une autre cellule. Si je veux faire la somme des ventes de Sarah Lee, je la tape dans la cellule E3 et j'utilise la formule suivante :

=SUMIFS(C2:C5, A2:A5, E3)

Chaque fois que la valeur de E3 change, le total est automatiquement mis à jour. 

Utilisez SUMIFS() pour additionner des valeurs en utilisant la référence de la cellule. Image par l'auteur.

Application de la logique OR dans SUMIFS()

Nous savons que SUMIFS() n'utilise que la logique AND, ce qui signifie que toutes les conditions que nous définissons doivent être vraies pour additionner les valeurs. Cependant, si nous voulons utiliser la logique OR, qui additionne les valeurs même si l'une des conditions est remplie, nous pouvons le faire en combinant plusieurs SUMIFS().

Par exemple, si je veux faire la somme des ventes de John Doe OU de Drake White, je combine deux formules SUMIFS() comme suit :

=SUMIFS(C2:C8, A2:A8, "John Doe") + SUMIFS(C2:C8, A2:A8, "Drake White")

Comme Drake White ne figure pas sur la liste, cette partie de la formule renvoie 0. Le total ne comprend que les ventes de John Doe, ce qui montre comment fonctionne la logique de OR.

Utilisez la logique OR dans la fonction SUMIFS(). Image par l'auteur.

Dans Excel 365 ou une version ultérieure, nous pouvons simplifier cette opération à l'aide d'une formule de tableau qui donnera le même résultat.

=SUMIFS(C2:C8,A2:A8,{"John Doe","Drake White"})

Utilisez la formule de tableau SUMIFS(). Image par l'auteur.

SUMIFS() avec des plages nommées

Nous pouvons également utiliser les plages nommées au lieu des références de cellules telles que A2:A5 ou C2:C5. Dans cet exemple, je sélectionne A2:A5 et je le nomme Customer_list et je sélectionne C2:C5 et je le nomme Sales_list. Maintenant, il s'agit de pour trouver les ventes totales de Jean, j'utilise la formule suivante :

=SUMIFS(Sales_list, Customer_list, "John")

Additionnez les valeurs à l'aide de SUMIFS() en utilisant les plages nommées. Image par l'auteur.

Utilisation de caractères génériques pour les correspondances partielles

Les caractères génériques sont des caractères spéciaux (* ou ?) qui représentent des caractères inconnus ou variables dans les recherches de texte et les correspondances de motifs : 

• * correspond à n'importe quel nombre de caractères (y compris zéro).

• ? correspond à un seul caractère.

Voici comment ils fonctionnent : 

• A* correspondra à toutes les cellules commençant par A.

• *A correspondra à toutes les cellules qui se terminent par A.

• *A* correspondra à toutes les cellules contenant A où que ce soit.

SUMIFS() permet d'effectuer des recherches dans le texte à l'aide de caractères génériques. Il identifie les valeurs qui commencent par, se terminent par ou contiennent des motifs de texte spécifiques. Supposons que je veuille faire la somme des ventes pour le code produit qui commence par ABC. Dans ce cas, ma formule se présente comme suit :

=SUMIFS(D2:D8, C2:C8, "ABC*")

Pour résumer les ventes qui se terminent par 00, j'écris:

=SUMIFS(B2:B7, A2:A7, "*00")

Utilisez des caractères génériques dans SUMIFS(). Image par l'auteur.

Somme basée sur des plages de dates

SUMIFS() peut également additionner des valeurs dans une plage de dates spécifique. Si je veux connaître toutes les ventes à partir de janvier 2023, j'utilise la formule suivante : 

=SUMIFS(D2:D8, B2:B8, ">=01-Jan-2023", B2:B8, "<=31-Jan-2023") 

Cette formule vérifiera les dates du 1er janvier 2023 au 31 janvier 2023 et fera la somme de toutes les ventes. 

Récapitulez la plage de dates à l'aide de la fonction SUMIFS(). Image par l'auteur.

Additionner des valeurs avec plusieurs conditions numériques

SUMIFS() peut additionner des valeurs comprises dans des plages numériques spécifiques. Ici, j'ai un ensemble de données aléatoires et je veux faire la somme des valeurs où Sales est supérieur à 100 mais inférieur à 300. Pour ce faire, j'utilise la formule suivante :  

=SUMIFS(C2:C5, C2:C5, ">100", C2:C5, "<300")

Cette formule spécifie d'abord la plage de cellules à additionner (C2:C5) comme premier argument. Ensuite, il applique les deux conditions décrites ci-dessus à la même plage. La fonction SUMIFS() évalue simultanément chaque cellule de la plage en fonction des deux critères. Vous pouvez voir que seules les cellules dont les valeurs sont comprises entre 100 et 300 (exclusif) sont incluses dans la somme. 

Additionnez les valeurs d'une plage de nombres à l'aide de la fonction SUMIFS(). Image par l'auteur.

Dépannage si SUMIFS() ne fonctionne pas

Si votre fonction SUMIFS() ne fonctionne pas comme prévu, vérifiez les points suivants :

Tailles des gammes non concordantes 

Si votre formule SUMIFS() renvoie une erreur, il se peut que la taille des plages ne soit pas la même. Dans SUMIFS(), toutes les plages que vous sélectionnez, comme sum_range et criteria_ranges, doivent avoir le même nombre de lignes et de colonnes.

Par exemple, dans cette formule, l'intervalle de la somme est B2:B8, mais l'intervalle des critères est A2:A7, qui compte une ligne de moins :

=SUMIFS(B2:B8, A2:A7, "Drake White")

Cette incompatibilité entraînera une erreur sur le site #VALUE!. Veillez donc à ce que toutes les plages aient la même taille, comme ceci :

=SUMIFS(B2:B8, A2:A8, "John Doe")

Formatage incorrect des critères de texte 

Dans SUMIFS(), vous devez mettre les critères de texte entre guillemets. Si vous ne le faites pas, vous risquez d'obtenir des résultats erronés. Par exemple, cette formule renverra 0 car Excel considère Drake White comme un nom ou une variable, et non comme du texte :

=SUMIFS(B2:B8, A2:A8, Drake White)

Pour y remédier, mettez le Drake White entre guillemets comme ceci et vous obtiendrez le résultat correct :

=SUMIFS(B2:B8, A2:A8, "Drake White")

Valeurs zéro inattendues

Si la formule renvoie 0 alors que vous attendez un résultat, cela peut être dû à un formatage incorrect des conditions numériques ou des conditions basées sur la date. Dans SUMIFS(), les conditions telles que >100 ou <01-Jan-2023 doivent être écrites sous forme de chaînes de texte entre guillemets. Sinon, il renverra une erreur ou 0 parce que la condition n'est pas entre guillemets.

Vous pouvez également stocker la date dans une cellule distincte (par exemple, C1) et l'utiliser :

=SUMIFS(B2:B8, A2:A8, ">="&C1)

SUMIFS() ne fonctionne pas avec les transformations de tableaux

SUMIFS() ne prend pas en charge certaines transformations telles que l'extraction de composants spécifiques à partir des données de la formule. Par exemple, si je veux faire la somme des ventes de janvier et de mars, SUMIFS() ne fonctionnera pas directement avec les critères de tableau {"janvier", "mars"}. 

Pour illustrer cela, supposons que j'ai des mois dans la colonne A (janvier, février, mars, avril) et des valeurs de vente dans la colonne B (100, 200, 300, 400). La formule

=SUMIFS(B2:B5, A2:A5, {"January","March"})

ne renverra pas la somme attendue de 400 (100 + 300). Au lieu de cela, nous pouvons utiliser SUMPRODUCT():

=SUMPRODUCT((A2:A5={"January","March"})*B2:B5)

ou enveloppez SUMIFS() avec SUM() en utilisant cette formule :

=SUM(SUMIFS(B2:B5, A2:A5, {"January","March"}))

Les deux solutions traiteront correctement les critères du tableau et renverront la somme des ventes pour janvier et mars.

Bonnes pratiques pour l'utilisation de SUMIFS()

Pour tirer le meilleur parti de la fonction SUMIFS(), nous devons éviter les erreurs courantes en suivant ces bonnes pratiques : 

• Utilisez des caractères génériques pour un filtrage souple : Il est conseillé d'utiliser des caractères génériques lors du filtrage des données, car il arrive que nos données ne soient pas parfaitement organisées. Les noms des produits ne sont peut-être pas toujours orthographiés de la même manière, ou il y a des espaces supplémentaires. Les caractères génériques vous permettent de faire correspondre des motifs sans avoir besoin d'un texte exact.

• Évitez de coder des critères en dur : Au lieu de saisir les conditions directement dans votre formule, stockez-les dans une cellule distincte. Si vous codifiez en dur une condition telle que "Drake White" ou ">100", vous devrez modifier la formule à chaque fois que vous voudrez la changer. Cependant, avec les références de cellules, la formule sera mise à jour automatiquement.

• Utilisez des références absolues lorsque vous copiez des formules : Lorsque vous copiez une formule SUMIFS() dans d'autres cellules, Excel ajuste automatiquement les références des cellules. Si vous ne voulez pas que cela se produise, utilisez des références absolues en ajoutant $ pour verrouiller les cellules en place.

Réflexions finales

SUMIFS() est une fonction Excel qui nous aide à analyser les données en filtrant et en additionnant les nombres qui correspondent à des critères spécifiques. C'est un outil idéal pour effectuer des tâches telles que le suivi des ventes par région, la synthèse des dépenses par catégorie ou le comptage des stocks par type de produit. Une fois que vous serez à l'aise avec SUMIFS(), vous vous rendrez compte qu'il vous fait gagner du temps et qu'il vous aide à repérer les tendances importantes dans vos données sans avoir à recourir à des solutions compliquées.

Si vous souhaitez développer ces compétences, consultez nos cours Analyse des données dans Excel, Préparation des données dans Excel et Visualisation des données dans Excel ou le cursus de compétences Fondamentaux d'Excel. Ils vous aideront à mettre à jour vos compétences existantes.