EXCEL SOMME.SI avec condition sur des dates

Résolu

claudio -

Bonjour,

Voila mon PB

Col A : des dates
Col B : Des valeurs

Formule : Si les dates saisies dans la col A sont comprises entre début et fin de mois, additionner les cellules de la Col B qui correspondent à ces dates. En gros additionner uniquement les cellules qui correspondent à une période donnée.

Merci à Tous

Configuration: Windows XP
Internet Explorer 7.0

Afficher la suite

A voir également

Somme si ens date mois
Excel somme date - Meilleures réponses
Somme si mois et année - Meilleures réponses
Comment faire une somme sur excel - Guide
Formule excel date + 6 mois ✓ - Forum Excel
Nb si ens non vide ✓ - Forum Excel
Mfg date - Forum PC portable
Somme en anglais excel - Guide

6 réponses

Résumé de la discussion

Dans un classeur Excel, il faut additionner les montants d'une colonne B lorsque les dates de la colonne A appartiennent à un mois donné entre le début et la fin du mois. Plusieurs réponses proposent des formules matricielles ou les fonctions SOMMEPROD et MOIS pour filtrer par mois, avec des plages de données identiques et des conditions combinées. Des échanges détaillent aussi les pièges liés au traitement des dates comme numériques et l’intérêt d’utiliser MOIS sur des dates plutôt que des textes, dans certains cas. En cas de besoin, des variantes comme SOMME ou SOMMEPROD avec des opérateurs logiques et des données sur plusieurs feuilles sont évoquées pour adapter les conditions et les plages.

Réponse 1 / 6 - Meilleure réponse
La meilleure réponse est la réponse qui a été validée par nos équipes.
gbinforme Contributeur
bonjour

Si en D1, tu mets le mois à comptabiliser, tu peux le faire avec cette formule :
```
=SOMME((MOIS(A1:A65535)=D1)*(B1:B65535))
ou
=SOMME((A1:A65535>=DATEVAL("1/9/2007"))*(A1:A65535<=DATEVAL("30/9/2007"))*(B1:B65535))
```
C'est une formule matricielle à valider par CTRL + MAJ + ENTREE (3 doigts au moins)
Il faut que les deux plages soit identiques en taille, même si elles ne sont pas sur la même feuille, ou ne commencent pas à la même ligne et elles ne peuvent pas être saisies comme une colonne entière.

Il est bien sûr possible de rajouter d'autres conditions, saisies entre parenthèses et séparées par "*"
```
=SOMME((MOIS(A1:A65535)=D1)*(C1:C65535)="valeur")*(B1:B65535))
```
10
1. claudio
  
  Gbinforme.
  
  La première fonction fonctionne impécable.
  
  Pour la deuxième j'ai pas réussi.
  
  Je te remercie pour tes infos,
  
  Cordialement,
  
  Claude
  
  0
2. claudio
  
  Bonjour GBINFORME,
  
  Merci pour ta réponse rapide.
  La première fonctionne à la perfection.
  Pour la deuxième j'ai pas réussi, je cherche encore pourquoi....
  
  Je vais encore te prendre la tête mais je tente le coup....
  
  En vrai j'ai 3 colonnes.
  
  Col A : 2 valeurs ( entrée ou sortie)
  Col B : des dates
  Col C : des montants
  
  Dans une cellule je veux additionner tous les montants de la colonne C qui sont sorti entre 2 dates (1er et dernier jour du mois)
  mais aussi qui sont comptabilisés comme sortie dans la colonne A. Donc 2 conditions pour additionner mes montants de ma colonne C.
  Et j'ai pas compris la formule que tu as écrit. Peux tu me donner un exemple STP.
  
  Je te remercie d'avance
  
  Cordialement,
  
  Claude
  
  0
3. gbinforme Contributeur
  
  bonjour
  
  Dans une cellule je veux additionner tous les montants de la colonne C qui sont sorti entre 2 dates (1er et dernier jour du mois)
  mais aussi qui sont comptabilisés comme sortie dans la colonne A. Donc 2 conditions pour additionner mes montants de ma colonne C.
  
  =SOMME((MOIS(B1:B65535)=D1)*(A1:A65535)="sortie")*(C1:C65535))
  
  Modifier D1 par la cellule où tu mets le mois concerné.
  Modifier "sortie" par la valeur mise dans la cellule A pour indiquer la sortie.
  
  0
