Sumif dans Google Sheets avec des exemples de formule

Le tutoriel montre comment utiliser la fonction SUMIF dans les feuilles de calcul Google pour résumer conditionnellement les cellules. Vous trouverez des exemples de formule pour le texte, les nombres et les dates et apprendre à résumer avec plusieurs critères.

Certaines des meilleures fonctions dans Google Sheets sont celles qui vous aident à résumer et à classer les données. Aujourd'hui, nous allons examiner de plus près l'une de ces fonctions - Sumif - un instrument puissant pour la somme de cellules conditionnellement. Avant d'étudier les exemples de syntaxe et de formule, permettez-moi de commencer par quelques remarques importantes.

Google Sheets a deux fonctions pour additionner des nombres en fonction des conditions: Sumif et Sumifs . Le premier évalue une seule condition tandis que le second peut tester plusieurs conditions à la fois. Dans ce tutoriel, nous nous concentrerons uniquement sur la fonction SumiF, l'utilisation de Sumifs sera couverte dans le prochain article.

Si vous savez comment utiliser Sumif dans Excel Desktop ou Excel en ligne, Sumif dans Google Sheets sera un morceau de gâteau pour vous car les deux sont essentiellement les mêmes. Mais ne vous précipitez pas encore pour fermer cette page - vous pouvez trouver quelques formules Sumif inactives mais très utiles que vous ne connaissiez pas!

• Sumif dans Google Sheets - Syntaxe et utilisations de base
• Sumif avec des critères de texte (correspondance exacte)
• Sumif avec des caractères génériques (match partiel)
• Formule de suMIF sensible au cas
• Somme si plus, inférieur à, égal ou non égal à
• Formules Sumif pour dates
• Somme si vide ou pas vide
• Sumif avec plusieurs critères (ou logique)
• Google Sheets Sumif - choses à retenir

Voir les couleurs, additionnez les nombres!

Résumer les cellules par leur couleur de remplissage - sans effort et avec précision

Essayer maintenant

Doublons? Fusionner, résume, fait!

Douplicats de spot, combiner les détails et les totaux de décompte

Essayer maintenant

Déverrouiller le plein potentiel de vos feuilles Google

Découvrez votre acolyte de vos données - Considéré par des millions de personnes!

Découvrez la collection

Sumif dans Google Sheets - Syntaxe et utilisations de base

La fonction SUMIF est que Google Sheets est conçu pour résumer les données numériques en fonction d'une seule condition. Sa syntaxe est la suivante:

Sumif (gamme, critère, [sum_range])

Où:

• Plage (requis) - La plage de cellules qui doit être évaluée par critère .
• Critère (requis) - La condition à remplir.
• SUM_RANGE (Facultatif) - La plage dans laquelle résumer les nombres. S'il est omis, la plage est additionnée.

À titre d'exemple, faisons une formule simple qui additionnera les nombres dans la colonne B si la colonne A contient un élément égal à "l'échantillon d'élément".

Pour cela, nous définissons les arguments suivants:

• Range - Une liste d'éléments - A2: A12.
• Critère - Une cellule contenant l'élément d'intérêt - E1.
• SUM_RANGE - Montrations à résumer - B2: B12.

En rassemblant tous les arguments, nous obtenons la formule suivante:

=SUMIF(A2:A12,E1,B2:B12)

Et cela fonctionne exactement comme il se doit:

Exemples Google Sheets Sumif

D'après l'exemple ci-dessus, vous pouvez avoir l'impression que l'utilisation de formules Sumif dans les feuilles de calcul Google est si facile que vous pouvez le faire avec vos yeux fermés. Dans la plupart des cas, c'est vraiment ainsi :) Mais il y a encore des astuces et des utilisations non triviales qui pourraient rendre vos formules plus efficaces. Les exemples ci-dessous montrent quelques cas d'utilisation typiques. Pour rendre les exemples plus faciles à suivre, je vous invite à ouvrir notre échantillon de feuille Google Sumif.

Formules Sumif avec critères de texte (correspondance exacte)

Pour additionner des nombres qui ont un texte spécifique dans une autre colonne dans la même ligne, vous fournissez simplement le texte d'intérêt dans l'argument du critère de votre formule SUMIF. Comme d'habitude, tout texte dans tout argument de toute formule doit être enfermé en "Double Quotes".

Par exemple, pour obtenir un total de bananes , vous utilisez cette formule:

=SUMIF(A5:A15,"bananas",B5:B15)

Ou, vous pouvez mettre le critère dans une cellule et vous référer à cette cellule:

