Fonction SUMIFS, comment utiliser la fonction pour additionner plusieurs conditions dans Excel

La fonction Sumifs dans Excel est extrêmement utile lorsque vous devez additionner de nombreuses conditions. Vous trouverez ci-dessous des détails sur la façon d'utiliser la fonction Sumifs dans Microsoft Excel .

La fonction SUMIFS dans Excel est l'une des fonctions de base d'Excel , une fonction de calcul couramment utilisée dans Excel. Pour calculer le total dans Excel, nous utiliserons la fonction SUM . Si nous voulons ajouter une certaine condition à la fonction total, nous utiliserons la fonction SUMIF . Dans les cas où le tableau de données nécessite une somme avec de nombreuses conditions, nous devons utiliser la fonction SUMIFS. L'article ci-dessous vous expliquera comment utiliser la fonction SUMIFS dans Excel.

• MS Excel - Leçon 5 : Formules et fonctions Excel
• Comment combiner la fonction Sumif et la fonction Vlookup dans Excel
• 3 façons de calculer des totaux dans Excel
• Comment corriger l'erreur de la fonction SUM qui ne s'ajoute pas dans Excel

Table des matières de l'article

• Syntaxe de la fonction SUMIFS dans Excel
• Remarque lors de l'utilisation de la fonction SUMIFS dans Excel
• Instructions d'utilisation de la fonction SUMIFS dans Excel
  • Utiliser des caractères génériques
  • Utiliser des plages nommées avec la fonction SUMIFS
  • Exemple de fonction SUMIFS dans Excel
• Limites de la fonction SUMIFS

Syntaxe de la fonction SUMIFS dans Excel

La syntaxe de la fonction SUMIFS dans Excel est =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).

Là-dedans :

• Sum_range : sont les cellules qui doivent être additionnées dans le tableau de données, les valeurs vides et les valeurs de texte sont ignorées, paramètres obligatoires.
• Criteria_range1 : la plage qui doit être testée à l’aide du critère criteria1, est une valeur obligatoire.
• Critères 1 : condition appliquée à critères_plage1, peut être un nombre, une expression, une référence de cellule pour déterminer quelles cellules de critères_plage1 seront additionnées, également une valeur obligatoire.
• Criteria_range2, critères2,… : plages et conditions supplémentaires facultatives, maximum 127 paires de critères_range, critères.

Remarque lors de l'utilisation de la fonction SUMIFS dans Excel

• Pour chaque cellule de la plage, la plage sum_range sera additionnée lorsque toutes les conditions correspondantes déterminées comme étant vraies pour cette cellule seront remplies.
• Les cellules de sum_range qui contiennent TRUE sont considérées comme 1, les cellules contenant FALSE sont considérées comme 0.
• Chaque critère_range doit avoir la même taille de sélection que sum_range, critères_range et sum_range doivent avoir le même nombre de lignes et le même nombre de colonnes.
• Les critères peuvent utiliser un point d'interrogation (?) au lieu d'un seul caractère, un astérisque (*) au lieu d'une chaîne. Si la condition est un point d'interrogation ou un astérisque, vous devez saisir un signe ~ devant, la valeur de la condition est le texte entouré de ".
• Plusieurs conditions sont appliquées à l'aide de la logique ET, c'est-à-dire critères1 ET critères2, etc.
• Chaque plage supplémentaire doit avoir le même nombre de lignes et de colonnes que la plage totale, mais il n'est pas nécessaire que les plages soient contiguës. Si vous fournissez des plages qui ne correspondent pas, vous obtiendrez une erreur #VALUE.
• La chaîne de texte dans les critères doit être placée entre guillemets (""), c'est-à-dire "apple", ">32", "jap*"
• Les références de cellules dans les critères ne peuvent pas être placées entre guillemets, c'est-à-dire "<>
• SUMIF et SUMIFS peuvent gérer des plages, mais ne peuvent pas gérer des tableaux. Cela signifie que vous ne pouvez pas utiliser d'autres fonctions comme YEAR sur la plage de critères car le résultat est un tableau. Si vous avez besoin de cette fonctionnalité, utilisez la fonction SUMPRODUCT.
• L'ordre des arguments entre les fonctions SUMIFS et SUMIF est différent. La plage de somme est le premier argument de SUMIFS, mais le troisième argument de SUMIF.

