XL 2013 SOMMEPROD ET CRITERE TEXTE

[Fabiennesd]

Bonjour, j'ai une formule sommeprod à trois critères qui fonctionne plutôt bien
=SOMMEPROD(((('fichier plat BSP'!$F$7:$F$399999="AB")+('fichier plat BSP'!$F$7:$F$399999="AA")+('fichier plat BSP'!$F$7:$F$399999="AB")+('fichier plat BSP'!$F$7:$F$399999="AC")+('fichier plat BSP'!$F$7:$F$399999="AD")+('fichier plat BSP'!$F$7:$F$399999="AE")+('fichier plat BSP'!$F$7:$F$399999="AF")+('fichier plat BSP'!$F$7:$F$399999="AG")+('fichier plat BSP'!$F$7:$F$399999="AH")+('fichier plat BSP'!$F$7:$F$399999="AI")+('fichier plat BSP'!$F$7:$F$399999="AJ")+('fichier plat BSP'!$F$7:$F$399999="AK")+('fichier plat BSP'!$F$7:$F$399999="AL")+('fichier plat BSP'!$F$7:$F$399999="AM")+('fichier plat BSP'!$F$7:$F$399999="AN")+('fichier plat BSP'!$F$7:$F$399999="AO")+('fichier plat BSP'!$F$7:$F$399999="AP")+('fichier plat BSP'!$F$7:$F$399999="AQ")+('fichier plat BSP'!$F$7:$F$399999="AR")+('fichier plat BSP'!$F$7:$F$399999="AS")+('fichier plat BSP'!$F$7:$F$399999="AT")+('fichier plat BSP'!$F$7:$F$399999="AU")+('fichier plat BSP'!$F$7:$F$399999="AV")+('fichier plat BSP'!$F$7:$F$399999="AW")+('fichier plat BSP'!$F$7:$F$399999="AX")+('fichier plat BSP'!$F$7:$F$399999="AY")+('fichier plat BSP'!$F$7:$F$399999="AZ")+('fichier plat BSP'!$F$7:$F$399999="WB"))*(('fichier plat BSP'!$A$7:$A$399999=A3)*('fichier plat BSP'!$O$7:$O$399999))))
en bleu la liste des portefeuilles de mes commerciaux
en vert en A3 le mois à prendre en compte
en rouge les montants à aditionner
Le souci que j'ai : c'est que les portefeuilles varient tout le temps et jusqu'à présent je modifiais la formule manuellement mais ce n'est pas franchement pratique..
Puis j'ai essayé de créer une plage nommée pour la liste de mes portefeuilles mais ça ne fonctionne pas non plus .. (normal d'après ce que j'ai lu dans les forums..)
j'ai aussi pensé à la BDSOMME mais en fait j'ai une centaine de formules différentes de ce type à créer qui mélangent une bonne quinzaine d'objets différents (portefeuilles, magasins, villes, etc..) et je ne peux donc créer une BDSOMME pour chaque formule..
Avez vous une idée ou un conseil à m'apporter?
par avance merci à tous
cdlt
Fabienne

 

[JHA]

Bonjour à tous,

Peux-tu mettre un fichier exemple.
Autrement, as-tu essayé de mettre les critères à part et de nommer la plage.
Il te suffirait de modifier les critères.
=SOMMEPROD(((('fichier plat BSP'!$F$7:$F$399999=plage nommée)*('fichier plat BSP'!$O$7:$O$399999))

JHA

 

[danielco]

Bonjour,

Regarde cette exemple (formule matricielle, valider avec Ctrl+Maj+Entrée :

=SOMME((F1:F5=TRANSPOSE(H1:H3))*O1:O5)

 

[Fabiennesd]

merci JHA, oui j'ai fait tout un onglet avec mes plages de données, les ai nommées et j'ai voulu intégrer la plage nommée dans la formule mais ça n'a pas fonctionné, je pense parce que la somme prod ne comprend pas que chaque cellule de la plage nommée est un critère à part entière (pardon j'explique de manière bien peu scientifique..
je prépare un fichier ce soir et je vous le joins dès que possible.

merci également Danielco, je ne connais pas du tout la formule que tu me montres. Je vais la tester. Mais j'ai deux petites questions :
1/ que signifie le *10:05 en fin de formule??
2 / si je dois rajouter un ou deux critères supplémentaires comme "mois = janvier" et "magasin = Paris" + magasin = Bordeaux" est ce possible?

bonne soirée à tous

 

[JHA]

Bonjour à tous,

Ce n'est pas "*10:05 " mais la plage "O1:O5"

JHA

 

[danielco]

merci JHA, oui j'ai fait tout un onglet avec mes plages de données, les ai nommées et j'ai voulu intégrer la plage nommée dans la formule mais ça n'a pas fonctionné, je pense parce que la somme prod ne comprend pas que chaque cellule de la plage nommée est un critère à part entière (pardon j'explique de manière bien peu scientifique..
je prépare un fichier ce soir et je vous le joins dès que possible.

merci également Danielco, je ne connais pas du tout la formule que tu me montres. Je vais la tester. Mais j'ai deux petites questions :
1/ que signifie le *10:05 en fin de formule??
2 / si je dois rajouter un ou deux critères supplémentaires comme "mois = janvier" et "magasin = Paris" + magasin = Bordeaux" est ce possible?

bonne soirée à tous

1. Ce n'est pas "*10:05" mais la plage de cellules "O1:O5" qui correspond à "'fichier plat BSP'!$O$7:$O$399999" de ta formule.
2. Oui, mais la formule va être un peu différente :

=SOMME((F1:F5=TRANSPOSE(H1:H3))*O1:O5*(P1:P5="janvier")*((Q1:Q5=D1)+(Q1:Q5=D2)))

Daniel

 

[Fabiennesd]

bonjour à tous,
comme promis voici un petit exemple de fichier avec la formule souhaitée..
Je teste vos propositions dans la journée et reviens vers vous
encore merci et à très vite
Fabienne

 

[danielco]

Pas beaucoup mieux :

=SOMME((A2:A18=TRANSPOSE(M1:M3))*((B2:B18="nantes")+(B2:B18="paris"))*((C2:C18="numéro 4")+(C2:C18="numéro 2"))*((D2:D18="janvier")+(D2:D18="fevrier"))*(E2:E18))

Daniel

 

[danielco]

Ou (matricielle, à valider avec Ctrl+Maj+Entrée) :

=SOMME(ESTNUM(EQUIV(A2:A18;M1:M3;0))*ESTNUM(EQUIV(C2:C18;N1:N2;0))*ESTNUM(EQUIV(D2:D18;O1:O2;0))*E2:E18)

Avec :

Daniel

 

[Fabiennesd]

merci beaucoup Daniel, je viens de tester et ça a l'air de bien fonctionner, en particulier la formule avec les fonctions ESTNUM et EQUIV..
mais j'avoue que la fonction TRANSPOSE m'intéresse aussi! vos propositions m'ouvrent de belles perspectives
il faut que je teste "grandeur nature " mais je n'aurai pas accès à mon fichier avant la semaine prochaine. Je vous tiens au courant.
En attendant je passe cette discussion en "résolu
Un grand merci pour votre aide aussi rapide qu'efficace!
bien cordialement
Fabienne