Addition données colonnes à critères multiples sommeprod?

Bonjour le forum,

Après beaucoup de tentatives et de recherches infructueuses sur la résolution de mon problème, je me tourne ici afin de trouver une solution.

Voici le problème :

J'ai un tableau avec des colonnes correspondant à différentes phase de résolution de problème (suivi de l'avancement). Ces colonnes sont renseignées avec des dates et un statut correspondant.

Je cherche un moyen d'automatiser le suivi (nombre de phase en retard sur nombre globale de phase).

Mon problème est que j'ai plusieurs critères à prendre en compte. Par exemple, pour les phases de Septembre à comptabiliser, je dois compter les phases dues en septembre (encore en cours ou non) + celles des mois précédents encore ouvertes + celles des mois précédents clôturées en septembre.

Pour compter celles en retard, je dois comptabiliser celle du mois en cours en retard + celles du mois en cours clôturées en retard + celles des mois précédents clôturées sur le mois en cours.

Je voulais utiliser "sommeprod" mais je sèche dans son utilisation.

Ci joint le fichier correspondant.

Merci pour votre aide.

"Au secours Obiwan Kenobi, vous êtes mon seul espoir"

Bonjour

Dans votre fichier pouvez-vous me dire ce que vous devez avoir pour :
1. capa due en septembre (ouvertes + clôturées). Ici j'ai 4 avec cette formule --> =NB.SI.ENS(H2:H13;">="&I31;I2:I13;"=*closed*")
2. capa des mois précédentes clôturées en septembre --> 0 ?
3. capa des mois précédents encore ouvertes. Ici j'ai 5 --> =NB.SI.ENS(B2:B13;"<"&I31;D2:D13;"=open") ou =NB.SI(D2:D13;"=open")

Quelle est la date prise en compte pour l'ouverture ? La colonne B ou la colonne H ?
si on prend le point 1, la date d'ouverture est mentionnée en colonne B mais il semble que vous utilisez la colonne H
si on prend le point 3, la date d'ouverture est en colonne B ou plus simle il suffit de compter le nombre d'Open trouvé en colonne D puisque vous y avez mis une formule.

Si vous pouviez dire ce que l'on doit trouver pour chacun des points cela aiderait

Crdlt

Bonjour Dan,

Avant tout merci pour la réponse :)

La date d'ouverture (colonne B) n'a pas d'impact sur le calcul souhaité. Seules les colonnes "Phase Investigation" (qui correspond à la date de clôture) et "prévision phase Investigation" (date prévisionnelle de clôture) comptent.

Afin d'avoir le nombre de CAPA à comptabiliser pour le mois de septembre (ou tout autres mois) je dois compter celle prévues dans le mois en cours (date de la colonne "prévision") + celle des mois précédents encore ouvertes + celles des mois précédents qui auraient été clôturées pendant le mois en cours.

Dans l'exemple donné dans le fichier joint j'ai bien 9 CAPA à trouver car : 8 ont une date prévue en septembre + 1 du mois d'août qui a été clôturée en septembre (et donc comptée comme dans celle due en septembre).

Pour calculer celles en retard, je dois compter celle du mois en cours qui sont dépassées (prévionnelle vs date du jour) + celle du mois en cours clôturées en retard (par rapport à la date prévisionnelle) + celle des mois précédent non clôturées + celles des mois précédents clôturées sur le mois en cours.

Dans l'exemple donné dans le fichier joint, j'ai bien 5 CAPA à trouver car : 3 prévue en septembre sont en retard + 1 de septembre clôturée en retard + 1 d'août clôturée en septembre.

Je ne sais pas si je suis bien clair dans mes explications, auquel cas je peux essayer de reformuler.

Merci à nouveau pour le temps passé sur mon problème :)

Afin d'avoir le nombre de CAPA à comptabiliser pour le mois de septembre (ou tout autres mois) je dois compter celle prévues dans le mois en cours (date de la colonne "prévision") + celle des mois précédents encore ouvertes + celles des mois précédents qui auraient été clôturées pendant le mois en cours.

Ok. Dans votre fichier il faut décomposer.

1. celle prévues dans le mois en cours (date de la colonne "prévision") --> 8 ? --> =NB.SI.ENS(H2:H13;">="&I31;H2:H13; "<="&FIN.MOIS(I31;0))
2. celle des mois précédents encore ouvertes --> 0 ? --> =NB.SI.ENS(H2:H13;"<"&I31;D2:D13;"=open")
3. celles des mois précédents qui auraient été clôturées pendant le mois en cours. --> 1 (valeur en I1 ?) --> =NB.SI.ENS(H2:H13;"<"&I31;D2:D13;"=*closed*";C2:C13;">="&I31)