4. lmi007
  
  BONJOUR
  
  J'ai une premiere colonne avec des dates
  une deuxieme avec des noms
  1 premier critere avec des noms
  2 critere avec des dates
  
  j'aimerai additionner toutes les cellules des
  
  0
5. lmi007
  
  bonjour
  
  1ère colonne : 12 mois de l'année (ex 1/12/2008)
  2ème colonne : 12 noms differents (ex Alain)
  3ème colonne : type de RDV (ex vitrine)
  4ème colonne : résultat
  
  1er critère : mois
  2ème critère : nom
  
  Comment faire pour comptabiliser le nombre de même cellule d'Alain, Patrick et Franck chaque mois en respectant l'ensemble des criteres :
  
  ex : Alain a réalisé 5 vitrines respectivement le 1/1/2008, 3/1/2008, 7/2/2008, 16/2/2008, 19/2/2008
  Patrick 3 vitrines respectivement le 12/3/2008, 13/5/2008, 15/5/2008
  Franck 2 vitrines respectivement le 1/5/2008, 7/5/2008
  
  j'aimerai une formule dans la cellule résultat m'indiquant (si je prend l'ex d'Alain, Patrick et Franck) :
  
  ALAIN
  mois résultat
  janvier 2 (NB d'Alain apparaissant en janvier)
  fevrier 3 (NB d'Alain apparaissant en fevrier)
  mars 0
  avril 0
  mai 0
  
  PATRICK
  mois résultat
  janvier 0
  fevrier 0
  Mars 1 (NB de Patrick apparaissant en mars)
  Avril 0
  mai 2 (NB de Patrick apparaissant en mai)
  
  FRANCK
  mois résultat
  janvier 0
  fevrier 0
  mars 0
  avril 0
  mai 2 (NB de Franck apparaissant en mai)
  juin 0
  juillet 0
  
  J'espère que ces explications vous aideront et que vous me trouverez une solution.
  Merci d'avance
  
  0
Réponse 2 / 6
grenouille731
Bonjour,
Voila, je pose mon problème à mon tour :
J'ai un beau classeur (dont vous avez la copie ci joint) avec pas mal de données concernant des matériaux.
J'ai ensuite créé une feuille par mois pour faire le total des produit en fonction donc du mois, du produit, du client. Malheureusement je suis un peu en panique car je pense qu'il faut que je mette plusieurs condition pour la fonction SOMMEPROD et la fonction qui me gene le plus pour l'intégrer, c'et la fonction date.
J'attend un sauveur potentiel.
Par avance, Merci.

7
1. Vaucluse Contributeur
  
  Bonjour grenouille
  Si vous souhaitez que l'on vous sorte de votre mare, dites nous où est la copie "ci jont". En principe , il faut un lien pour l'atteindre!
  Dans l'attente
  Un vieux crapaud
  CRDLMNT
  
  0
2. grenouille731
  
  @VaucluseMAMANNNNNNNNNNNNN
  je n'arrive pas à le mettre mon lien !!!!!!! Honte à moi.
  Help please.
  
  0
3. Vaucluse Contributeur
  
  @grenouille731Essayez ici et suivez scupuleusement la procédure, car je ne pense pas que maman puisse faire quelque chose
  
  https://www.cjoint.com/
  
  0
4. grenouille731
  
  https://www.cjoint.com/?eroO5L3fL6
  on ne sais jamais tant il a marché......
  mais j'en doute puisqu'il fait quand même 2.3Mo....
  
  0
5. Vaucluse Contributeur
  
  @grenouille731.... marche pas...!
  Sans doute avez vous réutilisé un ancien contact, car le lien n'est plus disponibles (dixit cijoint)
  
  0
Réponse 3 / 6
eriiic Contributeur
Bonsoir tout le monde,

Si la formule de gbinforme ne fonctionne pas pour vous, utilisez à ce moment là SOMMEPROD en remplaçant les * par un ;
Ce qui donne =SOMMEPROD((MOIS(B1:B65535)=D1);(A1:A65535)="sortie");(C1:C65535)) il y a peut-être un problème de version excel

Le principe est le suivant :
On a 2 matrices de test (A et B) et une matrice de valeur (C)
Si on a "sortie" en A1 et qu'en B1 on saisi =(A1="sortie") pour excel c'est un test et il repondra VRAI
Si on multiplie ce VRAI par une valeur numérique excel comprend qu'on fait un calcul et transforme ce VRAI en 1. Ex: saisir en C1: =B1*1 => 1*1 => 1
Bien sûr si on a autre chose que "sortie" en A1 excel répondra FAUX qui multiplié par 1 => 0*1 => 0

Ce qu'on vient de faire pour une ligne, excel le fait pour toutes les lignes des plages, il s'agit d'une multiplication de matrices où toutes les lignes sont traitées individuellement (toutes les matrices doivent avoir la même dimension)

ex avec des valeurs bidons:
Supposons A1= "sortie", B1=3, C1=1235
tests et multiplications avec les évaluations successives d'excel :
(A1="sortie") * (B1 = 3) * (1235)
VRAI * VRAI * 1235
1 * 1 * 1235 = 1235

ligne 2, supposons A2="nok" , B2=3, C2= 15
tests et multiplications :
(A2="sortie") * (B2=3) * (15)
FAUX * VRAI * 15
0 * 1 * 15 = 0

Et comme on lui a demandé de faire la somme de toutes les lignes il fera 1235 + 0 => 1235

J'ai essayé de décomposer le travail d'excel pour éclaircir les choses mais ce n'est pas une notion facile à apprehender ni à expliquer.

Mais c'est vraiment très puissant et pratique (adieu la limitation à 7 SI imbriqués et les formules si difficiles à lire), je vous encourage à essayer jusqu'à ce que vous y arriviez

eric

2
1. Vaucluse Contributeur
  
  Bonsoir Eric, je ferme pour ce soir, mais j'essaie ça dès que possible et je t'en reparle. Je ne crois pas que cela solutionne mon problème de "MOIS"!
  Que faut il faire pour que Excel reconnaisse un mois dans une série de date?
  Que doit on rentrer en D1 ? le nom du mois, la date du premier jour?
  Ou doit on écrire aussi MOIS(D1) dans la formule au lieu de D1?
  BCRDLMNT
  
  0
2. gbinforme Contributeur
  
  bonjour
  
  =SOMME((MOIS(B1:B65535)=D1)*(A1:A65535)="sortie")*(C1:C65535)) ou =SOMMEPROD((MOIS(B1:B65535)=D1);(A1:A65535)="sortie");(C1:C65535))
  
  ne fonctionnent pas car il y a une erreur de saisie que j'avais vue mais j'ai eu une erreur forum au moment d'envoyer ma correction peut-être dûe à l'autre message ?
  
  par contre ces deux formules
  =SOMME((MOIS(B1:B65535)=D1)*(A1:A65535="sortie")*(C1:C65535)) à valider par ctrl + maj + entrée ou =SOMMEPROD((MOIS(B1:B65535)=D1)*(A1:A65535="sortie")*(C1:C65535)) à valider par entrée
  
  fonctionnent tout à fait correctement et sont équivalentes.
  
  J'avais laissé traîner une fermeture de parenthèse intempestive en complétant la formule sur le forum,
  et eriiic a recopié mon erreur puis a remplacé à tord les "*" par des ";", ce qui ne correspond pas d'ailleurs à l'explication du fonctionnement qu'il donne et les ";" servent à rajouter d'autres matrices.
  
  Mais c'est vraiment très puissant et pratique : tout à fait d'accord !
  Je dirais même qu'il est indispensable de connaître et d'utiliser ses formules pour la gestion des tableaux.
  
  0