=SUMIF(A5:A15,B1,B5:B15)

Cette formule est claire, non? Maintenant, comment obtenez-vous un total de tous les articles sauf les bananes? Pour cela, utilisez l'opérateur non égal :

=SUMIF(A5:A15,"bananas",B5:B15)

Si un "élément d'exclusion" est entré dans une cellule, vous enfermez l'opérateur non égal à Double Quotes ("") et concaténez l'opérateur et la référence de cellule en utilisant un Ampersand (&). Par exemple:

=SUMIF (A5:A15,""&B1, B5:B15)

La capture d'écran suivante démontre à la fois "SUM si elle est égale à" et "SUM si ce n'est pas égal aux" formules en action:

Veuillez noter que SUMIF dans Google Sheets recherche exactement le texte spécifié. Dans cet exemple, seules les montants de bananes sont additionnés, les bananes vertes et les bananes Goldfinger ne sont pas incluses. Pour résumer avec une correspondance partielle, utilisez des caractères génériques comme indiqué dans l'exemple suivant.

Formules Sumif avec des caractères génériques (match partiel)

Dans les situations où vous souhaitez résumer les cellules dans une colonne si une cellule dans une autre colonne contient un texte ou un caractère spécifique dans le cadre du contenu cellulaire , incluez l'un des caractères génériques suivants dans vos critères:

• Marque de questions (?) Pour correspondre à un seul caractère.
• Astérisque (*) pour correspondre à n'importe quelle séquence de caractères.

Par exemple, pour résumer les quantités de toutes sortes de bananes, utilisez cette formule:

=SUMIF(A2:A13,"*bananas*",B2:B13)

Vous pouvez également utiliser les jokers avec des références cellulaires. Pour cela, joignez le caractère générique dans les guillemets et concaténez-le avec une référence cellulaire:

=SUMIF(A2:A13, "*"&E1&"*", B2:B13)

Quoi qu'il en soit, notre formule Sumif ajoute les montants de toutes les bananes:

Pour correspondre à un véritable point d'interrogation ou à l'astérisque, préfixez-le avec le personnage Tilde (~) comme "~?" ou "~ *".

Par exemple, pour résumer les nombres de la colonne B qui ont un astérisque dans la colonne A dans la même ligne, utilisez cette formule:

=SUMIF(A2:A13, "~*", B2:B13)

Vous pouvez même taper un astérisque dans une cellule, par exemple B1, et concaténer cette cellule avec le char tilde:

=SUMIF(A2:A13, "~"&E1, B2:B13)

Sumif sensible à la casse dans Google Sheets

Par défaut, Sumif dans Google Sheets ne voit pas la différence entre les petites et majuscules. Pour le forcer à téter les caractères majuscules et minuscules différemment, utilisez Sumif en combinaison avec les fonctions Find and ArrayFormula:

Sumif (arrayformula (find (" text ", plage)), 1, sum_range)

En supposant que vous avez une liste de numéros de commande dans A2: A12 et des montants correspondants dans C2: C12, où le même numéro de commande apparaît dans plusieurs lignes. Vous entrez l'ID de commande cible dans une cellule, par exemple F1, et utilisez la formule suivante pour retourner le total de la commande:

=SUMIF(ARRAYFORMULA(FIND(F1, A2:A12)),1, C2:C12)

Comment fonctionne cette formule

Pour mieux comprendre la logique de la formule, décomposons-la en parties significatives:

La partie la plus délicate est l'argument de la plage : ArrayFormula (Find (F1, A2: A12))

Vous utilisez la fonction Rechercher sensible à la casse pour rechercher l'ID de commande exacte. Le problème est qu'une formule de recherche régulière ne peut rechercher que dans une seule cellule. Pour rechercher dans une plage, une formule de tableau est nécessaire, donc vous ne trouvez pas dans ArrayFormula.

Lorsque la combinaison ci-dessus trouve une correspondance exacte, elle renvoie 1 (la position du premier caractère trouvé), sinon une erreur #Value. Ainsi, la seule chose qui vous reste est de résumer les montants correspondant à 1. Pour cela, vous mettez 1 dans l'argument du critère et C2: C12 dans l'argument SUM_RANGE . Fait!

Formules Sumif pour les nombres

Pour résumer les nombres qui répondent à une certaine condition, utilisez l'un des opérateurs de comparaison de votre formule Sumif. Dans la plupart des cas, le choix d'un opérateur approprié n'est pas un problème. L'intégrer correctement dans le critère pourrait être un défi.

