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 !

 

[Beast]

Re : Problème Somme prod avec critères

Bonjour ODVJ & Cisco,

Les deux documents sont toujours ouverts (et l'ont toujours été !).

J'ai essayé en mettant la valeur A1 dans les deux documents et ça m'indique toujours #REF!

Qvez-vous d'autres suggestions ?

Merci !

 

[CISCO]

Re : Problème Somme prod avec critères

Bonjour à tous, bonjour ODJV, Beast.

Et qu'est-ce que cela donne avec le fichier de mon post #14, avec un autre fichier essaivérification.xlsx contenant les informations "Fourniture" en colonne C, ouvert ?

@ plus

 

[Beast]

Re : Problème Somme prod avec critères

Bonjour CISCO,

J'ai rentré dans le document la formule suivante (en nommant les lignes) et en ayant les documents ouverts :

=SOMMEPROD((INDIRECT("[BDV2.xlsm]Feuil1!$C$7:$C"&[BDV2.xlsm]Feuil1!$A$1)="Fourniture")*(DN=300)*(QUANTITÉ)*(PRIXUNITAIRE))/SOMME(QUANTITÉ)

Excel répond par #NOM!

Je mets un screen shot du "étapes par étapes" de la cellule.

 

[CISCO]

Re : Problème Somme prod avec critères

Bonjour

Est-ce que tu as défini les noms DN, QUANTITE, PRIX_UNITAIRE dans le gestionnaire de noms, comme je l'ai fait dans mes deux derniers fichiers (passer par l'onglet "Formules")?

@ plus

 

[Beast]

Re : Problème Somme prod avec critères

Oui, je les ai nommé (cf le doc) et j'ai veillé à ce que ce soit les mêmes noms dans le gestionnaire de nom/la formule... C'est ensuite qu'Excel affiche #NOM!

 

[CISCO]

Re : Problème Somme prod avec critères

Bonsoir

Est-ce que tu pourrais mettre en pièce jointe tes deux fichiers, en n'y laissant que quelques lignes ?

@ plus

P.S : J'ai rajouté mon deuxième fichier sur le post #14.

 

[Beast]

Re : Problème Somme prod avec critères

Bonjour CISCO,

En pièce jointe les deux documents en question,

Grand merci !

 

[CISCO]

Re : Problème Somme prod avec critères

Bonjour

Cf. une possibilité en pièce jointe, avec dans en Feuil1!A1 du fichier BDV2 la formule =NBVAL(D)+6
A toi de vérifier la formule.

@ plus

 

[CISCO]

Re : Problème Somme prod avec critères

RE

Quand tu écris, comme dans ton post #14, dans le fichier Estimation
=SOMMEPROD((INDIRECT("[BDV2.xlsm]Feuil1!$C$7:$C"&[BDV2.xlsm]Feuil1!$A$1)="Fourniture")*(DN=300)*(QUANTITÉ)*(PR IXUNITAIRE))/SOMME(QUANTITÉ)
Excel va chercher dans ce même fichier Estimation les données des plages DN, QUANTITE, PRIXUNITAIRE (Nul part tu lui as dit que ces données sont dans BDV2). Cela explique pourquoi il te renvoyait le message d'erreur #NOM.


Il faudrait pouvoir faire avec un nom faisant appel à une autre feuille. On peut aussi faire avec des INDIRECT, comme j'ai fait dans le post ci-dessus.

@ plus

 

[Beast]

Re : Problème Somme prod avec critères

C'est bel et bien ça, merci beaucoup CISCO (et les autres !) d'avoir résolu le problème !

 

[CISCO]

Re : Problème Somme prod avec critères

Bonjour

On peut aussi faire avec des noms faisant appel au contenu de l'autre fichier. Cf. dans le gestionnaire de noms de Estimation version. A toi de vérifier.

@ plus

P.S : Cela semble même fonctionner avec le second fichier non ouvert.

 

[CISCO]

Re : Problème Somme prod avec critères

Bonjour

Et une possibilité avec des plages dynamiques, c-à-d dont la longueur s'adapte automatiquement à la hauteur du second fichier. On peut aussi faire sans aucune valeur en Feuil1!A1 dans le second fichier, en utilisant pour définir les divers noms des formules en DECALER([BDV2.xlsm]Feuil1!$D$7;;;EQUIV("zzz";....;1)) pour les plages contenant du texte, et DECALER([BDV2.xlsm]Feuil1!$D$7;;;EQUIV(9^9;....;1)) pour celles contenant des nombres.

@ plus