Ce tutoriel explique la fonction Excel Sumif en anglais simple. L'accent principal est sur les exemples de formules réels avec toutes sortes de critères, y compris le texte, les nombres, les dates, les jokers, les blancs et les non-blanks.

Microsoft Excel a une poignée de fonctions pour résumer de grands ensembles de données pour les rapports et les analyses. L'une des fonctions les plus utiles qui peut vous aider à comprendre un ensemble incompréhensible de données diverses est Sumif. Au lieu d'additionner tous les nombres dans une plage, cela vous permet de résumer uniquement les valeurs qui répondent à vos critères.

Ainsi, chaque fois que votre tache nécessite une somme conditionnelle dans Excel, la fonction SUMIF est ce dont vous avez besoin. Une bonne chose est que la fonction est disponible dans toutes les versions, d'Excel 2000 à Excel 365. Une autre grande chose est qu'une fois que vous avez appris Sumif, cela vous fera très peu d'efforts pour ma?triser d'autres fonctions "IF" telles que Sumifs, Coutif, Coups, moyenne, etc.

Sumif in Excel - syntaxe et utilisations de base

La fonction SUMIF , également connue sous le nom de somme conditionnelle Excel, est utilisée pour additionner les valeurs cellulaires en fonction d'une certaine condition.

La fonction est disponible dans Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 et Lower.

La syntaxe est la suivante:

Comme vous le voyez, la fonction SUMIF a 3 arguments - les 2 premiers sont requis et le dernier est facultatif.

• Plage (requise) - La plage de cellules à évaluer par des critères .
• Critères (requis) - La condition qui doit être remplie. Il peut être fourni sous la forme d'un nombre, d'un texte, d'une date, d'une expression logique, d'une référence cellulaire ou d'une autre fonction Excel. Par exemple, vous pouvez saisir les critères tels que "5", "cerises", "10/25/2014", "
• SUM_RANGE (Facultatif) - La plage à résumer si la condition est remplie. S'il est omis, la plage est additionnée.

Note. Veuillez prêter attention que tous les critères ou critères de texte contenant des opérateurs logiques doivent être enfermés à deux guillemets, par exemple "pommes", "> 10". Les références cellulaires doivent être utilisées sans les guillemets, sinon elles seraient traitées comme des cha?nes de texte.

Formule de base de Sumif

Pour mieux comprendre la syntaxe Sumif, considérez l'exemple suivant. Supposons que vous ayez une liste de produits dans la colonne A, des régions de la colonne B et des montants de vente dans la colonne C. Votre objectif est d'obtenir un total de ventes pour une région spécifique, disons North . Pour l'avoir fait, construisons une formule Excel Sumif sous sa forme la plus simple.

Vous commencez par définir les arguments suivants:

• Plage - Une liste des régions (B2: B10).
• Critères - "Nord" ou une cellule contenant la région d'intérêt (F1).
• SUM_RANGE - Les montants des ventes doivent être additionnés (C2: C10).

En rassemblant les arguments, nous obtenons la formule suivante:

=SUMIF(B2:B10, "north", C2:C10)

ou

=SUMIF(B2:B10, F1, C2:C10)

Les deux formules ne résument que les ventes dans la région du Nord :

Note. Le paramètre SUM_RANGE définit en fait uniquement la cellule la plus à gauche supérieure de la plage à résumer. La zone restante est définie par les dimensions de l'argument de la plage. En pratique, cela signifie que l'argument SUM_RANGE ne doit pas nécessairement être de la même taille que l'argument de portée , c'est-à-dire qu'il peut avoir un nombre différent de lignes et de colonnes. Cependant, la cellule supérieure gauche doit toujours être la droite. Par exemple, dans la formule ci-dessus, vous pouvez fournir C2, ou C2: C4, ou même C2: C100 comme argument SUM_RANGE , et le résultat sera toujours correct. Cependant, la meilleure pratique consiste à fournir une plage de taille égale et SUM_RANGE .

