Google Sheets : faire des critères complexes pour SUMIF() et autres
Une fonction comme SUMIF (SOMME.SI si vous utilisez des formules en français) permet de faire une somme des éléments d’une plage répondant à un critère. On retrouve le même concept pour AVERAGEIF, SUMIFS ou encore COUNTIF. Sauf que quand on regarde la documentation, les critères semblent très basiques… Par exemple, si ont veut sommer les valeurs positives de la colonne A, la formule est SUMIF(A:A, ">0"). Si on veut sommer les lignes de la colonne A pour lesquelles les lignes de la colonne B sont « Validé », c’est SUMIF(B:B, "Validé", A:A). Mais comment faire si on souhaite appliquer une formule à une plage et utiliser le résultat pour valider le critère ?
Prenons cet exemple de données d’entrée :
Ce sont des simples opérations financières, avec une date et une valeur. Imaginons maintenant qu’on souhaite faire la somme des valeurs par mois. De manière intuitive, on se dit que pour chaque mois, il faut un SUMIF de la colonne B si le mois de la colonne A est celui du mois considéré. Par exemple, pour Janvier, on aurait envie de faire quelque chose comme SUMIF(MONTH(A:A), 1, B:B) mais ça ne marche pas. On se dit qu’il faudrait créer un range avec les mois de la colonne A, sans forcément créer une colonne pour ça (car oui, créer une colonne intermédiaire est une solution simple efficace). Bonne nouvelle, il existe la fonction qu’il nous faut ! C’est ARRAYFORMULA ! Si dans une cellule, vous mettez ARRAYFORMULA(DATE(A:A)), vous verrez la colonne se remplir avec les mois des dates. Il suffit donc d’injecter ça dans notre SUMIF.
Voici ce que ça pourrait donner :
La colonne D et E sont créées manuellement. La formule en pour la colonne F est SUMIF(ARRAYFORMULA(MONTH(A:A));D:D;B:B). L’utilisation de la colonne D (qu’on peut masquer, car il est vrai que visuellement c’est pas foufou) permet d’avoir la même formule dans toutes les cellules de la colonne F sans devoir incrémenter manuellement le numéro du mois dans la formule (le D:D remplace le numéro du mois). Essayez de le coder, vous verrez que c’est plus facile 😉
Pierre Gradot 