Problème Somme prod avec critères

[Beast]

Bonjour,

Je fais appel au forum car je suis bloqué sur Excel.

En effet, j’essaie de réaliser une moyenne pondérée en utilisant la fonction somme prod que voici :

=SOMMEPROD((C7:C89="Fourniture"))*((D789="300"))*((E7:E89);(F7:F89))/(SOMME(E7:E89))

Pour réaliser la moyenne pondérée, je tiens compte de deux différents critères : Fourniture pour la colonne C et 300 pour la colonne D. Même en enlevant les guillemets de 300, cela ne change rien.

Le but étant d'automatiser le mieux le tableau afin que tous les calculs soient automatiques selon divers critères.

Ensuite, je réalise donc une division afin d'avoir la moyenne pondérée en tenant compte des critères.

Seulement, Excel affiche #VALEUR! donc je me demande où ça plante...

De plus, pour le moment, je connais la dernière ligne de mon tableau qui est 89, mais au fur et à mesure, de nouvelles données vont être entrées et du coup je souhaiterais pouvoir déterminer la dernière ligne automatiquement.

On m'a conseillé d'utiliser la fonction NB VAL mais ça rend la formule encore plus compliquée et je ne m'en sors pas :

=SOMMEPROD(65000-NB.VIDE($C$7:$C$65000="Fourniture"))*(65000-NB.VIDE($D$7:$D$65000=300))*(65000-NB.VIDE($E$7:$E$65000))*(65000-NB.VIDE($F$7:$F$65000))/(65000-NB.VIDE($E$7:$E$65000)

Donc voilà, je fais appel au forum si jamais vous avez une idée.

Et j'aimerais ne pas utiliser de macro VBA car je veux simplifier au plus la tâche des futurs utilisateurs.

J'espère avoir été assez clair et je vous remercie !

 

[CISCO]

Re : Problème Somme prod avec critères

Bonjour

Il me semble qu'il y a des parenthèses mal placées dans ta première proposition...

Ceci dit, cela serait tellement plus simple si tu mettais un petit fichier en pièce jointe, avec juste une dizaine de lignes.

@ plus

 

[Lolote83]

Re : Problème Somme prod avec critères

Salut BEAST, CISCO,
Juste de temps d'actualiser ma page Web et CISCO est passé par là.
Donc je reprends son message concernant la possibilité d'avoir un fichier.
Pour la question de la dernière ligne.
Exemple en A1 tu donnes le nombre de valeur de la colonne B avec pour formule =NBVAL(B:B)
Ensuite tu reprends ta formule SOMMEPROD en faisant référence à la cellule A1 grâce à la fonction INDIRECT ce qui pourrait donner
=SOMMEPROD((indirect("C7:C" & A1)="Fourniture"))*((indirect("D7" & A1)="300"))*((indirect("E7:E" & A1));(indirect("F7:F" & A1)))/(SOMME(indirect("E7:E" & A1)))
Formule non testée, écrite ici en direct
@+ Lolote83

 

[Beast]

Re : Problème Somme prod avec critères

Bonjour à vous,

J'aimerais joindre le fichier mais c'est un document de travail, donc je ne pense pas pouvoir le partagé comme ça malheureusement... Je mets un exemple en dessous du tableau avec les informations changées pour que vous ayez une idée :
B C D E F
SITE TYPE DN QUANTITE PRIX UNITAIRE
AAAA Fourniture 300 813 211,77
AAAA Fourniture 300 52 136,52
AAAA Fourniture 300 125 47,12
AAAA Fourniture 250 40 58,61
AAAA Fourniture 250 7 2,74
AAAA Fourniture 200 141 15,36

J'espère que ça vous éclaire un peu sur le sujet

Encore merci !

 

[Lolote83]

Re : Problème Somme prod avec critères

Salut BEAST, CISCO,
Ton fichier en retour en espérant que cela corresponde à ton souhait
Effectivement avec un exemple ça va beaucoup mieux.
@+ Lolote83

 

[Beast]

Re : Problème Somme prod avec critères

Franchement, mille mercis !

Je me suis inscrit un peu en désespoir ce midi, et vous me répondez super vite et avec le document et tout !

Franchement, vous êtes au top !

Je vous remercie tous les deux pour votre aide !

 

[CISCO]

Re : Problème Somme prod avec critères

Bonjour

Bonjour à vous,

J'aimerais joindre le fichier mais c'est un document de travail....

Encore merci !

Même dans ce cas, tu fais un double de ton fichier, tu supprimes les logos, les feuilles inutiles, presque toutes les lignes, sauf les 10 ou 15 premières, tu effaces les données des colonnes inutiles, tu remplaces les noms par des noms bidons... Après, on est tous tellement plus efficaces...

@ plus

 

[CISCO]

Re : Problème Somme prod avec critères

RE

Une autre possibilité en pièce jointe. Cf. les noms définis dans le gestionnaire de noms. Il y a d'autres possibilités en utilisant un EQUIV(9^9;....;1) à la place du NBVAL.

@ plus

 

[Beast]

Re : Problème Somme prod avec critères

Nonjour,

Je vous remercie pour les conseils !

Je reviens à la charge car j'ai un petit problème à nouveau, je souhaiterais récupérer les cellules provenant d'un autre document (nommé BVD2.xlsm), et lorsque je rentre dans la fonction le nom du fichier, Excel ne reconnaît plus les cellules...

Ca ressemble à ça :

=SOMMEPROD(INDIRECT([BDV2.xlsm]Feuil1!"C7:C" & $A$1)="Fourniture")*(INDIRECT([BDV2.xlsm]Feuil1!$D7:$D & $A$1)=300)*(INDIRECT([BDV2.xlsm]Feuil1!$E7:$E & $A$1))*(INDIRECT([BDV2.xlsm]Feuil1!$F7:$F & $A$1))/;(SOMME(INDIRECT([BDV2.xlsm]Feuil1!$E7:$E & $A$1)))

J'ai aussi essayé en rentrant [BDV2.xlsm]Feuil1! pour chaque cellule (ce qui donnait une fonction vraiment longue) mais Excel n'en veut pas non plus...

Auriez-vous une petite idée ?

Merci !

 

[Lolote83]

Re : Problème Somme prod avec critères

Re salut,
Je pense qu'il faut mettre entre guillemet tout le nom du fichier et la plage ce qui devrait donner pour le début de la formule

=SOMMEPROD(INDIRECT("[BDV2.xlsm]Feuil1!C7:C" & $A$1)="Fourniture") etc etc

Fais donc un essai comme ça.
@+ Lolote83

 

[Beast]

Re : Problème Somme prod avec critères

Bonjour Lolote83,

Excel renvoie vers #REF, j'ai mis un screen shot du problème en pièce jointe.
J'ai retiré les $ pour voir si ça pouvait résoudre le problème mais toujours pas...

Merci !

 

[Lolote83]

Re : Problème Somme prod avec critères

Salut,
A priori, dans ta copie d'écran il manque un & $A$1 après feuil1$C7:C" &$A$1 ))="Fourniture" etc etc etc
Sinon, je ne vois pas
@+ Lolote83

 

[Beast]

Re : Problème Somme prod avec critères

Je crois que l'aide Excel l'a supprimé car c'est affiché dans ma fonction, et décidément il en veut pas...

Merci quand même et bon week end

 

[CISCO]

Re : Problème Somme prod avec critères

Bonsoir

Chez moi, avec Excel 2010, cela fonctionne avec

=SOMMEPROD((INDIRECT("[essaivérification.xlsx]Feuil1!$C$7:$C"&A1)="Fourniture")*...

les informations "Fourniture" se trouvant en C7:C12 dans un autre fichier essaivérification ouvert, et A1 contenant la valeur 12.

@ plus

 

[ODVJ]

Re : Problème Somme prod avec critères

Bonsoir à tous,

D'après l'image de l'évaluation de formule qui anticipe le #Ref, il semblerait que A1 contienne la chaîne vide.
Excel n'a pas supprimé &$A$1. Il l'a simplement évalué.

Au fait, BDV2.xlsm est bien ouvert quand on fait appel à lui?
Si non, INDIRECT() va faire la tête!
Et il faudra alors passer par INDIRECT.EXT() de l'addin MoreFunc de Laurent Longre.

cordialement