Somme du 1er du mois en cours jusqu'à la veille

[Laurentl]

Bonjour à tous!

En gros j'ai 2 colonnes. L'une avec des dates classées par ordre chronologique ascendant et une autre à côté avec des valeurs numériques.
Je cherche une formule qui me fasse la somme de cette 2eme colonne pour les dates du mois en cours jusqu'à la veille.
Je ne peux pas utiliser de somme.si.ens ou de si, je ne sais pas comment faire.
En pièce jointe un petit exemple.

Quelqu'un peut-il m'aider?

Merci d'avance!

 

[Dugenou]

Re : Somme du 1er du mois en cours jusqu'à la veille

Bonjour,
Pourquoi ne peux tu pas utiliser de somme.si.ens ? ce serait si simple ! : c'est un challenge ? un exercice ?

=SOMME.SI.ENS($D$2:$D$507;$C$2:$C$507;">="&A10;$C$2:$C$507;"<"&AUJOURDHUI())

du coup avec somme( decaler()) , mais c'est lourdingue :

=SOMME(DECALER($D$1;EQUIV(A10;C2:C507;0);;EQUIV(AUJOURDHUI();C2:C507;0)-EQUIV(A10;C2:C507;0)))

Cordialement

 

[CISCO]

Re : Somme du 1er du mois en cours jusqu'à la veille

Bonjour

Dans la série lourd

SOMMEPROD((MOIS(C2:C507)=MOIS(A10))*(ANNEE(C2:C507)=ANNEE(A10))*(C2:C507<AUJOURDHUI())*D2:D507)

@ plus

 

[Laurentl]

Re : Somme du 1er du mois en cours jusqu'à la veille

Bonjour à vous deux.
Merci pour vos réponses!

Dugenou, je ne connais pas cette syntaxe dans ton somme.si.ens. je ne la comprends pas...
Elle fonctionne en effet, merci encore, mais pourquoi quand je remplace $D$2:$D$507 par D et $C$2:$C$507 par C:C ça ne fonctionne plus? On est obligé de fixer la matrice?
Ca me solutionne le probleme, mais j'aimerais comprendre.

Cisco, en effet, cette solution est un peu lourde...

 

[job75]

Re : Somme du 1er du mois en cours jusqu'à la veille

Bonsoir Laurentl, Dugenou, CISCO,

Si l'on veut une formule qui fonctionne aussi sur Excel 2003 il ne faut pas utiliser SOMME.SI.ENS.

Utiliser simplement SOMME.SI :

=SOMME.SI(C:C;"<"&AUJOURDHUI();D:D)-SOMME.SI(C:C;"<"&A10;D:D)

A+

 

[CISCO]

Re : Somme du 1er du mois en cours jusqu'à la veille

Bonjour

@ job75

J'y avais pensé, mais je n'avais pas remarqué qu'en A10 il y avait 01/03/2015 et non pas n'importe quelle date du mois de mars 2015.
Il suffisait que je clique dessus, mais je ne l'avais pas fait... Pas assez curieux, celui là.

@ plus

 

[job75]

Re : Somme du 1er du mois en cours jusqu'à la veille

Re CISCO,

Mettre en A10 un autre jour que le 1er du mois serait stupide.

A+

 

[Dugenou]

Re : Somme du 1er du mois en cours jusqu'à la veille

Bonjour Laurent, Job, Cisco
je ne comprends pas : chez moi ça marche aussi avec

=SOMME.SI.ENS($D:$D;$C:$C;">="&A10;$C:$C;"<"&AUJOURDHUI())

les 3 zones doivent avoir la même longueur, mais l'utilisation de colonnes complètes est à éviter pour minimiser les temps de calcul. Rien de t'empèche de définir une zone beaucoup plus longue que tes données genre :

=SOMME.SI.ENS($D1:$D10000;$C1:$C10000;">="&A10;$C1:$C10000;"<"&AUJOURDHUI())

c'est toujours mieux que le max...
Cordialement

 

[Laurentl]

Re : Somme du 1er du mois en cours jusqu'à la veille

Merci pour vos réponses.
Dugenou, effectivement ça fonctionne, j'ai du mal maniper hier.
Ok pour éviter les colonnes en entier.
Dans ta formule:
=SOMME.SI.ENS($D:$D;$C:$C;">="&A10;$C:$C;"<"&AUJOURDHUI())

pourquoi fixer les colonnes D et C?
Et je ne comprends pas la syntaxe ">="&A10 et "<"&AUJOURDHUI()
la première dit supérieur ou égal au mois de A10 et la 2eme strictement inférieur au jour d'aujourd'hui?
Alors pourquoi ça ne prend pas les valeurs d'avril, mai, juin etc?

 

[Dugenou]

Re : Somme du 1er du mois en cours jusqu'à la veille

Re,
les $ ne sont peut être pas nécessaires dans ton fichier, c'est plutôt une manie pour le cas où on recopierait à droite.
les critères sont en général des valeurs, si tu veux faire un critère > ou < tu dois concaténer le signe et la valeur : ce que je fais avec le &. Le résultat de la concaténation est donc >=A10 ; < aujourdhui(). Somme.si.ens te permet de faire la somme des données qui vérifient tous les critères en même temps. Donc on va totaliser tout ce qui est > ou = à A10 ET en même temps < aujourdhui().
Comme le fait justement remarquer Job75 : dans ta cellule A10 il n'est pas écrit Mars mais 1/03/2015 avec un format "mmm" : j'ai donc utilisé cette valeur comme borne inférieure. Si tu décidais d'écrire 5/03/2015 dans cette cellule : ça afficherai toujours mars, mais le résultat de la formule serait faux puisque on totaliserai les valeurs pour les dates >ou = au 5 mars. D’où sa remarque : "il serait stupide de mettre un autre jour".

Cordialement

 

[job75]

Re : Somme du 1er du mois en cours jusqu'à la veille

Bonjour le fil,

l'utilisation de colonnes complètes est à éviter pour minimiser les temps de calcul.

Limiter les plages ne change pratiquement rien à la durée d'exécution quand il s'agit du "UsedRange".

Testez (plusieurs fois) les cellules B10 et B11 du fichier joint avec ces 2 macros :

Sub Durée_B10()
Dim f$, t, i&
Application.ScreenUpdating = False
f = [B10].Formula
t = Timer
For i = 1 To 10000
  [B10].Formula = f
Next
MsgBox "Durée " & Format((Timer - t) / 10, "0.00") & " millième de seconde", , "B10"
End Sub

Sub Durée_B11()
Dim f$, t, i&
Application.ScreenUpdating = False
f = [B11].Formula
t = Timer
For i = 1 To 10000
  [B11].Formula = f
Next
MsgBox "Durée " & Format((Timer - t) / 10, "0.00") & " millième de seconde", , "B11"
End Sub

A+

 

[Dugenou]

Re : Somme du 1er du mois en cours jusqu'à la veille

Merci job,
quelle est donc la "bonne" raison pour ne pas utiliser des colonnes entières (mis à part la compatibilité) ?
Respectueusement

 

[job75]

Re : Somme du 1er du mois en cours jusqu'à la veille

Re Dugenou,

C'est avec SOMMEPROD ou des formules matricielles qu'il ne faut pas utiliser des colonnes entières.

SOMME.SI (ou NB.SI) ne sont pas des formules matricielles.

A+

 

[Laurentl]

Re : Somme du 1er du mois en cours jusqu'à la veille

Nickel!

Merci à tous, merci Dugenou, tout est très clair et résolu!

Bonne journée!