XL 2016 SOUS.TOTAL et SOMMEPROD

[GUY rrr]

Bonjour le forum,

J'ai une formule

=SOMMEPROD((MOIS($B$20:$B$1019)=1)*1;$J$20:$J$1019)

qui fonctionne très bien et souhaite y insérer un sous total.
J'ai innocemment et me semble-t-il logiquement essayé ceci

=SOUS.TOTAL(9;SOMMEPROD((MOIS($B$20:$B$1019)=1)*1;$J$20:$J$1019))

Et bien croyez-moi ou non, cela ne fonctionne pas .

Quelqu'un aurait-il une explication et une solution à mon problème car j'ai regardé à gauche et droite mais n'arrive pas à rendre fonctionnel.

Merci pour vos réponses

 

[sylvanu]

Bonjour Guy,
Si je comprends bien votre Sommeprod fait la somme de J lorsque le mois de de B est Janvier.
Il renvoie donc une valeur.
Sous.total(9;.... fait la somme d'une plage, donc ça ne peut pas marcher puisque le Sommeprod renvoie une valeur.
( https://support.microsoft.com/fr-fr...fonction-7b027003-f060-4ade-9040-e478765b9939 )
Que vouliez vous faire avec le Sous.total ?

 

[GUY rrr]

Bonjour sylvanu, le forum,

Vous avez bien compris ma demande.

En gros, j'ai un tableau avec en B des dates et J des valeurs.
Lorsque mon tableau n'est pas filtré, ma formule me renvoie la somme des valeurs de J en fonction du mois choisi en B.
Avec la formule que je tente de rendre fonctionnelle, je souhaite récupérer le résultat de la colonne J, toujours en fonction des mois choisis en B, et ce, quel que soit le filtrage effectué sur une colonne de A à J.

Souhaitant avoir été clair

 

[sylvanu]

Re,
Qu'entendez vous par "filtrer" ?
En PJ vous avez des filtres mis en B et J, et le résultat est toujours 100 ( dans l'exemple ) quels que soient les filtrages appliqués.
Vous auriez un fichier test pour montrer le problème ?

 

[GUY rrr]

Sylvanu,

Je trouve affreux de ne pas me faire comprendre avec des mots ... Va vraiment falloir revoir ma communication ...
Veuillez trouver ci-joint votre classeur en retour avec ma demande.

Merci pour votre retour

 

[sylvanu]

quel que soit le filtrage effectué

J'ai compris que le résultat devait être indépendant du filtrage.
En fait vous voulez exactement le contraire. Que le résultat tienne compte du filtrage.
Je regarde pour voir si j'y arrive.

 

[GUY rrr]

Je crois que j'ai compris ce que vous avez compris

 

[sylvanu]

Je n'y suis pas arrivé sauf en rajoutant une colonne où il y a :

=SI(SOUS.TOTAL(109;B20)>0;1;0)

Et le résultat devient :

=SOMMEPROD(((MOIS($B$20:$B$1019)=1)*1)*($K$20:$K$1019=1);$J$20:$J$1019)

Mais je suis sur qu'il existe une formule sans colonne ajoutée et sans VBA. Mais j'ai pas trouvé.

 

[TooFatBoy]

Bonjour,

J'ai innocemment et me semble-t-il logiquement essayé ceci

=SOUS.TOTAL(9;SOMMEPROD((MOIS($B$20:$B$1019)=1)*1;$J$20:$J$1019))

As-tu essayé avec 109 au lieu de 9 ?

 

[GUY rrr]

Bonjour Marcel32, sylvanu, le forum,

@Marcel32,
J'ai bien essayé le 109, mais rien ne change

@sylvanu,
Puis-je vous envoyer un MP ?

Merci pour vos réponses

 

[sylvanu]

Pourquoi pas. Mais quel intérêt par rapport au forum ?

 

[sylvanu]

Bonsoir,
Vous fûtes bien inspiré.
En PJ une solution qui est une adaptation d'un de vos liens :

SOUS.TOTAL et SOMMEPROD

Bonjour à tous ! J'ai trouvé un article dans le forum qui a une problématique proche de la mienne sauf que....(ça aurait été trop beau!!) ça

www.generation-nt.com

Avec :

=SOMMEPROD(((MOIS($B$20:$B$1019)=1)*1)*(SOUS.TOTAL(3;DECALER($B$20:$B$1019;LIGNE($B$20:$B$1019)-MIN(LIGNE($B$20:$B$1019));;1)));$J$20:$J$1019)

 

[GUY rrr]

Sylvanu, Marcel32, le forum,

Merci pour votre adaptation de la formule qui, je dois le reconnaitre n'aurait pu sortir en l'état actuel des choses de mon cerveau ...
Réponse rapide, formule fonctionnelle, je suis preneur et vous dit UN GRAND MERCI pour votre solution .

MERCI à tous les participants et au forum.

A bintôt

 

[Jocelyn]

Bonjour le Forum,
Bonjour GUY rrr, sylvanu, Marcel32,

Sur la base du fichier de Sylvanu et pour le fun une autre façon un poil plus courte d'écrire le sous.total() à l'intérieur du sommeprod()

Cordialement

 

[GUY rrr]

Bonjour sylvanu, Marcel32, Jocelyn, le forum,

@Jocelyn,

Et une solution supplémentaire dans la musette
Merci pour votre intervention

Bonne soirée

A bientôt