Controler les niveaux et nombre de diplomes

Bonjour,

Pour contrôler la validité de l'encadrement d'équipes, je dois déterminer pour chaque club s'ils disposent de suffisamment d'entraineurs (en nombre et en niveau de diplôme).


Pour cela, j'ai associé aux diplômes un coefficient (onglet donnees) ; un diplôme servant à couvrir une équipe d'un niveau donné. Je calcule alors le "score" des diplômes que je compare successivement au score associé au diplôme requis (onglet devoir) -> si le score du club est supérieur, c'est bon (affichage "Oui").

Pour prendre en compte les entraineurs diplômés qui encadrent déjà une équipe, je soustrais au score total du club le score déjà utilisé à l'aide d'une fonction SOMME PROD :

=SI(INDEX(entraineur!H:H;EQUIV(devoir!A3;entraineur!A:A;0))-SOMMEPROD((E3="Oui")*D3)>=D4;"Oui";"Non")

La méthode fonctionne plutôt bien dans la grande majorité des cas. Le problème vient quand un club dispose d'un nombre plus important que nécessaire de "haut" diplôme : dans l'exemple du club A, le score total est de 101000001000. Une fois l'entraineur de Ligue 1 enlevé, il reste donc 101000001000-1e11 = 1000001000 ; une fois l'entraineur de la N2 enlevé, il reste donc 1000001000 - 1e7 = 990001000. Or ce total est supérieur au score nécessaire pour l'équipe de N3 alors que le club ne dispose pas d'un 3ème entraineur avec le diplôme suffisant (le 3ème n'étant que de niveau DR2) ce qui fait qu'au global, l'équipe 3 de N3 parait couverte "calculatoirement parlant" alors qu'en réalité ce n'est pas le cas.

Je n'arrive pas à trouver de moyen pour arriver à mes fins (peut-être d'ailleurs que la méthode par "score" n'est pas adaptée ...).

Si quelqu'un pouvait m'aider à me dépanner (sans macro), ce serait super.

Le lien de mon fichier : https://www.cjoint.com/c/LHlqNKIEEvO
Windows / Firefox 103.0