3. eriiic Contributeur
  
  "ce qui ne correspond pas d'ailleurs à l'explication du fonctionnement qu'il donne"
  SOMMEPROD(matrice1;matrice2;matrice3,...) : Donne la somme des produits des éléments de matrice correspondants.
  N'hésite pas à me corriger s'il y a des erreurs ou a expliquer différemment si besoin.
  eric
  
  1
4. claudio
  
  Bonsoir à tous,
  
  Je vais êttre franc avec vous, je suis à l'origine de ce post et je ne comprend toujours pas.
  J'ai compris le, principe de la formule matricielle.
  Mais je fais ça chez moi et ça ne fonctionne pas.
  Quelqu'un peut-il m'envoyer un fichier avec un exemple
  
  Je vous re donne les donnes :
  
  Col A : des dates : exemple ( du 01/01/2007 au 30/02/2007)
  Col B : 1 valeur par cellulle ( entrée ou sortie)
  Col C : des montants ( qui correspondent à des ventes par jour)
  
  Je veux pouvoir additionner uniquement les montants du mois de janvier et qui sont marqués "sortie"
  
  Merci à tous de votre patience et de votre compréhension...
  
  Claude
  
  0
5. gbinforme Contributeur
  
  @eriiicbonjour
  
  Désolé, je n'ai pas compris ce que tu voulais me dire ?
  
  SOMMEPROD(matrice1;matrice2;matrice3,...) : oui mais en l'occurrence, nous n'avions qu'une matrice à additionner même si elle s'agrémentait de deux conditions ?
  
  N'hésite pas à me corriger... si besoin
  Rien à corriger car tes explications effectivement me paraissent très judicieuses sur un sujet complexe comme tu le fais remarquer.
  
  0
