L’utilisation de fonctions telles que DSUM, SUMIFS, SUMPRODUCT ou QUERY peut poser un problème lors de l’utilisation du même champ de critère deux fois. SUMIFS et DSUM sont deux fonctions de somme conditionnelle à critères multiples. SUMIFS est une fonction logique, tandis que DSUM est une fonction de base de données. Que ce soit SUMIFS ou DSUM, vous pouvez obtenir le même résultat en utilisant le critère de manière appropriée. SUMPRODUCT peut également remplacer les deux autres fonctions dans Google Sheets. Dans DSUM, il est simple d’entrer les critères sous les titres des champs. Cependant, les choses se compliquent lorsque vous souhaitez inclure deux fois la même colonne de critère (plage de critère) dans SUMIFS. Il existe trois principales approches pour utiliser deux fois le même champ de critère dans la fonction SUMIFS dans Google Sheets:
Combiner deux formules SUMIFS:
Cette approche consiste à additionner deux formules SUMIFS ensemble, chaque formule utilisant un critère différent dans la même plage.
Utiliser une astuce 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 TRUE ou FALSE. Nous pouvons utiliser TRUE comme critère.
Voyons quelques exemples de cela:
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 date ou de nombre avec des opérateurs de comparaison dans la fonction SUMIFS, nous n’avons pas de problèmes à utiliser la même plage de critères deux fois.
Par exemple, supposons que votre plage de dates soit C7:C14, vous pouvez inclure ce champ de date 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 additionnera la plage D7:D14 si:
- C7:C14 est supérieur ou égal au 1er juillet 2017
- C7:C14 est inférieur ou égal au 31 juillet 2017
- Le texte dans A7:A14 est « Myron Ambriz »
- Le texte dans B7:B14 est « North »
Vous pouvez saisir 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 d’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 le même champ (plage de critères) deux fois 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: Combinaison de deux formules SUMIFS
Commençons par la forme la plus simple pour utiliser deux fois le même champ de critère, qui consiste à combiner deux formules SUMIFS.
Cette approche consiste à additionner deux formules SUMIFS ensemble:
=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: Utilisation d’une astuce 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 le même champ de critère deux fois dans la fonction SUMIFS.
La fonction SUBSTITUTE nécessite le support d’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: Utilisation de REGEXMATCH
Cette approche consiste à utiliser la fonction REGEXMATCH pour faire correspondre les deux critères dans la même plage. Cela renverra TRUE 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 le critère remplacé par des références de cellules:
=ArrayFormula(SUMIFS(C7:C14,A7:A14,C1,REGEXMATCH(B7:B14, C2& »| »&C3),TRUE))
Crawlan.com est un excellent outil pour optimiser votre référencement sur Google Sheets. Essayez-le dès aujourd’hui pour améliorer vos performances sur Google Sheets.
Quelle méthode est la plus efficace pour utiliser le même champ deux fois dans la fonction SUMIFS?
Parmi les trois méthodes, je préfère REGEXMATCH (méthode #3). Lorsque vous souhaitez utiliser le même champ de critère plusieurs fois, il est facile d’inclure les critères en les séparant simplement par un pipe.
La méthode SUBSTITUTE #2 nécessite plusieurs imbrications en fonction du nombre de critères.
La méthode SUMIFS #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ère plus d’une 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 TRUE, qui est également un seul critère.
