SOMMEPROD avec conditions MIN MAX par ligne
Bonjour,
J'ai dans un onglet (RH) un tableau de plusieurs lignes (plusieurs salariés, ligne 5 à 11) avec en colonne :
- date d'entrée (B)
- date de sortie (C)
- salaire chargé annuel (G)
J'ai dans un autre onglet (PLAN DE TRESORERIE) les mois en colonnes (le 1er mois étant en C2), et différents postes de dépenses en lignes, dont les RH bien évidemment, à la ligne 26.
Je souhaiterais par une formule à étirer sur la ligne 26 sur chaque colonne, pouvoir obtenir le montant mensuel des RH.
J'ai naturellement pensé à commencer par un :
SOMMEPROD(RH!$G$5:$G$11)Néanmoins, la somme n'a de sens que lorsque le mois en question (en ligne 2, à partir de C2) est inclus entre la date d'entrée (colonne B de l'onglet RH) et la date de sortie (colonne C de l'onglet RH).
Par ailleurs, si la date d'entrée et/ou de sortie tombe sur le mois en cours, il faut proratiser.
J'ai donc d'abord essayé de valider le fait que le mois en cours est bien compris (au moins partiellement) entre la date d'entrée et de sortie du salarié avec une condition supplémentaire dans le sommeprod : en gros on vérifie que la date de sortie est supérieure ou égale au début de mois ET que la date d'entrée est inférieure ou égale à la fin du mois, donc l'intersection n'est pas nulle..
SOMMEPROD((RH!$C$5:$C$11>='PLAN DE TRESORERIE'!C$2)*(RH!$B$5:$B$11<=FIN.MOIS('PLAN DE TRESORERIE'!C$2;0))*RH!$G$5:$G$11)Avec cette formule, j'obtiens les montants totaux annuels, mais il me faut les diviser pour chaque ligne par le nombre de jours travaillés sur l'année (C-B) et les multiplier par le nombre de jours travaillés dans le mois, sachant que le nombre de jours travaillés dans le mois est :
MIN(RH!$C$5:$C$11;FIN.MOIS('PLAN DE TRESORERIE'!C$2;0))-MAX(RH!$B$5:$B$11;'PLAN DE TRESORERIE'!C$2)Ce qui me donne au total :
SOMMEPROD(RH!$G$5:$G$11*(RH!$C$5:$C$11>='PLAN DE TRESORERIE'!C$2)*(RH!$B$5:$B$11<=FIN.MOIS('PLAN DE TRESORERIE'!C$2;0))*((MIN(RH!$C$5:$C$11;FIN.MOIS('PLAN DE TRESORERIE'!C$2;0))-MAX(RH!$B$5:$B$11;'PLAN DE TRESORERIE'!C$2))/(RH!$C$5:$C$11-RH!$B$5:$B$11)))Le problème est que le MIN et le MAX s'appliquent sur la plage, et ne sont pas résolus à chaque ligne au cours du sommeprod ce qui était le comportement que j'attendais..
Désolé pour l'explication un peu longue mais c'est pour être clair sur ce que je souhaitais faire..
Avez-vous une idée de modification de cette formule ou d'utilisation d'une autre formule pour arriver au résultat escompté, à savoir faire un sommeprod avec des calculs résolus à chaque ligne ?
Merci d'avance...