sommeprod et #valeur!

[Goose]

Bjr,
j'ai un problème de #VALEUR! en feuille "Synthèse" dû à une formule en feuille "T2011":
=SI(J20+I20>MAINTENANT();"";SOMME(L20*5;M20*3;N20* 2;O20*3))
en remplaçant le "" par 0, je régle le problème, mais je fausse le classement !!
pouvez-vous m'aider ?
ci-joint le fichier
@+

 

[Tibo]

Re : sommeprod et #valeur!

Bonsoir,

Une proposition de correction en T9 :

=SI(SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40"))=0;
SOMMEPROD((INDIRECT("'"&$A9&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$V$14:$V$40");3)=V9);INDIRECT("'"&$A9&"'!$S$14:$S$40"));
SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40")))

J'ai mis cette partie du SOMMEPROD ;INDIRECT("'"&$A9&"'!$U$14:$U$40") à la fin en la faisant précéder d'un ;

Même principe pour U9

à adapter pour les autres lignes

@+

 

[Goose]

Re : sommeprod et #valeur!

Bsr et merci Tibo pour cette réponse rapide !!
autre question cependant, comment remplacer la valeur 0 par "" en T9 ?

 

[Tibo]

Re : sommeprod et #valeur!

Bonjour,

S'il s'agit d'éviter l'affichage du zéro, tu peux passer par exemple par un format personnalisé de ce type :

Standard;-Standard;

Ainsi, les zéros n'apparaissent plus

@+

 

[Goose]

Re : sommeprod et #valeur!

Merci Tibo mais malheureusement cette solution me crée un résultat en R9 et ne fera plus apparaitre le 0 en cas d'un score nul !!
il n'y a pas un moyen de donner un résultat "" dans la formule en T9 ?
@+

 

[Tibo]

Re : sommeprod et #valeur!

Bonjour,

On peut reprendre le test dans un autre test. Ca allonge la formule, mais ça devrait donner ce que tu veux :

=SI(SI(SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40"))=0;
SOMMEPROD((INDIRECT("'"&$A9&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$V$14:$V$40");3)=V9);INDIRECT("'"&$A9&"'!$S$14:$S$40"));
SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40")))=0;"";SI(SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40"))=0;
SOMMEPROD((INDIRECT("'"&$A9&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$V$14:$V$40");3)=V9);INDIRECT("'"&$A9&"'!$S$14:$S$40"));
SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40"))))

Il y a peut-être plus simple, mais ça demande à analyser la formule pour voir s'il n'y a pas moyen d'avoir une autre approche.

@+

 

[Goose]

Re : sommeprod et #valeur!

Effectivement Tibo, cela résoud le problème en R9, mais je n'arrive pas faire apparaitre le 0 quand un score par exemple est de 0-15 ou 42-0 !! peut être en intervenant sur la formule en feuille "T2010":
=SI(J20+I20>MAINTENANT();"";SOMME(L20*5;M20*3;N20* 2;O20*3))
????
@+

 

[Goose]

Re : sommeprod et #valeur!

bon je crois avoir trouvé...

En T9:
=SI(OU(SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$J$14:$J$40"))>AUJOURDHUI();SOMMEPROD((INDIRECT("'"&$A9&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$V$14:$V$40");3)=V9);INDIRECT("'"&$A9&"'!$J$14:$J$40"))>AUJOURDHUI());"";SI(SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40"))=0;
SOMMEPROD((INDIRECT("'"&$A9&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$V$14:$V$40");3)=V9);INDIRECT("'"&$A9&"'!$S$14:$S$40"));
SOMMEPROD((INDIRECT("'"&$A9&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A9&"'!$P$14:$P$40");3)=V9);INDIRECT("'"&$A9&"'!$U$14:$U$40"))))

Merci Tibo de m'avoir éclairé !!
@+

 

[Goose]

Re : sommeprod et #valeur!

Re,
Encore un petit souci de #REF! en T14 par rapport une feuille non créer !!
peux tu voir ça ??
merci d'avance
@+

 

[Tibo]

Re : sommeprod et #valeur!

re,

On peut tester si la feuille existe avec INDIRECT :

Pour T14, ça donne ceci :

=SI(ESTNUM(INDIRECT("'"&$A14&"'!A1"));SI(OU(SOMMEPROD((INDIRECT("'"&$A14&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A14&"'!$P$14:$P$40");3)=V14);INDIRECT("'"&$A14&"'!$J$14:$J$40"))>MAINTENANT();SOMMEPROD((INDIRECT("'"&$A14&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A14&"'!$V$14:$V$40");3)=V14);INDIRECT("'"&$A14&"'!$J$14:$J$40"))>MAINTENANT());"";SI(SOMMEPROD((INDIRECT("'"&$A14&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A14&"'!$P$14:$P$40");3)=V14);INDIRECT("'"&$A14&"'!$U$14:$U$40"))=0;
SOMMEPROD((INDIRECT("'"&$A14&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A14&"'!$V$14:$V$40");3)=V14);INDIRECT("'"&$A14&"'!$S$14:$S$40"));
SOMMEPROD((INDIRECT("'"&$A14&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A14&"'!$P$14:$P$40");3)=V14);INDIRECT("'"&$A14&"'!$U$14:$U$40"))));"")

Principe : avec SI(ESTNUM(INDIRECT("'"&$A14&"'!A1")), on teste si la cellule A1 de la feuille souhaitée retourne une valeur numérique. Si oui, on applique la formule, si non, on retourne ""

@+

 

[Goose]

Re : sommeprod et #valeur!

Bjr Tibo, merci, mais ta solution ne me donne plus aucun résultats !!
que faire ??
@+

 

[Tibo]

Re : sommeprod et #valeur!

Bonjour,

Ca me semble normal dès lors que la feuille T2012 n'existe pas.

Quel message attends-tu si la feuille n'existe pas ?

Tu peux l'indiquer à la fin de la formule en adaptant le ""

@+

 

[Goose]

Re : sommeprod et #valeur!

je me suis mal exprimé, je n'ai plus de résultats même sur les feuilles existantes !!

 

[Tibo]

Re : sommeprod et #valeur!

re,

Toute petite correction en T4 (il manquait un *1) :

=SI(ESTNUM(INDIRECT("'"&$A4&"'!A1")*1);SI(OU(SOMMEPROD((INDIRECT("'"&$A4&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A4&"'!$P$14:$P$40");3)=V4);INDIRECT("'"&$A4&"'!$J$14:$J$40"))>MAINTENANT();SOMMEPROD((INDIRECT("'"&$A4&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A4&"'!$V$14:$V$40");3)=V4);INDIRECT("'"&$A4&"'!$J$14:$J$40"))>MAINTENANT());"";SI(SOMMEPROD((INDIRECT("'"&$A4&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A4&"'!$P$14:$P$40");3)=V4);INDIRECT("'"&$A4&"'!$U$14:$U$40"))=0;
SOMMEPROD((INDIRECT("'"&$A4&"'!$P$14:$P$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A4&"'!$V$14:$V$40");3)=V4);INDIRECT("'"&$A4&"'!$S$14:$S$40"));
SOMMEPROD((INDIRECT("'"&$A4&"'!$V$14:$V$40")=$P$2)*(GAUCHE(INDIRECT("'"&$A4&"'!$P$14:$P$40");3)=V4);INDIRECT("'"&$A4&"'!$U$14:$U$40"))));"")

On suppose que la cellule A1 de chaque feuille est toujors vide.

@+

 

[Goose]

Re : sommeprod et #valeur!

Nickel, merci pour tous !!
@+