Téléchargez l'exemple de classeur
Ce didacticiel montrera comment utiliser la fonction SUMIFS pour additionner les données correspondant à des mois spécifiques dans Excel et Google Sheets.
Somme si par mois
Tout d'abord, nous montrerons comment additionner des données correspondant à des dates qui tombent dans un mois et une année spécifiques.
Nous pouvons utiliser la fonction SUMIFS, ainsi que les fonctions DATE, ANNÉE, MOIS et MOIS pour additionner les Nombre de ventes au sein de chaque Mois.
| 1 | =SOMME(C3:C9,B3:B9,">="&DATE(ANNÉE(E3),MOIS(E3),1),B3:B9,"<="&MOIS(E3,0)) |
Pour construire la formule ci-dessus, nous commençons par définir la plage de dates pour chaque mois. Nous utilisons la fonction DATE pour définir le premier jour du mois (ex. 5/1/2021). Nous pouvons le faire en « programmant en dur » la date dans la formule :
| 1 | ">="&DATE(2021,5,1) |
Ou, mieux encore, nous pouvons rendre les critères flexibles en faisant référence à une date dans la cellule E3, en extrayant l'année et le mois de la date (et en définissant le jour égal à 1), comme le montre l'exemple ci-dessus :
| 1 | ">="&DATE(ANNEE(E3),MOIS(E3),1) |
Pour définir le dernier jour du mois, nous pouvons utiliser la fonction EOMONTH :
| 1 | "<="&SEOMOIS(E3,0) |
En mettant tous ces critères ensemble, on peut écrire la formule SUMIFS suivante :
| 1 | =SOMME(C3:C9,B3:B9,">="&DATE(ANNÉE(E3),MOIS(E3),1),B3:B9,"<="&MOIS(E3,0)) |
Verrouillage des références de cellule
Pour rendre nos formules plus faciles à lire, nous avons montré les formules sans références de cellules verrouillées :
| 1 | =SOMME(C3:C9,B3:B9,">="&DATE(ANNÉE(E3),MOIS(E3),1),B3:B9,"<="&MOIS(E3,0)) |
Mais ces formules ne fonctionneront pas correctement si elles sont copiées et collées ailleurs dans votre fichier. Au lieu de cela, vous devez utiliser des références de cellules verrouillées comme ceci :
| 1 | =SUMIFS($C$3:$C$9,$B$3:$B$9,">="&DATE(ANNÉE(E3),MOIS(E3),1),$B$3:$B$9,"<=" &EMOIS(E3,0)) |
Lisez notre article sur le verrouillage des références de cellules pour en savoir plus.
Formatage des valeurs mensuelles
Dans cet exemple, nous avons répertorié les mois dans la colonne E. Ces valeurs de mois sont en fait des dates formatées pour omettre le jour, à l'aide d'un formatage numérique personnalisé.
Le format de données personnalisé est « mmm yyyy » pour afficher mai 2022.
Somme par mois sur plusieurs années
L'exemple ci-dessus a additionné les données avec des dates comprises dans un mois et une année spécifiques. Au lieu de cela, vous pouvez additionner les données avec des dates qui tombent dans un mois de n'importe quelle année à l'aide de la fonction SOMMEPROD.
| 1 | =SOMMEPROD(C3:C8,--(MOIS(B3:B8)=MOIS(G3))) |
Dans cet exemple, nous utilisons la fonction SOMMEPROD pour effectuer des calculs compliqués « somme si ». Parcourons la formule.
Voici notre formule finale :
| 1 | =SOMMEPROD(C3:C8,--(MOIS(B3:B8)=MOIS(G3))) |
Tout d'abord, la fonction SOMMEPROD répertorie les Nombre de ventes pour chaque Date de vente puis compare le mois de chaque Date de vente contre le spécifié Mois, renvoyant TRUE si les mois correspondent, ou FALSE si ce n'est pas le cas :
| 1 | =SOMMEPROD({30;42;51;28;17;34},--({VRAI;FAUX;VRAI;FAUX;VRAI;FAUX})) |
Ensuite, les doubles tirets (-) convertissent les valeurs VRAI et FAUX en 1 et 0 :
| 1 | =SOMMEPROD({30;42;51;28;17;34},{1;0;1;0;1;0}) |
La fonction SOMMEPROD multiplie ensuite chaque paire d'entrées dans les tableaux :
| 1 | =SOMMEPROD({30;0;51;0;17;0}) |
Enfin, les nombres du tableau sont additionnés :
| 1 | =98 |
Plus de détails sur l'utilisation des instructions booléennes et de la commande « - » dans une fonction SOMMEPROD peuvent être trouvés ici
Somme si par mois dans Google Sheets
Ces formules fonctionnent exactement de la même manière dans Google Sheets que dans Excel.
