Vous l’avez déjà probablement rencontré lors de l’utilisation de fonctions comme DSOMME, SUMIFS, SUMPRODUCT ou QUERY : le problème d’utiliser deux fois le même champ critère.
SUMIFS et DSOMME sont deux fonctions de somme conditionnelle avec plusieurs critères. SUMIFS est une fonction logique, tandis que DSOMME est une fonction de base de données.
Que ce soit pour SUMIFS ou DSOMME, vous pouvez obtenir le même résultat en utilisant correctement le critère. SUMPRODUCT peut également remplacer les deux autres fonctions dans Google Sheets.
Avec DSOMME, il est simple d’entrer les critères sous les titres de champ. Cependant, les choses se compliquent lorsque vous souhaitez inclure deux fois la même colonne de critères (plage de critères) dans SUMIFS.
Il existe trois approches principales pour utiliser deux fois le même champ de critères dans la fonction SUMIFS dans Google Sheets :
Combiner deux formules SUMIFS :
Cette approche consiste à additionner deux formules SUMIFS, chaque formule utilisant un critère différent dans la même plage.
Utiliser un contournement de la fonction SUBSTITUTE :
Cette approche consiste à utiliser la fonction SUBSTITUTE pour remplacer l’un des critères par l’autre.
Utiliser REGEXMATCH :
Cette approche consiste à utiliser la fonction REGEXMATCH pour faire correspondre les deux critères dans la même plage. Cela renverra VRAI ou FAUX. Nous pouvons utiliser VRAI comme critère.
Voyons quelques exemples :
Syntaxe de la fonction SUMIFS dans Google Sheets :
SUMIFS(plage_somme, plage_critère1, critère1, [plage_critère2, ...], [critère2, ...])SUMIFS : même plage de critères pour la comparaison
Lorsque nous utilisons des critères de dates ou de nombres avec des opérateurs de comparaison dans la fonction SUMIFS, nous n’avons aucun problème à utiliser deux fois la même plage de critères.
Par exemple, supposons que votre plage de dates soit C7:C14, vous pouvez inclure cette plage de dates deux fois dans SUMIFS en tant que plage_critère1 et plage_critère2 :
=SUMIFS(D7:D14, C7:C14, ">= " & DATE(2017,7,1), C7:C14, "<= " & DATE(2017,7,31), A7:A14, "Myron Ambriz", B7:B14, "North")Cette formule SUMIFS va additionner la plage D7:D14 si :
- C7:C14 est supérieure ou égale au 1er juillet 2017
- C7:C14 est inférieure ou égale au 31 juillet 2017
- Le texte dans A7:A14 est « Myron Ambriz »
- Le texte dans B7:B14 est « North »
Vous pouvez entrer ces critères dans la plage de cellules C1:C4 et utiliser la formule comme suit :
=SUMIFS(D7:D14, C7:C14, ">= " & C1, C7:C14, "<= " & C2, A7:A14, C3, B7:B14, C4)
Permettez-moi de vous expliquer comment gérer la même plage de critères deux fois lorsqu’il n’y a pas d’opérateurs de comparaison.
Différentes méthodes pour inclure deux fois le même champ dans la fonction SUMIFS
C’est la partie intéressante. Dans cette section, vous pouvez apprendre les différentes méthodes que nous avons mentionnées au début pour inclure deux fois le même champ (plage de critères) dans SUMIFS dans Google Sheets.
Problème : Comment utiliser SUMIFS pour additionner le montant des ventes (C7:C14) de « Philip Nida » (A7:A14) dans « North » ou « South » (B7:B14) ?
Méthode 1: Combinez deux formules SUMIFS
Commençons par la forme la plus simple d’utilisation du même champ de critères deux fois, qui consiste à combiner deux formules SUMIFS.
Cette approche consiste à additionner deux formules SUMIFS :
=SUMIFS(C7:C, A7:A, "Philip Nida", B7:B, "North") + SUMIFS(C7:C, A7:A, "Philip Nida", B7:B, "South")Cette formule remplace le critère codé en dur par des références de cellules :
=SUMIFS(C7:C, A7:A, C1, B7:B, C2) + SUMIFS(C7:C, A7:A, C1, B7:B, C3)
Méthode 2: Utilisez une astuce avec la fonction SUBSTITUTE
Cette approche consiste à utiliser la fonction SUBSTITUTE pour remplacer l’un des critères par l’autre.
Par exemple, pour additionner les valeurs dans C7:C14 lorsque les valeurs dans A7:A14 sont « Philip Nida » et B7:B14 sont « North » ou « South », vous pouvez utiliser la formule suivante :
=ArrayFormula(SUMIFS(C7:C14, A7:A14, "Philip Nida", SUBSTITUTE(B7:B14, "South", "North"), "North"))Nous avons utilisé la fonction SUBSTITUTE pour remplacer « South » par « North » dans la colonne B. Ainsi, nous n’avons pas besoin d’utiliser deux fois le même champ de critères dans la fonction SUMIFS.
La fonction SUBSTITUTE nécessite le support de ARRAYFORMULA.
La même formule SUMIFS avec des critères codés en dur remplacés par des références de cellules :
=ArrayFormula(SUMIFS(C7:C, A7:A, C1, SUBSTITUTE(B7:B, C3, C2), C2))Méthode 3: Utilisez REGEXMATCH
Cette approche consiste à utiliser la fonction REGEXMATCH pour faire correspondre les deux critères dans la même plage. Cela renverra VRAI pour les lignes correspondantes, qui seront utilisées comme critère.
Par exemple, pour additionner les valeurs dans C lorsque les valeurs dans la colonne A sont « Philip Nida » et B est « North » ou « South », vous pouvez utiliser la formule suivante :
=ArrayFormula(SUMIFS(C7:C14, A7:A14, "Philip Nida", REGEXMATCH(B7:B14, "North|South"), TRUE))La même formule avec les critères remplacés par des références de cellules :
=ArrayFormula(SUMIFS(C7:C14, A7:A14, C1, REGEXMATCH(B7:B14, C2 & "|" & C3), TRUE))En relation: REGEXMATCH dans SUMIFS et plusieurs colonnes de critères dans Google Sheets.
Quelle méthode est la plus efficace pour utiliser deux fois le même champ dans la fonction SUMIFS ?
Des trois méthodes, je préfère REGEXMATCH (méthode n°3). Lorsque vous souhaitez utiliser le même champ de critères plusieurs fois, il est facile d’inclure les critères en les séparant simplement par un pipe.
La méthode SUBSTITUTE n°2 nécessite des emboîtements multiples en fonction du nombre de critères.
La méthode SUMIFS n°1 peut nécessiter l’ajout de plus d’une formule SUMIFS, ce qui peut rendre la formule encombrée.
En conclusion, la formule SUMIFS ne prend pas en charge l’utilisation du même champ de critères plusieurs fois sans opérateurs de comparaison.
Dans la méthode SUBSTITUTE, nous remplaçons un critère par l’autre, il n’y a donc essentiellement qu’un seul critère. Lors de l’utilisation de REGEXMATCH, nous utilisons le critère VRAI, qui est également un seul critère.
