Somme de valeurs recherchées dans un autre onglet

[Framboise75]

Bonjour,

Je souhaiterais additionner des valeurs contenues dans un onglet différent selon plusieurs conditions. Il existe sûrement une formule type INDEX ou SOMMEPROD pour le faire, mais je ne trouve pas.
Etant donné que ce n'est pas facile à expliquer, j'ai joint un fichier.

N'hésitez pas à me demander si vous avez des questions.

J'espère que vous pourrez m'aider !

Merci d'avance et bon après-midi,
Françoise

 

[mth]

Re : Somme de valeurs recherchées dans un autre onglet

Bonjour SAS LABBE, Dull 🙂

Essaie cette formule en cellule B4:

=SOMMEPROD((INDIRECT("'"&$A4&"'!$A$2:$A$30")=$C$1)*(NON(ESTVIDE(INDIRECT("'"&$A4&"'!$S$2:$S$30")))) *(MOIS(INDIRECT("'"&$A4&"'!$S$2:$S$30"))=MOIS(B$3))*(INDIRECT("'"&$A4&"'!$P$2:$P$30")))

Attention, tu as des "xxx" qui traînent dans tes champs de date colonne S, il faudrait les éliminer, si non la formule ci dessus te renverra #VALEUR!

Bien à toi,
(et grosse bise Dull 🙂 )

mth

 

[Framboise75]

Re : Somme de valeurs recherchées dans un autre onglet

Merci beaucoup mth,

J'ai enlevé tous les xxx, mais la formule renvoie toujours le message d'erreur... 😕 (fichier joint)
Pourrais-tu stp "traduire" la formule pour une inculte d'excel comme moi (d'autant qu'il me faudra l'adapter à mon fichier original et ce sera plus facile si je comprends quelque chose !)

Merci d'avance !

 

[Dull]

Re : Somme de valeurs recherchées dans un autre onglet

Salut Françoise🙂, Mireille🙂, le Forum

Avec toutes ces parenthèses il y en avait une qui était mal placé 😉 et Si mth le permet voici la formule corrigée

=SOMMEPROD((INDIRECT("'"&$A4&"'!$A$2:$A$30")=$C$1)*(NON(ESTVIDE(INDIRECT("'"&$A4&"'!$S$2:$S$30"))))*(MOIS(INDIRECT("'"&$A4&"'!$S$2:$S$30")=MOIS(B$3)))*(INDIRECT("'"&$A4&"'!$P$2:$P$30")))

Pour la traduction Exercice Ô combien peu facile mais je vais essayer (nerigolepasMireille)😛

Indirect te permet de faire référence à la feuille dont le nom se trouve en A4.
la coche entre les guillemets "'"c'est pour les espaces dans le nom des feuilles: sans ces coches, et si il y a un espace dans le nom de la feuille, tu aura une erreur

Autrement dit =INDIRECT("'"&$A4&"'!$A$3") te donnera la valeur de la cellule A3 de la Feuille dont le nom est en A4.

NON(ESTVIDE) prend en considération seulement les cellules non vides de la plage

Pour SOMMEPROD c'est la Multiplication de toutes les valeurs des matrices puis la formule additionne les produits

Le conseil que je peux te donner c'est de séparer les différentes formules et de tester pour voir ce que cela donne

Ex: écrit =INDIRECT("'"&A4&"'!A3") dans une cellule et tu aura "Chantier n°2"

si tu écrit =DECALER(INDIRECT("'"&$A4&"'!$A$5");-2;15) te donnera la valeur de la cellule 2 cellules dessus et 15 cellules à droite soit 10

J'espère m'être bien fait comprendre 😉

Bonne Journée

 

[Framboise75]

Re : Somme de valeurs recherchées dans un autre onglet

Merci beaucoup Dull et bravo pour l'exercice difficile de traduction : j'ai presque tout compris sur le plan théorique (!) Si ok pour toi, je me permettrai de te recontacter en cas de problème sur la phase pratique (adaptation à mon fichier original).

Par contre, je m'aperçois qu'en insérant des dates dans la colonne S, Excel ne les différencie pas par mois et englobe tous les montants (fichier joint). Peut-être ai-je loupé quelque chose...

