XL 2010 SOMMEPROD avec 3 conditions de dates

[Nazgul]

Bonjour,

Dans le tableau de données nous avons une A colonne "Projet", une colonne B "Date prévisionnelle" et une colonne C "Date réelle". Ce sont des dates de passage en comité de dépenses.

Je cherche à comptabiliser par mois :
- Le nombre de projet passé en comités prévus par mois (colonne G)
- Le nombre de projets passé en comités dans les délais, à savoir dans le mois de la date prévue (colonne H):
- L'important est de bien comptabiliser les bons projets et non pas comptabiliser indifféremment les projets passés en comité tel ou tel mois.
- Le nombre de projets passé en comité avec au moins un mois de retard (colonne I):
- Exemple: En Avril 2017 (C9) passe en comité un projet qui a quasiment 2 mois de retard.

Je sèche totalement après plusieurs tests infructueux...

TEST Comités prévus
En colonne G le premier test fonctionne (G3) mais pas très pratique, ne permet pas de faire glisser la formule, je dois taper les intervalles à la main pour chaque mois :

=SOMMEPROD((B3:B9>=DATE(2016;12;1))*((B3:B9<=DATE(2016;12;31))))

Mon test pour automatiser est un échec en G4 (sur le mois de février 2017 F4):

=SOMMEPROD(($B$3:$B$9=MOIS(F4))*(($B$3:$B$9=ANNEE(F4))))

TEST Comités passés dans les temps
Ce qui m'a donné le plus de mal et toujours aucun résultat...

=SOMMEPROD((MOIS($B$3:$B$9)=MOIS($C$3:$C$9));(ANNEE($B$3:$B$9)=ANNEE($C$3:$C$9));(MOIS($B$3:$B$9)=MOIS($F3));(ANNEE($B$3:$B$9)=ANNEE($F3))*(C3:C9))

TEST Comités passés avec au moins un mois de retard
Butant sur les parties précédentes je ne m'y suis encore trop frotté

Voilà mon problème, j'espère avoir été clair... Je ne suis pas certain d'avoir totalement saisi comment fonctionne la fonction SOMMEPROD

Merci d'avance pour votre aide !

 

[kan3pz]

Bonjour Nazgul,
un essai en PJ, je n'ai pas tout vérifié...
A noter, pour les colonnes H & I formule matricielle à valider par CTRL + MAJ +ENTREE, le contrôle se fait par rapport au mois de la date prévisionnelle (peut être à modifier pour prendre la date réelle).

A+

 

[Nazgul]

Bonjour kan3pz,

Merci beaucoup pour ta réponse et ta rapidité.

La formule de la colonne G fonctionne nickel !

Je pense que pour les colonnes suivantes je n'ai pas été très clair...
Je ne comprends pas bien la formule. Il s'agissait de savoir sur les opérations passées en comité dans un mois, lesquelles étaient des opérations dont le comité était aussi prévu dans le même mois. Je crois avoir trouvé la solution, du moins pour ça.

Je ne comprends pas bien ta formule en L par contre...

En tout cas merci pour ton aide.

 

[Nazgul]

Voici le fichier

 

[kan3pz]

Bonjour Nazgul,
Voici le fichier modifié avec les explications de mes formules, le test vérifie si il y a plus d'1 mois de délai entre la date prév et la date réelle ce qui est différent du test si le projet est réalisé le même mois...