Instructions d'utilisation de la fonction SUMIFS dans Excel

Utiliser des caractères génériques

Caractères génériques tels que « * » et « ? » peut être utilisé dans l'argument critère lors de l'utilisation de la fonction SUMIFS. L'utilisation de ces caractères génériques vous aidera à trouver des correspondances similaires mais pas exactes.

Astérisque (*) - Il correspond à n'importe quelle chaîne de caractères, qui peut être utilisée après, avant ou avec les critères environnants pour permettre une utilisation partielle des critères de recherche.

Par exemple, si vous appliquez les critères suivants dans la fonction SUMIFS :

• N * - Implique toutes les cellules de la plage commençant par N
• * N - Implique toutes les cellules de la plage se terminant par N
• *N* - Cellules contenant N

Point d'interrogation (?) - Correspond à n'importe quel caractère. Supposons que vous appliquiez N?r comme critère. "?" ici remplacera un seul caractère. N?r correspondra au Nord, Nor, etc. Cependant, il ne prendra pas en compte le Nom.

Que se passe-t-il si les données fournies contiennent un astérisque ou un vrai point d'interrogation ?

Dans ce cas, vous pouvez utiliser le tilde (~). Dans ce cas, vous devez taper « ~ » avant le point d’interrogation.

Utiliser des plages nommées avec la fonction SUMIFS

La plage nommée est le nom descriptif d’un ensemble de cellules ou d’une plage de cellules dans une feuille de calcul. Vous pouvez utiliser des plages nommées tout en utilisant la fonction SUMIFS.

Exemple de fonction SUMIFS dans Excel

Nous calculerons le total avec le tableau de données ci-dessous avec un certain nombre de conditions différentes.

Exemple 1 : Calculez le total des produits vendus par l'employé de Hoai avec un prix unitaire inférieur à 400 000 VND.

Dans la zone de saisie du résultat, nous entrons la formule =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") puis="" press="">

Là-dedans :

• D2:D7 est la zone dans laquelle l'élément total doit être calculé.
• C2:C7 est la plage de valeurs de la condition du nom de l'employé.
• « Hoai » est la condition pour le nom de l'employé qui doit calculer le nombre d'éléments contenus dans la zone C2:C7.
• E2:E7 est l'état de l'article.
• « 400 000 » est la condition pour contenir des éléments dans les zones E2:E7.

Le résultat est le nombre total d’éléments comme indiqué ci-dessous.

Exemple 2 : Calculer le nombre total d'articles vendus par les employés russes avec numéro de série <>

Dans la zone de saisie du résultat, nous entrons la formule =SUMIFS(D2:D7,C2:C7,"=Russia",A2:A7,"<5") puis="" press="" enter.="" à = "" name="" multiplication="" tablet="" "nga"="" enter="" sign="or" remove="" sign="even">

En conséquence, nous obtenons le nombre total d'articles vendus par les employés russes avec des numéros de série < 5="" soit 550 articles="">

Exemple 3 : Calculez le nombre total de produits vendus par les employés russes, hors produits foulards.

Dans la zone de saisie du résultat, nous entrons la formule =SUMIFS(D2:D7,C2:C7,« Russia »,B2:B7,« <>scarf »). Dans lequel le signe <> est utilisé pour exclure un certain objet dans la zone de données comme condition.

Le résultat est le nombre total de produits comme indiqué ci-dessous.

Exemple 4 :

Calculez le total de toutes les statistiques des Pokémon de type Eau de génération 1 :

La condition ici est que l'eau et la génération soient égales à 1.

Remarque : L'ensemble de données complet continue après la ligne 14, jusqu'à la ligne 759.

Voici les détails étape par étape :

