Formule SOMMEPROD

Bonjour les experts!

J'avais déjà écrit un sujet la dessus que j'avais fermé car tout semblait bien fonctionner sur ma formule (merci encore à ceux qui m'ont aidé!) mais comme je n'avais fait que des tests sommaire tout semblait bien aller mais c'est en testant plus solidement la formule je me suis rendu compte qu'il manquait des choses que je n'avais pas pus préciser la 1er fois puisque je l'ignorais moi-même!

Alors voila...j'ai la formule suivante :

=SOMMEPROD(($C$10:$C$20="Peintre")*($D$10:$D$20="Rouleau")*($E$10:$E$20="Soir")*(GAUCHE(F10:F20,1)="V")*(--DROITE(F10:F20,2)))

la formule va très bien lorsque les conditions sont remplis et idéale mais j'aimerais qu'elle puisse faire les choses suivantes :

A-Accepter jusqu'à des additions ayant 4 valeurs après la lettre. Présentement elle n'en considère que 2 et si jamais j'ai une case qui n'a qu'un chiffre après la lettre j'ai une erreur VALEUR. J'aimerais que la formule puisse additionner V10+V5.6+V11.8 sans me retrouver avec des erreurs VALEUR

B-Présentement si j'ai une case vide j'ai une erreur VALEUR. Il se peut que dans mon fichier j'ai parfois des cases vides mais j'aimerais que la formule les ignore tout simplement et saute la case.

C-Non nécessaire mais serait un ''nice to have''. Ne pas bloquer la formule si je n'ai que des lettres non suivi de chiffres. Ex si j'ai un code CF dans une case la formule bloque parce qu'elle ne voit pas de chiffres...Est-ce qu'on peut contouner ca?

Je joins un fichier et mes remerciements encore une fois pour votre aide!

Bonjour,

Ajoute-toi une colonne pour avoir la lettre et tu résouds (presque) tous tes pb d'un coup. En H10 :

=SI(ET(NBVAL(C10:G10)=5;NBCAR(G10)>1);GAUCHE(G10;1);"")

En G4 :

=SOMMEPROD(($C$10:$C$20="Boulanger")*($D$10:$D$20="Couteau")*($E$10:$E$20="Jour")*(H10:H20="J")*((SI(NBCAR(G10:G20)=1;0;--STXT(G10:G20;2;10)))))

à valider en matriciel

Etsi tu saisis (ou réparti) lettre et nombre dans 2 colonne pour avoir une bdd digne de ce nom tu peux tout avoir avec un simple TCD sans saisir une seule formule.

eric

edit : ajout fichier exemple tcd

Eric, c'est un fichier que plusieurs remplissent dont les résultats vont servir à alimenter automatiquement un autre fichier. Je dois faire sortir sur plusieurs colonnes (1 an donc 365 colonnes) toutes occurrences d'un code V suivi de chiffres selon certains critères. J'aurais ensuite appliqué cette formule sur toutes les colonnes en modifiant les critères au besoin.

C'est la raison pour laquelle je veux éviter l'ajout de colonne qui peuvent être éviter parce que ce fichier est utilisé par plusieurs personnes. Est-ce que ma formule (qui fonctionne bien dans les conditions ou elle a été bâti) peut justement être modifié pour y incorporer les deux autres conditions de mon message (A-Accepter des valeurs pouvant aller jusqu'à 4 nombre après la lettre (EX. 11.4) et aussi d'ignorer la case vide dans le calcul sans provoquer une erreur de calcul?

Pour A je te l'ai mis. C'est --STXT(G10:G20;2;10)

Pour le reste en totalité je n'y crois guère. Mais qui sait, attend...

eric

Le reste étant d'ignorer les cases vides lors du calcul?

Je suis prêt a mettre des 0 partout ou il y a des cases vides et admettre que ca ne se fait pas au besoin mais le petit bout de formule fourni je crois fonctionne seulement si je fais deux colonnes distinctes pour les lettres et les chiffres. Il n'y a pas un bout qu'on peut ajouter qui dirait seulement de considérer tout après la lettre pouvant aller jusqu'à 4 caractère (EX: 11.7)?

C'est dans le post de 13:07 et dans le fichier en G4 :

...*((SI(NBCAR(G10:G20)=1;0;--STXT(G10:G20;2;10)))))

Est-ce que ca n'implique pas qu'il faut faire une colonne séparé pour les lettres? Est-ce possible d'inclure ce bout dans la formule pour qu'elle considère une cellule ou il y aurait des codes genre : V15, B5.6 etc...?

Si ce n'est pas réalisable en excel (le système à quand même ses limites). je chercherais une autre piste

Merci encore!

ce bout te donne le nombre qui suit la lettre, et 0 si lettre seule.

Tu vois bien qu'il est en G4 et ne se sert pas des colonnes supplémentaires.

Je voyais dans la formule qu'un 4e critère avait été ajouté pour une colonne qui n'existait pas à la base dans mon fichier et qui réglait le cas de la lettre. Vrai que la formule fonctionne dans ce cas et je t'en remercie puisque c'est une piste que je commence à regarder (séparer les codes des données chiffres) mais ce que je me demande depuis le départ est : Est-ce que c'est possible de modifier la formule de départ pour simplement dire : Considère tout les caractère allant jusqu'à 4 dans ton calcul, que ce soit 1-2-3 ou 4.

=SOMMEPROD(($C$10:$C$20="Peintre")*($D$10:$D$20="Rouleau")*($E$10:$E$20="Soir")*(GAUCHE(F10:F20,1)="V")*(--DROITE(F10:F20,2)))

Dans la formule la seule chose qui cloche c'est la fin. Je peux modifier le F10:F20,2 pour un F10:F20,3 mais alors TOUTES mes cellules devront avoir 3 caractère après la lettre sinon j'ai une erreur VALEUR.

Merci de ton aide et ta patience Eric et comme tu es l'expert si tu me dis que ca ne se fait pas...alors comme je t'ai mentionné je chercherais une autre poste!

mais c'est ce que je n'arrête pas de te dire :

à la place de :

*(--DROITE(F10:F20,2)))

