Sommaire
Pour additionner par mois en colonnes, vous pouvez utiliser la fonction SUMIFS avec la fonction EOMONTH. Dans l'exemple illustré, la formule en G5 est:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Cette formule utilise trois plages nommées: «montant» (D5: D15), «client» (B5: B15) et «date» (C5: C15).
Explication
La fonction SUMIFS est conçue pour additionner les valeurs dans une plage basée sur un ou plusieurs critères. Dans ce cas, nous avons besoin de trois critères:
- Nom du client = client dans la colonne F
- Date> = premier du mois (à partir de la date de la ligne 4)
- Date <= fin du mois (à partir de la date de la ligne 4)
Point clé: les noms de mois dans G4: I4 sont en fait des dates valides, formatées avec le format numérique personnalisé "mmm". Cela nous permet d'utiliser les valeurs de date dans G4: I4 directement pour les critères n ° 2 et n ° 3 ci-dessus.
De retour dans SUMIFS, la première paire plage / critères est utilisée pour vérifier le nom du client:
client,$F5
Remarquez que F5 est une référence mixte, avec la colonne verrouillée. Cela permet à la ligne de changer lorsque la formule est copiée dans le tableau, mais le nom du client provient toujours de la colonne F.
La deuxième paire plage / critères est utilisée pour comparer les dates au premier du mois:
date,">="&G$4
Comme mentionné ci-dessus, les valeurs dans G4: I4 sont en fait des dates: 1er janvier 2019, 1er février 2019 et 1er mars 2019. Ainsi, ce critère vérifie simplement les dates supérieures aux dates du premier mois de la ligne 4. Notez que c'est une autre référence mixte, cette fois avec la ligne verrouillée. Cela permet à la colonne de changer au fur et à mesure que la formule est copiée dans le tableau, mais maintient le numéro de ligne fixe. La concaténation avec une esperluette (&) est nécessaire lors de la création de critères qui utilisent un opérateur logique et une valeur d'une autre cellule.
La troisième paire plage / critères est utilisée pour comparer les dates au dernier jour du mois:
date,"<="&EOMONTH(G$4,0)
Pour obtenir le dernier jour de chaque mois, nous utilisons la fonction EOMONTH à la date de l'en-tête de colonne de la ligne 4. Avec zéro fourni pour l'argument mois, EOMONTH renvoie le dernier jour du même mois. Encore une fois, la référence à G4 est mélangée pour empêcher la ligne de changer.
Solution de tableau croisé dynamique
Un tableau croisé dynamique serait une excellente solution à ce problème, car il peut automatiquement regrouper par mois sans aucune formule. Pour une comparaison côte à côte des formules et des tableaux croisés dynamiques, regardez cette vidéo: Pourquoi les tableaux croisés dynamiques.