1. Sélectionnez la cellule H3 .
2. Tapez =SUMIFS.
3. Double-cliquez sur la commande SUMIFS.
4. Sélectionnez la plage totale comme C2:C759.
5. Taper,
6. Sélectionnez la plage pour la première condition B2:B759(valeur de type 1)
7. Sélectionner des critères (cellule F3, avec valeur Water)
8. taper ,
9. Sélectionnez la plage pour la deuxième condition à partir de D2:D759(valeur de génération)
10. Taper,
11. Déterminez les critères (la cellule G3 a la valeur 1).
12. Appuyez sur Entrée .

Remarque : Vous pouvez ajouter d'autres conditions en répétant les étapes 9 à 12 avant d'appuyer sur Entrée.

Cette fonction calcule désormais la somme des statistiques totales des Pokémon Eau de génération 1. Cette fonction peut être répétée pour les générations futures afin de les comparer.

Vous trouverez ci-dessus quelques exemples d'utilisation de la fonction SUMIFS, en calculant la valeur totale avec de nombreuses conditions combinées. Nous devons organiser correctement les plages et les conditions qui les accompagnent afin qu'Excel puisse reconnaître la formule de fonction pour le calcul.

Limites de la fonction SUMIFS

La fonction SUMIFS présente certaines limitations dont vous devez être conscient :

• Les conditions dans SUMIFS sont combinées à l'aide de la logique ET. En d’autres termes, toutes les conditions doivent être VRAIES pour qu’une cellule soit incluse dans le total. Pour additionner des cellules à l’aide de la logique OU, vous pouvez utiliser une solution alternative dans des cas simples.
• La fonction SUMIFS nécessite des plages réelles pour tous les arguments de plage ; vous ne pouvez pas utiliser un tableau. Cela signifie que vous ne pouvez pas faire des choses comme extraire l'année d'une date dans la fonction SUMIFS. Pour modifier les valeurs qui apparaissent dans l'argument plage avant d'appliquer les critères, la fonction SUMPRODUCT est une solution flexible.
• SUMIFS n'est pas sensible à la casse. Pour additionner les valeurs en fonction d'une condition sensible à la casse, vous pouvez utiliser une formule basée sur la fonction SUMPRODUCT avec la fonction EXACT.

La manière la plus courante de contourner les limitations ci-dessus consiste à utiliser la fonction SUMPRODUCT. Dans les versions actuelles d'Excel, une autre option consiste à utiliser les nouvelles fonctions BYROW et BYCOL.

Comment corriger l'erreur #VALUE dans la fonction SUMIFS d'Excel

Problème : La formule fait référence à des cellules dans un classeur fermé

La fonction SUMIF qui référence une cellule ou une plage de cellules dans un classeur fermé entraînera une erreur #VALEUR !.

Remarque : Il s'agit d'une erreur courante. Vous rencontrez également souvent ce message lorsque vous utilisez d'autres fonctions Excel telles que COUNTIF, COUNTIFS, COUNTBLANK...

Solution:

Ouvrez le classeur contenu dans cette formule, appuyez sur F9 pour actualiser la formule. Vous pouvez également résoudre le problème en utilisant les fonctions SUM et IF dans une formule matricielle.

Problème : La chaîne de critères comporte plus de 255 caractères.

La fonction SUMIFS renvoie des résultats erronés lorsque vous essayez de combiner des chaînes de plus de 255 caractères.

Solution:

Raccourcissez la chaîne si possible. Si ce n'est pas possible, utilisez la fonction CONCATENATE ou l'opérateur & pour diviser la valeur en plusieurs chaînes. Par exemple:

=SUMIF(B2:B12,"long string"&"another long string")

Problème : Dans SUMIFS, l'argument critères_range est incohérent avec l'argument sum_range.

L'argument range doit toujours être le même dans SUMIFS. Cela signifie que les arguments critères_range et sum_range doivent faire référence au même nombre de lignes et de colonnes.

Dans l'exemple suivant, la formule renvoie le total des ventes quotidiennes de pommes à Bellevue. Cependant, l'argument sum_range (C2:C10) ne correspond pas au nombre de lignes et de colonnes dans critères_range (A2:A12 & B2:B12). L'utilisation d'une formule =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)donnera une erreur #VALUE.

Solution:

Passez sum_rangeà C2:C12 et réessayez la formule.

Je vous souhaite du succès !