Note. La fonction SUMIF est par nature insensible à la casse. Cependant, il est possible de le forcer à reconna?tre le cas de texte. Pour plus de détails, veuillez consulter SUMIF sensible à la casse dans Excel.

Comment utiliser Sumif dans Excel - Exemples de formule

Espérons que l'exemple ci-dessus vous a aidé à acquérir une compréhension de base du fonctionnement de la fonction. Ci-dessous, vous trouverez quelques formules supplémentaires qui montrent comment utiliser SUMIF dans Excel avec divers critères.

Sumif supérieur ou inférieur

Pour résumer les nombres supérieurs ou inférieurs à une valeur particulière, configurez les critères SumiF avec l'un des opérateurs logiques suivants:

• Supérieur à (>)
• Supérieur ou égal à (> =)
• Moins de (
• Moins que ou égal à (

Dans le tableau ci-dessous, supposant que vous souhaitiez additionner les numéros de vente des articles qui expédient en 3 jours ou plus. Pour exprimer cette condition, mettez un opérateur de comparaison (>) avant le nombre et entourez la construction en doubles guillemets:

=SUMIF(C2:C10, ">3", B2:B10)

Si le numéro cible est dans une autre cellule , disons F1, concaténez l'opérateur logique et la référence de cellule:

=SUMIF(C2:C10, ">"&F1, B2:B10)

De la même manière, vous pouvez résumer des valeurs inférieures à un nombre donné. Pour cela, utilisez l'opérateur inférieur à (

=SUMIF(C2:C10, "<h3 id="Somme-si-égal-à"> Somme si égal à</h3> <p> Une formule Sumif avec les critères "égaux à" fonctionne pour les nombres et le texte. Dans de tels critères, le signe égal n'est pas réellement requis.</p> <p> Par exemple, pour trouver un total des articles qui expédient en 3 jours, l'une des formules ci-dessous fera:</p> <p> <code>=SUMIF(C2:C10, 3, B2:B10)

ou

=SUMIF(C2:C10, "=3", B2:B10)

Pour résumer si égal à la cellule , fournissez une référence cellulaire pour les critères :

=SUMIF(C2:C10, F1, B2:B10)

Où B2: B10 sont les montants, C2: C10 est la durée de l'expédition et F1 est le délai de livraison souhaité.

De même, vous pouvez utiliser les critères "égaux à" avec les valeurs de texte . Par exemple, pour additionner les montants des pommes , choisissez l'une des formules ci-dessous:

=SUMIF(A2:A10, "apples", B2:B10)

=SUMIF(A2:A10, "=apples", B2:B10)

=SUMIF(A2:A10, F1, B2:B10)

Où A2: A10 est la liste des éléments à comparer avec la valeur en F1.

Les formules ci-dessus impliquent que le critère correspond à l'ensemble du contenu cellulaire. Par conséquent, la fonction SUMIF additionnera les ventes de pommes , mais pas, disons, les pommes vertes . Pour résumer les correspondances partielles, construisez les critères " IF Cell Contient " comme dans cette formule générique Sumif.

Note. Veuillez prêter attention que, dans les formules Excel Sumif, un opérateur de comparaison ou égal doit toujours être enfermé en doubles guillemets, qu'ils soient utilisés seuls ou avec un nombre ou un texte.

Somme sinon égal à

Pour construire les critères "non égaux à", utilisez l'opérateur logique "".

Lorsqu'une valeur, texte ou numéro, est codé en dur dans les critères, n'oubliez pas d'entourer toute la construction avec des devis doubles.

Par exemple, pour résumer les montants avec expédition autres que 3 jours, la formule se déroule comme suit:

=SUMIF(C2:C10, "3", B2:B10)

Pour trouver un total de tous les éléments à l'exception des pommes , la formule est:

=SUMIF(A2:A10, "apples", B2:B10)

Lorsque le critère est dans une autre cellule, concaténez l'opérateur "non égal à" et une référence cellulaire comme ceci:

=SUMIF(A2:A10, ""&F1, B2:B10)

Sum si vide

Cet exemple montre comment résumer les cellules dans une colonne si une cellule correspondante dans une autre colonne est vide. Il existe deux formules pour accomplir la tache. Le celui à utiliser dépend de votre interprétation d'une "cellule vide".

Si "blanc" signifie des cellules qui ne contiennent absolument rien (pas de formule, aucune cha?ne de longueur zéro renvoyée par une autre fonction), utilisez "=" pour les critères. Par exemple:

=SUMIF(B2:B10, "=", C2:D10)

Si "vide" comprend des cha?nes vides (par exemple, des cellules avec une formule comme = "" ), utilisez "" pour les critères:

=SUMIF(B2:B10, "", C2:D10)

Les deux formules renvoient un total de ventes pour les régions non définies, c'est-à-dire où une cellule de la colonne B est vide:

SUM

Pour faire de la formule "Si la cellule n'est pas vide, SUM", utilisez "" comme critère. Cela additionnera toutes les cellules qui contiennent quoi que ce soit, y compris les cha?nes de longueur zéro.

Par exemple, voici comment vous pouvez additionner les ventes pour toutes les régions, c'est-à-dire où la colonne B n'est pas vide:

=SUMIF(B2:B10, "", C2:D10)

Excel Sumif avec des critères de texte

Lors de l'ajout de nombres dans une colonne basée sur des valeurs de texte dans une autre colonne, il est important de différencier la correspondance exacte et partielle .

Pour les exemples de formule réels, veuillez consulter la somme si égale et sum si elle n'est pas égale à.

Dans la section suivante, nous allons examiner de plus près les formules Sumif avec un match partiel.

Formules Sumif avec des caractères génériques

Pour résumer conditionnellement les cellules par match partiel , incluez l'un des caractères génériques suivants dans vos critères:

• Marque de questions (?) Pour correspondre à un seul caractère dans une position spécifique.
• Astérisque (*) pour correspondre à n'importe quel nombre de caractères.

Exemple 1. Sum Valeurs basées sur une correspondance partielle

Supposons que vous souhaitiez le total des ventes pour toutes les régions du nord, y compris le nord , le nord-est et le nord-ouest . Pour le faire, mettez un astérisque juste après le mot "Nord":

=SUMIF(B2:B10, "north*", C2:D10)

Un astérisque des deux c?tés fonctionnera également:

=SUMIF(B2:B10, "*north*", C2:D10)

Alternativement, vous pouvez taper la région d'intérêt dans une cellule prédéfinie (F1), puis concaténer une référence de cellule et un caractère générique enfermé en citations:

=SUMIF(B2:B10, F1&"*", C2:D10)

Ou

=SUMIF(B2:B10, "*"&F1&"*", C2:D10)

Exemple 2. Sum si la cellule contient * ou?

Pour faire correspondre un point d'interrogation littéral ou un astérisque , placez un tilde (~) devant le caractère, par exemple "~?" ou "~ *".

Par exemple, pour résumer les ventes pour les régions marquées de *, utilisez "* ~ *" pour les critères . Dans ce cas, le premier astérisque est un joker et le second est un personnage d'astérisque littéral:

=SUMIF(B2:B10, "*~*", C2:D10)

Si les critères (* dans notre cas) sont entrés dans une cellule séparée, alors concaténez un tilde et la référence de la cellule, comme ceci:

=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)

Exemple 3. Sum si une autre cellule contient du texte

Si votre ensemble de données contient divers types de données et que vous souhaitez uniquement résumer les cellules correspondant aux valeurs de texte, les formules Sumif suivantes seront utiles.

Pour additionner les valeurs dans les cellules C2: C8 Si une cellule de la colonne A contient des caractères de texte:

=SUMIF(A2:A8,"?*", C2:C8)

Pour résumer les valeurs dans C2: C8 Si une cellule de la colonne A contient une valeur de texte, y compris les cha?nes de longueur nulle:

=SUMIF(A2:A8,"*", C2:C8)

Les deux formules ci-dessus ignorent les valeurs non texte telles que les nombres, les dates, les erreurs et les booléens.

Comment utiliser Excel Sumif avec des dates

L'utilisation des dates comme critères Sumif est beaucoup comme utiliser des nombres. La chose la plus importante est de fournir une date dans le format que Excel comprend. Si vous ne savez pas quel format de date est pris en charge et lequel ne l'est pas, la fonction de date peut être une solution.

En supposant que vous cherchez à résumer les ventes pour les articles livrés avant le 10 septembre-2010, les critères peuvent être exprimés de cette manière:

=SUMIF(C2:C10, "

ou

=SUMIF(C2:C10, "

ou

=SUMIF(C2:C10, "

Où F1 est la date cible.

Pour résumer les cellules en fonction de la date d'aujourd'hui , incluez la fonction aujourd'hui dans vos critères. Par exemple, c'est ainsi que vous calculez un total de ventes avec une date de livraison avant aujourd'hui:

=SUMIF(C2:C10, "

Pour résumer dans une plage de dates , vous devez définir séparément une date plus petite et plus grande. Cela peut être fait à l'aide de la fonction SUMIFS qui prend en charge plusieurs critères.

Par exemple, pour résumer les valeurs dans la colonne B Si une date dans la colonne C se situe entre deux dates , c'est la formule à utiliser:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "

Lorsque B2: B10 est la plage de somme, C2: C10 est la liste des dates à vérifier, F1 est la date de début et G1 est la date de fin.

D'autres exemples de formules peuvent être trouvés dans les suMIF avec une plage de dates comme critères.

Comment faire Sumif à partir d'une autre feuille

Pour résumer conditionnellement les données d'une feuille différente, fournissez des références externes pour les arguments SUMIF. Le moyen le plus simple consiste à commencer à taper une formule, à passer à une autre feuille de calcul et à sélectionner des plages à l'aide de la souris. Excel insérera automatiquement toutes les références, sans que vous ayez à vous soucier de la syntaxe correcte.

Par exemple, la formule ci-dessous additionnera les valeurs en C2: C10 sur la fiche technique basée sur les critères en B3 sur la feuille1 :

=SUMIF(Data!B2:B10, B3, Data!C2:C10)

Comment utiliser correctement les références cellulaires dans les critères Sumif

Pour créer une formule flexible, vous insérez normalement tous les paramètres variables dans des cellules prédéfinies au lieu de les "codage en dur". Avec Excel Sumif, cela pourrait être un peu un défi.

Dans le cas le plus simple lors de la sommation "si égale à", vous utilisez simplement une référence cellulaire pour les critères. Par exemple:

=SUMIF(C2:C10, F1, B2:B10)

Mais lorsqu'une référence cellulaire est utilisée avec un opérateur logique , les critères doivent être fournis sous la forme d'une cha?ne. Ainsi, vous utilisez les Double Quotes ("") pour démarrer une cha?ne de texte et AmperSand (&) pour concaténer et terminer la cha?ne. Par exemple:

=SUMIF(C2:C10, ">"&F7, B2:B10)

Veuillez noter que les opérateurs de comparaison sont enfermés dans des guillemets alors que les références cellulaires ne le sont pas.

Pourquoi ma formule Sumif ne fonctionne-t-elle pas?

Il pourrait y avoir plusieurs raisons pour lesquelles Excel Sumif ne fonctionne pas pour vous. Parfois, votre formule ne renvoie pas ce que vous attendez uniquement parce que le type de données dans une cellule ou dans un argument n'est pas adapté à la fonction SUMIF. Vous trouverez ci-dessous une liste de choses importantes à vérifier.

1. Sumif ne prend en charge qu'une seule condition

La syntaxe de la fonction SUMIF n'a de place que pour une seule condition. Pour résumer avec plusieurs critères, utilisez soit la fonction SUMIFS (ajoute des cellules qui remplissent toutes les conditions) ou construisent une formule Sumif avec plusieurs ou critères (résume les cellules qui remplissent l'une des conditions).

2. La plage et SUM_RANGE devraient être de la même taille

Pour qu'une formule Sumif fonctionne correctement, l'argument Range et SUM_RANGE devrait avoir les mêmes dimensions, sinon vous pouvez obtenir des résultats trompeurs. Le fait est que Microsoft Excel ne s'appuie pas sur la capacité de l'utilisateur à fournir des gammes de correspondance et pour éviter d'éventuels problèmes d'incohérence, il détermine automatiquement la plage de somme de cette manière:

SUM_RANGE définit uniquement la cellule supérieure gauche de la plage qui sera additionnée, la zone restante est déterminée par la taille et la forme de l'argument de la plage .

Compte tenu de ce qui précède, la formule ci-dessous résumera en fait les cellules en C2: C10 et non dans C2: D10. Pourquoi? Parce que la plage se compose de 1 colonne et 9 lignes, tout comme SUM_RANGE .

=SUMIF(B2:B10, "north", C2:D10)

Dans les versions Excel plus anciennes, les gammes de taille inégale peuvent causer de nombreux problèmes. Dans Excel moderne, des formules Sumif complexes où SUM_RANGE ont moins de lignes et / ou de colonnes que la plage sont également capricieuses. C'est pourquoi c'est une bonne pratique de toujours définir le même nombre de lignes et de colonnes pour ces deux arguments.

3. La plage et SUM_RANGE doivent être des gammes, pas des tableaux

Bien que SUMIF puisse traiter une constante de tableau dans les critères comme indiqué dans cet exemple, il ne prend pas en charge les tableaux dans Range et SUM_RANGE . Ces deux arguments ne peuvent être que des gammes cellulaires.

5. Syntaxe des critères Sumif

Pour les critères, la fonction SUMIF permet d'utiliser différents types de données, notamment du texte, des nombres, des dates, des références de cellules, des opérateurs logiques (>, ), des caractères génériques (?, *, ~) Et d'autres fonctions. La syntaxe de ces critères est assez spécifique.

Si l'argument des critères comprend une valeur de texte, un caractère générique ou un opérateur logique suivi du texte, du nombre ou de la date, renforce les critères entiers en guillemets. Par exemple:

=SUMIF(B2:B10, "north*", C2:D10)

=SUMIF(C2:D10, ">100")

=SUMIF(B2:B10, "north", C2:D10)

=SUMIF(C2:C10, "

Lorsqu'un opérateur logique est suivi d'une référence cellulaire ou d'une autre fonction, les critères doivent être fournis sous la forme d'une cha?ne. Ainsi, vous utilisez un ampère et) pour concaténer un opérateur logique et une référence ou une fonction. Par exemple:

=SUMIF(B2:B10, ">"&F1)

=SUMIF(C2:D10, "<h3 id="Sumif-d-un-autre-classeur-ne-fonctionne-pas"> 4. Sumif d'un autre classeur ne fonctionne pas</h3> <p> Comme pour de nombreuses fonctions Excel, SUMIF peut se référer à d'autres feuilles et classeurs, à condition qu'ils soient actuellement ouverts.</p> <p> Par exemple, cette formule fonctionnera bien tant que Book1 est ouvert:</p> <p> <code>=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9,"apples",[Book1.xlsx]Sheet1!$F$2:$F$9)

Et cela cessera de fonctionner dès que Book1 sera fermé. Cela se produit parce que les gammes référencées dans les classeurs fermés sont déférencés dans les tableaux. Et puisque les tableaux ne sont pas pris en charge dans la gamme et les arguments SUM_RANGE , SUMIF lance un #Value! erreur.

6. Sumif ne reconna?t pas le cas de texte

Par conception, Sumif in Excel n'est pas sensible au cas, ce qui signifie qu'il traite les lettres majuscules et minuscules comme les mêmes caractères. Pour créer une formule SUMIF sensible à la casse, utilisez la fonction de sumproduct avec exact.

Voilà comment utiliser Sumif dans Excel. J'espère que nos exemples de formule vous ont donné de bonnes informations. Comme toujours, je vous remercie de lire et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Exemples Excel Sumif (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!