Merci d'avance

 

[mth]

Re : Somme de valeurs recherchées dans un autre onglet

re 🙂

Un ti problème de parenthèses 🙂
Vois ton fichier joint (je n'ai pas tout mis à jour partout, juste le début).
Par ailleurs, quand je t'ai dit d'éliminer les xxx dans les cellules, je ne voulais pas dire les remplacer par un tiret, il faut laisser vide, sans quoi tu auras une erreur

Bonne soirée à toi
et bravo Dull pour toutes tes explications 🙂 🙂

mth

 

[david84]

Re : Somme de valeurs recherchées dans un autre onglet

Bonsoir Framboise, Dull🙂,mth🙂,
A tester avec ce que j'en ai compris.

=SOMMEPROD((INDIRECT("'"&$A4&"'!$A$2:$A$14")='Récap par chantiers'!$C$1)*(MOIS(INDIRECT("'"&$A4&"'!$S$2:$S$14"))=MOIS('Récap par chantiers'!B$3));INDIRECT("'"&$A4&"'!$P$2:$P$14"))

Les résultats sont quelquefois différents de ceux de mth donc à vérifier.
Faire également ce qu'il faut pour enlever les valeurs d'erreurs (pas le temps de voir à quoi c'est dû et pas facile quand on prend le train en marche).
A+

 

[Tibo]

Re : Somme de valeurs recherchées dans un autre onglet

Bonjour à tous,

Pas grand chose à ajouter à l'excellente formule de David 🙂.

Les valeurs sont dus à des tirets - situés sur les lignes de total.

Sinon, toute petite correction pour tenir compte des cellules vides au niveau des dates et pour lesquelles Excel considère que le mois correspondant est Janvier :

=SOMMEPROD((INDIRECT("'"&$A4&"'!$A$2:$A$14")=$C$1)*(MOIS(INDIRECT("'"&$A4&
"'!$S$2:$S$14"))=MOIS(B$3))*(INDIRECT("'"&$A4&"'!$S$2:$S$14")<>"");INDIRECT(
"'"&$A4&"'!$P$2:$P$14"))

Si on ne devait pas toucher aux tirets (bien que n'ayant pas compris à quoi ils pouvaient bien servir), je pense qu'il faudrait passer à une matricielle du type =SOMME(SI(...

@+

 

[Framboise75]

Re : Somme de valeurs recherchées dans un autre onglet

Bonjour Dull, mth, David et Tibo,

Je ne saurais trop comment vous remercier (à part avec de fastidieuses flatteries excelliennes auxquelles je vous sais insensibles parce que modestes !), bref un grand merci à vous tous.

Pour répondre à vos questions : désolée pour les tirets à la place des xxx, mth 😱, mais c'est un lamentable bricolage ! J'ai remarqué que si la cellule n'est pas remplie, Excel ne l'inclut pas dans sa plage de filtre et j'ai besoin de pouvoir filtrer les informations contenues dans les différentes colonnes. Apparemment, si je mets simplement un chiffre "1", ça fonctionne également et ne fausse pas les résultats. Voilà l'explication Tibo !

La débutante que je suis n'est pas sûre de bien comprendre la différence entre la formule de mth et celle de David modifiée par Tibo : laquelle me conseillez-vous d'utiliser ? Dans le document de travail, elles semblent toutes deux très bien fonctionner... 😱

Merci encore et bonne journée !

 

[david84]

Re : Somme de valeurs recherchées dans un autre onglet

Re,
déjà tu peux éliminer la mienne puisque Tibo lui a rajouté une condition que j'avais zappée.
Ensuite, entre les 2 autres, le mieux est de les tester pour voir si elles te donnent les mêmes résultats. Si c'est le cas, eh bien tu n'auras plus qu'à faire ton choix, peut-être en choisissant celle que tu comprends le mieux.
A+

 

[Framboise75]

Re : Somme de valeurs recherchées dans un autre onglet

Merci David, c'est ce que je vais faire (même si je suis loin de comprendre ces formules !). Je vais passer à la phase pratique en essayant de l'adapter dans mon fichier original en espérant que tout se passe bien...

Merci encore pour tout et bon week-end (un peu en avance) !