tu mets :

*((SI(NBCAR(G10:G20)=1;0;--STXT(G10:G20;2;10)))))

et tu auras ton nombre qu'il fasse 0 ou 9999999999

Cette partie là extrait tous les caractères à partir du 2nd et les transforme en numérique.

Et pour le reste sans colonne supp je ne sais pas faire.

Merci Eric.

je vais simplement fermer le sujet et voir la suite car soit je m'exprime mal dans ma demande ou je ne comprends pas les explications.

je prend le blâme!

Si j'utilise la formule du fichier que tu m'envoie pour rester dans mes besoins je devrais enlever la partie qui défini le colonne H pour la lettre car je veux l'inclure dans un seul code (H11.7). Si je fais cela, la formule plante. Si je prends la formule tel quel elle plante aussi puisque je n'ai pas de colonnes spécifiques à la lettre.

Merci de l'aide..je vais repenser à ma disposition pour regarder ta solution n.1 qui fonctionne et qui cause peut-être moin de soucis.

Si tu ne veux pas de la colonne H et bien garde ce que tu avais avant pour cette partie : *(GAUCHE(G10:G20;1)="J")

Ta formule complète est donc :

=SOMMEPROD(($C$10:$C$20="Boulanger")*($D$10:$D$20="Couteau")*($E$10:$E$20="Jour")*(GAUCHE(G10:G20;1)="J")*((SI(NBCAR(G10:G20)=1;0;--STXT(G10:G20;2;10)))))

à valider en matriciel comme dit ici https://forum.excel-pratique.com/excel/formule-sommeprod-t84772.html#p491356 ce que tu sembles avoir oublié.

C'est à dire avecShift+ Ctrl+Entrée

Mais tu ferais mieux de décomposer tes codes temps. C'est aimer bien se compliquer la vie. Une cellule = 1 donnée, pas 2.

C'est ce que j'ai fait merci. Je suis présentement entrain de décomposer les codes temps également. Je suis tes conseils!

Dis moi une chose...quel impact de ne pas valider en matriciel?

On apprend avec nos erreurs, c'est la raison pour laquelle je viens chercher vos conseils et avis!

Ben il ne calcule pas sur la matrice et tu obtiens #valeur! comme ici, ou bien un mauvais résultat.

Sur certaines fonctions il le fait tout seul, sur d'autres il faut lui imposer.

Sommeprod lui-même est matriciel à la base, mais comme on lui ajoute une matrice à l'intérieur il faut forcer son évaluation.