L'addition des 3 donne un résultat de 9
Est-ce correct ?

Bonjour,

C'est tout à fait correct.

Cependant, est-il possible de "regrouper" toutes ces formules en une seule? Afin de ne pas alourdir le tableau de colonnes supplémentaires et de simplifier le suivi du calcul? Je pensais que la formule "sommeprod" pouvait faire cela, mais je n'ai pas réussi à l'implémenter.

Merci :)

re

Cependant, est-il possible de "regrouper" toutes ces formules en une seule? Afin de ne pas alourdir le tableau de colonnes

Le plus simple est de mettre les trois formules en y ajoutant le + entre chacune d'elle.

Sommeprod est une formule matricielle et cela peut vite faire râmer excel. Puis si vous utilisez une seule formule ce sera bien plus compliqué à comprendre.
Avec NB.SI.ENS vous voyez directement les trois critères pris en compte
Par contre si votre tableau grandit, je mettrais un nom sur chaque plage H2:H13, C2:C13, D2:D13). Du coup vous éviter de modifier les formules et cela sera encore plus clair à lire
Ou alors vous devrez insérer les nouvelles lignes dans le tableau pour qu'elles soient pris en compte dans la formule

Si ok, on passe au point suivant ?
NB : pour la partie "1 d'août clôturée en septembre". --> j'en vois 2 (une en H7 et l'autre en H13)

Re :)

M E R C I !! cela fonctionne parfaitement.

Pour le second point, je ne vois qu'une seule cellule remplissant le critère avant sept clôturée en sept, en H7. Celle en H13 est bien prévu pour août mais clôturée en août, certes en retard ceci dit

Pour le second point, je ne vois qu'une seule cellule remplissant le critère avant sept clôturée en sept, en H7. Celle en H13 est bien prévu pour août mais clôturée en août, certes en retard ceci dit

cela voudrait dire qu'il faut tenir compte de la cellule C13 et H13 dans la formule

Pas pour le mois de septembre. Car la ligne en question concerne le mois d'août. Comme elle a été clôturée en retard mais sur août, elle n'est plus à comptabiliser en septembre du coup

Ok donc voici les formules

1. capa due en septembre en retard --> =NB.SI.ENS(H2:H13;">="&I31;H2:H13; "<="&FIN.MOIS(I31;0);I2:I13;"=late")
2. capa due en septembre clôturées en retard --> =NB.SI.ENS(H2:H13;">="&I31;H2:H13; "<="&FIN.MOIS(I31;0);I2:I13;"=closed late*")
3. capa des mois précédents clôturées sur le mois de septembre --> =NB.SI.ENS(H2:H13;"<"&I31;C2:C13;">="&I31;I2:I13;"=closed late")

Après comme pour les précédentes, mettre un + entre chaque formule
Une fois terminé, voir si vous voulez créer des noms spécifiques pour les colonnes C, D, H et I pour faciliter la lecture

Ça marche du tonnerre

Merci énormément.

Pour les noms spécifiques, je veux bien oui

Pour les noms spécifiques, je veux bien oui

Est-ce possible de donner un nom différent pour le status en colonne I car vous avez 2 noms status

Oui c'est tout à fait possible :)

Bonjour

Voici ce qu'il faut modifier pour remplacer les plages par des noms

- Allez dans le gestionnaire de noms
- Pour chaque nom, cliquez sur Nouveau et ajoutez la formule ci-dessous dans la rubrique "référence à"

Une fois terminé dans chaque formule NB.SI.ENS que je vous ai donnée, remplacez
C2:C13 par -> Date_Investigation
D2:D13 par -> Status_Investigation
H2:H13 par -> Date_Prev_Investigation
I2:I13 par -> Status_Prev_investigation

Les noms sont juste une proposition. Vous pouvez les nommer autrement si vous voulez.

Pour plus de visibilité vous pourriez remplacer les titres en ligne 1 par les mêmes noms utilisés dans les formules sans mettre le souligné entre les mots
Exemple : en C1 --> Date Investigation, D1 Status investigation, ...

Remarque importante : Ce n'est pas le cas dans votre fichier posté mais si vous deviez des infos en colonne B autres que des dates veillez à toujours avoir une cellule vide entre la dernière date du tableau et l'info que vous ajoutez. Le mieux étant de laisser les cellules vides après la dernière date mentionnée
Exemple dans votre fichier : si vous devez ajouter "toto" en colonne B au lieu d'une date, ajoutez le en B15 afin de laisser B14 vide.

Si ok, pensez à cloturer le fil

Crdlt

Bonjour,

Merci énormément Dan pour ton aide précieuse et la résolution de mon problème Tout fonctionne parfaitement bien.

Je clos donc ce sujet.

Merci encore