Ce didacticiel explique la différence entre les fonctions SUMIF et SUMIFS en termes de syntaxe et utilisation, et fournit un certain nombre dexemples de formules pour additionner des valeurs avec plusieurs critères ET / OU dans Excel 2019, 2016, 2013, 2010, 2007, 2003 et versions antérieures.
Comme tout le monde le sait, Microsoft Excel fournit un tableau de fonctions pour effectuer divers calculs avec des données. Il y a quelques articles, nous avons exploré COUNTIF et COUNTIFS, qui sont conçus pour compter les cellules en fonction dune seule condition et de plusieurs conditions, respectivement. La semaine dernière, nous avons couvert Excel SUMIF qui ajoute des valeurs répondant aux critères spécifiés. Il est maintenant temps de passer en revue la version plurielle de SUMIF – Excel SUMIFS qui permet de sommer les valeurs selon plusieurs critères.
Ceux qui connaissent la fonction SUMIF pourraient penser que sa conversion en SUMIFS prend juste un supplément « S » et quelques critères supplémentaires. Cela semble tout à fait logique… mais « logique » ce nest pas toujours le cas avec Microsoft 🙂
- Fonction SUMIF – syntaxe & utilisation
- Fonction SUMIFS – syntaxe & utilisation
- Comment utiliser SUMIFS dans Excel (ET logique)
- Excel SUMIF avec plusieurs critères (logique OR)
- Excel SUMIFS avec plusieurs conditions OR
- Utilisation dExcel SUM dans des formules matricielles
Fonction Excel SUMIF – syntaxe & utilisation
La fonction SUMIF est utilisée pour additionner conditionnellement des valeurs basées sur un seul critère. Nous avons discuté de la syntaxe SUMIF en détail dans larticle précédent, alors laissez-moi vous donner un bref résumé maintenant.
-
range– la plage de cellules à évaluer selon vos critères, obligatoire. -
criteria– la condition qui doit être remplie, obligatoire. -
sum_range– les cellules à additionner si la condition est remplie, facultative.
Comme vous le voyez, le La syntaxe de la fonction Excel SUMIF autorise une seule condition. Et encore, nous disons quExcel SUMIF peut être utilisé pour additionner des valeurs avec plusieurs critères. Comment est-ce possible? En ajoutant les résultats de plusieurs fonctions SUMIF et en utilisant des formules SUMIF avec des critères de tableau, comme illustré dans les exemples qui suivent.
Fonction Excel SUMIFS – syntaxe & utilisation
Vous utilisez SUMIFS dans Excel pour trouver une somme conditionnelle de valeurs basée sur plusieurs critères. La fonction SUMIFS a été introduite dans Excel 2007, vous pouvez donc lutiliser dans toutes les versions modernes dExcel 2019, 2016, 2013, 2010 et 2007.
Par rapport à SUMIF, la syntaxe SUMIFS est un peu plus complexe :
Les 3 premiers arguments sont obligatoires, les plages supplémentaires et leurs critères associés sont facultatifs.
-
sum_range– une ou plusieurs cellules à additionner, obligatoire. Cela peut être une seule cellule, une plage de cellules ou une plage nommée. Seules les cellules avec des nombres sont additionnées; les valeurs vides et textuelles sont ignorées. -
criteria_range1– la première plage à évaluer par les critères associés, obligatoire. -
criteria1– la première condition à remplir, obligatoire. Vous pouvez fournir les critères sous la forme dun nombre, dune expression logique, dune référence de cellule, dun texte ou dune autre fonction Excel. Par exemple, vous pouvez utiliser des critères tels que 10, « > = 10 », A1, « cerises » ou TODAY (). -
criteria_range2, criteria2, …– ce sont des plages supplémentaires et des critères qui leur sont associés, facultatifs. Vous pouvez utiliser jusquà 127 paires plage / critères dans les formules SUMIFS.
Et maintenant, laissez » s jetez un œil à la formule Excel SUMIFS avec deux conditions. Supposons que vous ayez un tableau répertoriant les envois de fruits de différents fournisseurs. Vous avez les noms des fruits dans la colonne A, les noms des fournisseurs dans la colonne B et la quantité dans la colonne C. Ce que vous voulez, cest connaître la somme des montants relatifs à un fruit et à un fournisseur donnés, par exemple toutes les pommes fournies par Pete.
Lorsque vous « réapprenez quelque chose de nouveau, cest toujours une bonne idée de commencer par des choses simples. Donc, pour commencer, définissons tous les arguments de notre formule SUMIFS:
- sum_range – C2: C9
- critère_range1 – A2: A9
- critère1 – « pommes »
- gamme_critères2 – B2: B9
- critère2 – « Pete »
Maintenant, assemblez les paramètres ci-dessus, et vous obtiendra la formule SUMIFS suivante:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Pour affiner la formule en outre, vous pouvez remplacer les critères de texte « pommes » et « Pete » par des références de cellule.Dans ce cas, vous naurez pas à modifier la formule pour calculer la quantité dautres fruits dun autre fournisseur:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Utilisation de SUMIFS et SUMIF dans Excel – choses à retenir
Puisque le but de ce tutoriel est de couvrir toutes les manières possibles de faire la somme des valeurs selon plusieurs conditions, nous discuterons des exemples de formule avec les deux fonctions – Excel SUMIFS et SUMIF avec plusieurs critères. Pour les utiliser correctement, vous devez comprendre clairement ce que ces deux fonctions ont en commun et en quoi elles sont différentes.
Bien que la partie commune soit claire – destination et paramètres similaires – les différences ne sont pas si évidents, bien que très essentiels.
1. Lordre des arguments
Dans les fonctions Excel SUMIF et SUMIFS, lordre des arguments est différent. En particulier, sum_range est le premier paramètre dans SUMIFS, mais il est 3e dans SUMIF.
À première vue, il peut sembler que Microsoft complique délibérément la courbe dapprentissage de ses utilisateurs. Howev euh, en y regardant de plus près, vous verrez le raisonnement derrière cela. Le fait est que sum_range est facultatif dans SUMIF. Si vous lomettez, pas de problème, votre formule SUMIF additionnera les valeurs de la plage (premier paramètre).
Dans SUMIFS, la somme_range est très importante et obligatoire, et cest pourquoi elle vient en premier. Les gars de Microsoft ont probablement pensé quaprès avoir ajouté la 10e ou la 100e paire plage / critères, quelquun pourrait oublier de spécifier la plage à additionner:)
2. La plage de somme et la plage de critères doivent être de taille égale
Dans la fonction SUMIF, largument sum_range ne doit pas nécessairement être de la même taille que largument de plage, tant que vous avez la cellule en haut à gauche à droite. Dans Excel SUMIFS, chaque critère_range doit contenir le même nombre de lignes et de colonnes que le paramètre sum_range.
Par exemple, =SUMIF(A2:A9,F1,C2:C18) renverra le résultat correct car Excel considère uniquement la cellule en haut à gauche de largument sum_range (C2 dans cet exemple, ce qui est correct), puis inclut autant de colonnes et de lignes que ce que contient largument range.
Très bien, assez de stratégie (cest-à-dire théorie) , passons à la tactique (cest-à-dire des exemples de formules:)
Comment utiliser SUMIFS dans Excel – exemples de formules
Il y a un instant, nous avons discuté dune simple formule SUMIFS avec deux textes critères. De la même manière, vous pouvez utiliser Excel SUMIFS avec plusieurs critères exprimés par des nombres, des dates, des expressions logiques et dautres fonctions Excel.
Exemple 1. Excel SUMIFS avec des opérateurs de comparaison
Dans notre tableau des fournisseurs de fruits, supposons que vous souhaitiez additionner toutes les livraisons effectuées par Mike avec la quantité 200 ou plus. Pour ce faire, vous utilisez lopérateur de comparaison « supérieur à o r égal à « (> =) dans critère2 et obtenez la formule SUMIFS suivante:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Nous avons couvert tous les opérateurs de comparaison possibles en détail lors de la discussion de la fonction Excel SUMIF, les mêmes opérateurs peut être utilisé dans les critères SUMIFS. Par exemple, la formule suivante renvoie la somme de toutes les valeurs des cellules C2: C9 supérieures ou égales à 200 et inférieures ou égales à 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Exemple 2. Utilisation dExcel SUMIFS avec des dates
Si vous souhaitez additionner des valeurs avec plusieurs critères basés sur la date actuelle, utilisez la fonction TODAY () dans vos SUMIFS critères, comme démontré ci-dessous. La formule suivante additionne les valeurs de la colonne D si une date correspondante dans la colonne C tombe dans les 7 derniers jours, y compris aujourdhui:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
De la même manière, vous pouvez utiliser la fonction Excel SUMIF pour additionner les valeurs dans une plage de dates donnée. Par exemple, la formule SUMIFS suivante ajoute les valeurs des cellules C2: C9 si une date de la colonne B se situe entre le 1er octobre 2014 et le 31 octobre 2014 inclus.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Le même résultat peut être obtenu en calculant la différence de deux fonctions SUMIF, comme illustré dans cet exemple – Comment utiliser SUMIF pour additionner des valeurs dans une plage de dates donnée. Cependant, Excel SUMIFS est beaucoup plus facile et plus compréhensible, nest-ce pas?
Exemple 3. Excel SUMIFS avec des cellules vides et non vides
Lors de lanalyse de rapports et dautres données, vous peut souvent avoir besoin de faire la somme des valeurs correspondant à des cellules vides ou non vides.
Et maintenant, voyons comment vous pouvez utiliser une formule SUMIFS avec des critères « vide » et « non vide » données.
Supposons que vous ayez une date de commande dans la colonne B, une date de livraison dans la colonne C et Qté. dans la colonne D. Comment trouvez-vous le total des produits qui nont pas encore été livrés? Autrement dit, vous souhaitez connaître la somme des valeurs correspondant aux cellules non vides de la colonne B et aux cellules vides de la colonne C.
La solution est dutiliser la formule SUMIFS avec 2 critères:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Utilisation dExcel SUMIF avec plusieurs critères OR
Comme indiqué au début de ce tutoriel, la fonction SUMIFS est conçue avec la logique AND. Mais que se passe-t-il si vous devez additionner des valeurs avec plusieurs critères OR, cest-à-dire lorsquau moins une des conditions est remplie?
Exemple 1. SUMIF + SUMIF
La solution la plus simple consiste à additionner les résultats renvoyés par plusieurs fonctions SUMIF. Par exemple, la formule suivante montre comment trouver le total des produits livrés par Mike et John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Comme vous le voyez, la première fonction SUMIF ajoute les quantités correspondant à « Mike », lautre fonction SUMIF renvoie les montants relatifs à « John » et ensuite vous additionnez ces 2 nombres.
Exemple 2. SUM & SUMIF avec un argument de tableau
La solution ci-dessus est très simple et peut faire le travail rapidement quand il ny a que quelques critères. Mais une formule SUMIF + SUMIF peut se développer énormément si vous souhaitez additionner des valeurs avec plusieurs conditions OR. Dans ce cas, une meilleure approche consiste à utiliser un argument de critère de tableau dans la fonction SUMIF. Examinons cette approche maintenant.
Vous pouvez commencer par lister toutes vos conditions séparées par des virgules, puis mettre la liste résultante séparée par des virgules entre {accolades}, ce qui est techniquement appelé un tableau.
Largument tableau composé de 3 valeurs oblige votre formule SUMIF à renvoyer trois résultats distincts, mais puisque nous écrivons la formule dans une seule cellule, il ne renverra que le premier résultat – cest-à-dire le total des produits livrés par John . Pour que cette approche basée sur les critères de tableau fonctionne, vous devez utiliser une autre petite astuce – entourez votre formule SUMIF dans une fonction SUM, comme ceci:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Comme vous le voyez, un critère de tableau rend la formule beaucoup plus compacte par rapport à SUMIF + SUMIF, et vous permet dajouter autant de valeurs que que vous aimez dans le tableau.
Cette approche fonctionne aussi bien avec des nombres quavec des valeurs textuelles. Par exemple, si au lieu des noms de fournisseurs dans la colonne C, vous aviez des identifiants de fournisseur comme 1, 2, 3, etc. ., alors votre formule SUMIF ressemblerait à ceci:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Contrairement aux valeurs textuelles, les nombres ne doivent pas être entre guillemets arguments de tableau.
Exemple 3. SUMPRODUCT & SUMIF
Au cas où, votre méthode préférée est de lister les critères dans certaines cellules plutôt que spécifiez-les directement dans la formule, vous pouvez utiliser SUMIF en conjonction avec la fonction SUMPRODUCT qui multiplie les composants dans les tableaux donnés et renvoie la somme de ces produits.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Où G2: G4 sont les cellules contenant vos critères, les noms des fournisseurs dans notre cas, comme illustré dans la capture décran ci-dessous.
Mais bien sûr, rien ne vous empêche de listant les valeurs dans un tableau de critères de votre fonction SUMIF si vous souhaitez:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Le résultat renvoyé par les deux formules sera identique à ce que vous voyez dans la capture décran:
Excel SUMIFS avec plusieurs critères OR
Si vous souhaitez additionner conditionnellement des valeurs dans Excel non seulement avec plusieurs conditions OR, mais avec plusieurs ensembles de conditions, vous devrez utiliser SUMIFS au lieu de SUMIF. Les formules seront très similaires à celles dont nous venons de parler.
Comme dhabitude, un exemple peut aider à mieux illustrer ce point. Dans notre tableau des fournisseurs de fruits, ajoutons la date de livraison (colonne E) et trouvez la quantité totale livrée par Mike, John et Pete en octobre.
Exemple 1. SUMIFS + SUMIFS
La formule produite par cette approche comprend beaucoup de répétitions et de regards encombrant, mais il est facile à comprendre et, surtout, cela fonctionne:)
Exemple 2. SUM & SUMIFS avec un argument de tableau
Jai essayé dexpliquer lessence de cette approche dans lexemple SUMIF, donc maintenant nous pouvons simplement copier cette formule, changer lordre des arguments (comme vous vous en souvenez, il est différent dans SUMIF et SUMIFS) et ajouter des critères supplémentaires. la formule résultante est plus compacte que SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Le résultat renvoyé par cette formule est exactement le même que celui que vous voyez dans la capture décran ci-dessus.
Exemple e 3. SUMPRODUCT & SUMIFS
Comme vous vous en souvenez, lapproche SUMPRODUCT diffère des deux précédentes par la manière dont vous saisissez chacun de vos critères dans une cellule séparée plutôt que de les spécifier directement dans la formule. Dans le cas de plusieurs ensembles de critères, la fonction SOMMEPROD ne suffit pas et vous devrez également utiliser ISNUMBER et MATCH.
Donc, en supposant que les noms des fournitures sont dans les cellules H1: H3, la date de début dans la cellule H4 et la date de fin dans la cellule H5, notre formule SOMMEPROD prend la forme suivante:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Beaucoup de gens se demandent pourquoi utiliser le double tiret (-) dans les formules SUMPRODUCT. Le fait est quExcel SUMPRODUCT ignore tout sauf les valeurs numériques, tandis que les opérateurs de comparaison dans notre formule renvoient des valeurs booléennes (VRAI / FAUX), qui ne sont pas numériques. Pour convertir ces valeurs booléennes en 1 « s et 0 », vous utilisez le double signe moins, qui est techniquement appelé lopérateur double unaire. Le premier unaire contraint respectivement TRUE / FALSE à -1/0. Le deuxième unaire nie les valeurs, cest-à-dire inverse le signe, les transformant en +1 et 0, ce que la fonction SUMPRODUCT peut comprendre.
Jespère que lexplication ci-dessus a du sens. Et même si ce nest pas le cas, rappelez-vous simplement cette règle de base: utilisez lopérateur double unaire (-) lorsque vous utilisez des opérateurs de comparaison dans vos formules SUMPRODUCT.
Utilisation dExcel SUM dans des formules matricielles
Comme vous vous en souvenez, Microsoft a implémenté la fonction SUMIFS dans Excel 2007. Si quelquun utilise toujours Excel 2003, 2000 ou antérieur, vous devrez utiliser une formule matricielle SUM pour ajouter des valeurs avec plusieurs critères AND. Naturellement, cette approche fonctionne également dans les versions modernes dExcel 2013 – 2007 et peut être considéré comme un équivalent à lancienne de la fonction SUMIFS.
Dans les formules SUMIF décrites ci-dessus, vous avez déjà utilisé des arguments de tableau, mais une formule de tableau est quelque chose de différent.
Exemple 1. Somme avec plusieurs critères AND dans Excel 2003 et versions antérieures
Revenons au tout premier exemple où nous avons trouvé une somme de montants relatifs à un fruit et à un fournisseur donnés:
Comme vous le savez déjà, cette tâche est facilement accomplie en utilisant un formule SUMIFS ordinaire:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Et maintenant, voyons comment la même tâche peut être accomplie dans les premières versions « sans SUMIFS » dExcel . Tout dabord, vous notez toutes les conditions à remplir sous la forme range = « condition ». Dans cet exemple, nous avons deux paires plage / condition:
Condition 1: A2: A9 = « pommes »
Condition 2: B2: B9 = « Pete »
Ensuite, vous écrivez une formule SUM qui « multiplie » toutes vos paires plage / condition, chacune entre crochets. Le dernier multiplicateur est la plage de somme, C2: C9 dans notre cas:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Comme illustré dans la capture décran ci-dessous, la formule fonctionne parfaitement dans la dernière version dExcel 2013.
Exemple 2. Formules matricielles SUM dans les versions modernes dExcel
Même dans les versions modernes dExcel, la puissance de la fonction SOMME ne doit pas être sous-estimée. La formule matricielle SUM nest pas simplement de la gymnastique de lesprit, mais a une valeur pratique, comme le montre lexemple suivant.
Supposons , vous avez deux colonnes, B et C, et vous devez compter combien de fois la colonne C est supérieure à la colonne B, lorsquune valeur de la colonne C est supérieure ou égale à 10. Une solution immédiate qui me vient à lesprit consiste à utiliser la SOMME formule matricielle:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Je ne vois aucune pratique application à la formule ci-dessus? Pensez-y dune autre manière:)
Supposons que vous ayez la liste des commandes comme celle illustrée dans la capture décran ci-dessous et que vous souhaitiez savoir combien de produits nont pas été livrés en entier à une date donnée. Traduit dans la langue dExcel, nous avons les conditions suivantes:
Condition 1: Une valeur dans la colonne B (articles commandés) est supérieure à 0
Condition 2: Une valeur dans la colonne C (livré) en moins que dans la colonne B
Condition 3: Une date dans la colonne D (date déchéance) est inférieure au 01/11/2014.
Mettre les trois fourchettes / conditions paires ensemble, vous obtenez la formule suivante:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Eh bien, les exemples de formules abordés dans ce didacticiel nont fait queffleurer la surface de ce que les fonctions Excel SUMIFS et SUMIF peuvent vraiment faire. combien de conditions complexes vous devez considérer.
- Comment utiliser SUMIF dans Excel – exemples de formules pour additionner conditionnellement les cellules
- Comment utiliser Excel SUMIFS et SUMIF avec plusieurs critères – exemples de formule
- Comment utiliser VLOOKUP & fonctions SUM ou SUMIF dans Excel
- SUMIF dans Google Sheets avec des exemples de formules
- COUNTIF dans Excel – compter sinon vide, supérieur à, dupliqué ou unique
- Comment compter et additionner les cellules par couleur dans Excel