Somme si plus ou moins que

Pour comparer les numéros de source à un nombre particulier, utilisez l'un des opérateurs logiques suivants:

• supérieur à (>)
• moins de (
• supérieur ou égal à (> =)
• moins que ou égal à (

Par exemple, pour additionner des nombres en B2: B12 qui sont supérieurs à 200, utilisez cette formule:

=SUMIF(B2:B12, ">200", B2:B12)

Veuillez remarquer la syntaxe correcte du critère: un nombre préfixé avec un opérateur de comparaison et toute la construction enfermée en guillemets.

Ou, vous pouvez taper le nombre dans une cellule et concaténer l'opérateur de comparaison avec une référence cellulaire:

=SUMIF(B2:B12, ">"&E1, B2:B12)

Vous pouvez même saisir à la fois l'opérateur de comparaison et le nombre dans des cellules séparées, et concaténer ces cellules: =SUMIF(B2:B12, E1&F1)

De la même manière, vous pouvez utiliser d'autres opérateurs logiques tels que:

SUMPS s'il est supérieur ou égal à 200:

=SUMIF(B2:B12, ">=200")

Somme si moins de 200:

=SUMIF(B2:B12, "

Somme si moins ou égal à 200:

=SUMIF(B2:B12, "

Somme si égal à

Pour résumer les nombres égaux un nombre spécifique, vous pouvez utiliser le signe d'égalité (=) avec le nombre ou omettre le signe d'égalité et inclure uniquement le nombre dans l'argument du critère .

Par exemple, pour additionner des quantités dans la colonne B dont la quantité dans la colonne C est égale à 10, utilisez l'une des formules ci-dessous:

=SUMIF(C2:C12, 10, B2:B12)

ou

=SUMIF(C2:C12, "=10", B2:B12)

ou

=SUMIF(C2:C12, F1, B2:B12)

Où F1 est la cellule avec la quantité requise.

Somme sinon égal à

Pour résumer des nombres autres que le nombre spécifié, utilisez l'opérateur non égal à l'opérateur ().

Dans notre exemple, pour additionner les quantités de la colonne B qui ont une quantité sauf 10 dans la colonne C, allez avec l'une de ces formules:

=SUMIF(C2:C12, "10", B2:B12)

=SUMIF(C2:C12, ""&F1, B2:B12)

La capture d'écran ci-dessous montre le résultat:

Formules Google Sheets Sumif pour les dates

Pour résumer conditionnellement les valeurs en fonction des critères de date, vous utilisez également les opérateurs de comparaison comme indiqué dans les exemples ci-dessus. Le point clé est qu'une date doit être fournie dans le format que Google Sheets peut comprendre.

Par exemple, pour résumer les montants en B2: B12 pour les dates de livraison avant le 11 avl.2024, construisez le critère d'une de ces manières:

=SUMIF(C2:C12, "

=SUMIF(C2:C12, "

=SUMIF(C2:C12, "

Où F1 est la date cible:

Dans le cas où vous souhaitez résumer conditionnellement les cellules en fonction de la date d'aujourd'hui , incluez la fonction aujourd'hui () dans l'argument du critère .

Par exemple, faisons une formule qui additionne les montants des livraisons d'aujourd'hui:

=SUMIF(C2:C12, TODAY(), B2:B12)

Prenant l'exemple, nous pouvons trouver un total de livraisons passées et futures:

Avant aujourd'hui: =SUMIF(C2:C12, "

Après aujourd'hui: =SUMIF(C2:C12, ">"&TODAY(), B2:B12)

Somme basée sur des cellules vierges ou non chargées

Dans de nombreuses situations, vous devrez peut-être résumer des valeurs dans une certaine colonne si une cellule correspondante dans une autre colonne est ou n'est pas vide.

Pour cela, utilisez l'un des critères suivants dans vos formules Google Sheets Sumif:

Sum si vide:

• "=" pour résumer des cellules complètement vides.
• "" Pour résumer les cellules vierges, y compris celles qui contiennent des chaînes de longueur nulle.

SUMPS SI NOW BLANQUE:

• "" Pour ajouter des cellules qui contiennent une valeur, y compris les chaînes de longueur nulle.

Par exemple, pour résumer les montants pour lesquels la date de livraison est définie (une cellule de la colonne C n'est pas vide ), utilisez cette formule:

=SUMIF(C5:C15, "", B5:B15)

Pour obtenir un total des montants sans date de livraison (une cellule de la colonne C est vide ), utilisez celle-ci:

=SUMIF(C5:C15, "", B5:B15)

Google Sheets Sumif avec plusieurs critères (ou logique)

La fonction SUMIF dans Google Sheets est conçue pour additionner des valeurs en fonction d'un seul critère. Pour résumer avec plusieurs critères, vous pouvez ajouter deux fonctions SUMIF ou plus ensemble.

Par exemple, pour résumer les montants des pommes et des oranges , utilisez cette formule:

=SUMIF(A2:A12, "apples", B2:B12) SUMIF(A2:A12, "oranges", B2:B12)

Ou, mettez les noms d'articles dans deux cellules distinctes, disons E1 et E2, et utilisez chacune de ces cellules comme critère:

=SUMIF(A2:A12, E1, B2:B12) SUMIF(A2:A12, E2, B2:B12)

Veuillez noter que cette formule fonctionne comme SUMIF avec ou logique - elle résume des valeurs si au moins l'un des critères spécifiés est rempli.

Dans cet exemple, nous ajoutons des valeurs dans la colonne B si la colonne A est égale à "pommes" ou "oranges". En d'autres termes, Sumif () sumif () fonctionne comme la pseudo-formelle suivante (pas réelle, elle ne montre que la logique!): Sumif (a: a, "pommes" ou "oranges", b: b).

Si vous cherchez à résumer conditionnellement et logique , c'est-à-dire que les valeurs additionnent lorsque tous les critères spécifiés sont remplis, utilisez la fonction Google Sheets Sumifs.

Google Sheets Sumif - choses à retenir

Maintenant que vous connaissez les écrous et les boulons de la fonction SUMIF dans Google Sheets, il peut être une bonne idée de faire un bref résumé de ce que vous avez déjà appris.

1. Sumif ne peut évaluer qu'une seule condition

La syntaxe de la fonction SUMIF ne permet qu'une seule plage , un critère et un sum_range . Pour résumer avec plusieurs critères , ajoutez plusieurs fonctions SUMIF (ou logique) ou utilisez des formules SUMIFS (et logique).

2. La fonction SUMIF est insensible au cas

Si vous recherchez une formule SUMIF sensible à la casse qui peut faire la différence entre les caractères majuscules et les caractères minuscules, utilisez SUMIF en combinaison avec ArrayFormula et trouvez comme indiqué dans cet exemple.

3. Afficher la plage de taille égale et SUM_RANGE

En fait, l'argument SUM_RANGE Spécifie 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 .

Pour le dire différemment, Sumif (a1: a10, "pommes", b1: b10) et sumif (a1: a10, "pommes", b1: b100) résumera les deux valeurs dans la plage b1: b10 car elle est de la même taille que la plage (a1: a10).

Ainsi, même si vous fournissez à tort une mauvaise plage de somme, Google Sheets calculera toujours votre formule à droite, à condition que la cellule supérieure gauche de SUM_RANGE soit correcte.

Cela dit, il est toujours recommandé de fournir une plage de taille égale et SUM_RANGE pour éviter les erreurs et prévenir les problèmes d'incohérence.

4. MAINTENANT LA SYNTAXE DE CRITÈRES SUMIF

Pour que votre formule Google Sheets Sumif fonctionne correctement, exprimez les critères de la bonne façon:

• Si le critère comprend du texte , du caractère générique ou de l'opérateur logique suivi d'un nombre, d'un texte ou d'une date, entourer le critère en guillemets. Par exemple:

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

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

  =SUMIF(A2:A10, ">5")

  =SUMIF(A5:A10, "apples", B5:B10)

• Si le critère comprend un opérateur logique et une référence de cellule ou une autre fonction , utilisez les guillemets pour démarrer une chaîne de texte et Ampersand (&) pour concaténer et terminer la chaîne. Par exemple:

  =SUMIF(A2:A10, ">"&B2)

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

5. Verrouille des gammes avec des références de cellules absolues si nécessaire

Si vous prévoyez de copier ou de déplacer votre formule SUMIF à un moment ultérieur, corrigez les gammes en utilisant des références de cellules absolues (avec le signe $) comme dans Sumif ($ a 2: $ a 10 $, "pommes", $ b $: $ b 10 $).

C'est ainsi que vous utilisez la fonction SUMIF dans Google Sheets. Pour examiner de plus près les formules discutées dans ce tutoriel, vous êtes invités à ouvrir notre exemple de feuille Google Sumif. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Feuille de calcul avec des exemples de formule

Échantillon de feuille de calcul Sumif

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!