Réponse 4 / 6
Vaucluse Contributeur
Bonjour Claudio

Une autre solution:
Colonne A (entrée ou sortie)
Colonne B les dates
Colonne C les montants
Tout cela a partir de la ligne A2
Utilisez par exemple les renvois:
en A1 "Sortie", en B1 =MOIS(D1)
Rentrez la date souhaité en D1_(Voir ma remarque en dessous en gras)
Colonne E, sur la hauteur du champ concerné à partir de la ligne A2:
=SI(MOIS(B2)=$B$1;C2;0)*SI(A2=$A$1;1;0)
En haut de la colonne E, vous pouvez faire le cumul des résultats
On peut aussi bien entendu utiliser pour ce renvoi, une colonne hors champ, masquée, en renvoyant le cumul au bon endroit
Pour info, dans ma configuration, et c'est peut être du à mes réglages, les formules conditionnelles ne marchent avec l'info" MOIS" que si la date est préalablement transformée en numérique, d'où ma proposition de passer par D1 pour aller en B1 renvoyer le code.Par contre cette option a un inconvénient majeur, elle ne peut pas couvrir plus de 12 mois

Je profite de cette intervention pour demander à gbinforme:
_Comment il fait fonctionner MOIS à partir d'un renvoi sur une date, et non sur une valeur numérique
_Comment il fait fonctionner sa dernière formule qu iinclut dans des opèrations une valeur texte ("Sortie")
Je suis vivement interessé par les réponses, donc,merci d'avance

Une autre option, à tout hasard, pour votre tableau, en lecture directe

Libérer la ligne A1 en décalant le tableau
EN C1; placer la formule:=SOUS.TOTAL(9;C1:C1000)
En colonne D, renvoyez comme précédemment=MOIS(B2) sur toute la hauteur du champ
Sélectionner le champ complet du tableau sur A à D
Barre d'outil / Données / Filtrer / Filtre automatique
Dans les déroulants, sélectionnez sur colonne A:"Sortie", puis sur colonne D le mois qui vous interesse.
Votre total s'affiche en C1, de plus vous avez à l'affichage la liste concernée.

BCRDLMNT

Science sans conscience n'est que ruine de l'Ame

0
1. gbinforme Contributeur
  
  bonjour
  
  Je profite de cette intervention pour demander à gbinforme:
  
  Tu nous expliques beaucoup de choses mais je pense qu'il y a des concepts qui ne sont pas très très clairs pour toi. ainsi, quand tu nous dis :
  
  _Comment il fait fonctionner MOIS à partir d'un renvoi sur une date, et non sur une valeur numérique
  Pour faire fonctionner MOIS à partir d'une date, il n'y a rien de plus normal sous Excel car une date est toujours enregistrée sous la forme d'un nombre décimal :
  - les valeurs entières représentent le nombre de jours depuis le 01/01/1900 ou le 01/01/1904 selon le calendrier choisi.
  - les valeurs décimales représentent la division du jour, c'est-à-dire les heures.
  
  L'affichage d'une date dans une cellule n'est donc que de la présentation qui ne change pas la valeur de la cellule.
  Si tu veux le vérifier, prends une cellule définie en standard et tu saisies par exemple 1,5.
  Tu définies le format en jj/mm/aaaa et tu obtiens : 01/01/1900
  Tu définies le format en [h]:mm et tu obtiens : 36:00
  Tu définies le format en jj/mm/aaaa hh:mm et tu obtiens : 01/01/1900 12:00
  
  Pour info, dans ma configuration, et c'est peut être du à mes réglages, les formules conditionnelles ne marchent avec l'info" MOIS" que si la date est préalablement transformée en numérique,
  Quels que soient tes réglages, je doute donc fortement de la véracité de cette affirmation...
  
  _Comment il fait fonctionner sa dernière formule qu iinclut dans des opèrations une valeur texte ("Sortie")
  =SOMME((MOIS(B1:B65535)=D1)*(A1:A65535)="sortie")*(C1:C65535))
  
  Cette formule fonctionne en fonction des données qui sont mentionnées dans la question :
  Col A : 2 valeurs ( entrée ou sortie)
  Col B : des dates
  Col C : des montants
  Donc je traite les valeurs Col A ( entrée ou sortie) comme du texte ( mais c'est un test et non une opération de calcul ), les dates Col B comme des dates ( mais c'est un test sur le mois ) et les montants Col C comme du numérique à totaliser ( là, c'est une vrai opération ) , cela me parait la base de tout calcul tableur.
  
  Si tu penses que l'on peut traiter d'une autre façon, je suis entièrement preneur et il est tout à fait possible d'apprendre plein d'autres méthodes car avant de faire le tour des possibilités d'un tableur il y a des jours à passer : je ne demande qu'à découvrir d'autres possibilités.
  
  Je suis vivement interessé par les réponses, donc,merci d'avance
  
  Voilà ce que j'ai pu apporter comme réponses et si tu veux continuer le dialogue, ce sera avec le plus grand plaisir.
  
  3
2. Vaucluse Contributeur
  
  Gbinforme
  
  je ne comprend pas bien le ton de votre message, surtout d'une part le début, ou je n'explique que ce que je constate et
  ensuite vos explications sur les dates, ce que je connais parfaitement, même si cela vous étonne,mais voyez vous :
  
  Si je met une formule conditionnelle incluant "MOIS", elle ne marche pas si je traite une liste de date (jour/mois /année) avec une cellule ou je rentre "Octobre" par exemple.
  Si je rentre une date dans la cellule une date (1/10/07) par exemple, elle ne marche qu'avec cette date précise et ne prend pas les cellules du même mois et d'autres jours.
  
  Si je renvois les cellules de la liste à traiter avec =MOIS(), effectivement, là, je récupère une valeur numérique correspondante au mois concerné pour toutes les dates, et là, je peux traiter en rentrant dans la cellule de référence le N° du mois recherché.
  C'est comme ça, je n'y peux rien.Et ma question portait sur le pourquoi et non sur la configuration des dates dans excel.
  Quant à la formule que vous donner incluant l'item="sortie" que vous préconnisez, je l'ai testée chez moi, elle ne renvoi que "valeur" quand les conditions sont remplies.Je ne savais pas sincéremnt que l'on pouvait associer un signe * avec une valeur texte et je me limitais au ;
  pour la bonne forme et pour conclure, je précise que je n'ai en aucun cas l'intention de donner des leçons à quelqu'un mais de comprendre pourquoi des solutions que je trouve sur ces forums et qui peuvent me servir ne marchent pas, comportement qui à mon sens ne justifie pas votre entrée en matière!.
  Je me permet de supposer que le fond de votre pensée ne se reflète pas dedans
  CRDLMNT
  PS: en revanche,et après un peu de réflexion, si quelque chose dans mes remarques a pu vous blesser, c'était bien involontaire et mes motivations ne sont que curiosité.
  
  2
Vous n’avez pas trouvé la réponse que vous recherchez ?

La communauté CCM répond en moyenne en moins de 2h.

Posez votre question
Réponse 5 / 6
aurel51
bonjour,

j'ai essayé d'intégrer vos formules sur mon fichier excel mais cela ne fonctionne pas.
j'aimerai avoir un coût total par mois. j'aimerai avoir le coût cumulé de tout mes articles pour janvier et à condition que la colonne (G) soit =oui pour chacun de mes articles.

=SOMMEPROD((MOIS(1)=Etalonnage!H7:H48);(Etalonnage!G7:G48)="oui")

la colonne H est rempli par mes dates et la colonne G si mon article est soldé ou non.

Merci de votre aide

0
1. gbinforme Contributeur
  
  bonjour
  
  j'ai essayé d'intégrer vos formules sur mon fichier excel mais cela ne fonctionne pas.
  
  Heureusement qu'elles ne fonctionnent pas car je me ferais du souci...
  Lorsque l'on saisi une formule il y a une syntaxe de saisie et l'on ne peut pas faire n'importe quoi.
  
  En traduisant ce que tu as essayé de faire, je te donnes la bonne syntaxe mais il faut que tu remplaces la dernière plage A7:A48 par la plage de coût de tes articles.
  ta version =SOMMEPROD((MOIS(1)=Etalonnage!H7:H48);(Etalonnage!G7:G48)="oui") corrigée =SOMMEPROD((MOIS(Etalonnage!H7:H48)=1)*1;(Etalonnage!G7:G48="oui")*1; (Etalonnage!A7:A48)) ou =SOMME((MOIS(Etalonnage!H7:H48)=1)*(Etalonnage!G7:G48="oui")*(Etalonnage!A7:A48)) en validant par ctrl+entrée+majuscule simultanées
  
  0
2. aurel51
  
  Merci beaucoup de ta réponse. En effet ma synthaxe n'était pas bonne. Ta formule marche très bien
  
  Problème résolu
  
  0
Réponse 6 / 6
nani
bonjour,

je souhaiterais savoir si quelqi'un pouvait m'aider

car j'ai realisé un tableau excel donc ds une colonne avec "date d'envoie" et 1 autre colonne "à relancer"

dans la colonne "à relancer" j'ai mis ca comme formule =SI(F3="non";K3+15;SI(F3="oui";K3+15;"")) mais le souci c'est je ne veux pas que ca tombe le samedi et dimanche.

comment faut il faire?.

merci d'avance pr votre aide

0
1. Vaucluse Contributeur
  
  Bonjour
  La solution la pus simple, à mon avis, serait, (étant donné que vos dates d'envoi ne doivent pas, du moins je pense , tomber un dimanche) serait de ramerner votre délai à 14 plutôt que 15, comme cela aucun risque, la date calculée porte le même jour que la date d'envoi!
  PS: pour être sure d'obtenir une réponse,évitez de vous accrocher sur un ancien sujetbpour poser vos questions, il est préférable d'ouvrir un nouveau sujet
  BCRDLMNT
  
  0
2. nani
  
  @Vaucluseok merci
  
  je sui d'accord avec vous pr reduire le nbre de jours
  
  mais les dates varie dc je retrouve le semadi et le dimanche
  
  il faudrait je pense une formule que ne me fasse pas tomber le samedi et dimanche
  
  0
3. Vaucluse Contributeur
  
  @naniRe
  Je ne comprends pas très bien votre problème.Avez vous des dates d'envoi qui tombe un samedi ou un dimanche?Car une date + 14 retombe nécessairement sur le même jour que la date initiale!
  BCRDLMNT
  
  0
4. nani
  
  @Vaucluseoui je viens juste de men apercevoir
  je me suis prise la tete pr rien
  merci
  
  0

EXCEL SOMME.SI avec condition sur des dates

6 réponses

Discussions similaires

Newsletters