XL 2016 Somme si (sous-total)

  • Initiateur de la discussion Initiateur de la discussion MLe
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

MLe

XLDnaute Nouveau
Bonjour à tous,
Je souhaite réaliser une somme mais avec deux conditions et un sous total en colonne M
1) Si B7 est différent de S_Total alors = B7
2) Si B7 = S_Total alors somme de la colonne C jusque B = S_total (du bas vers le haut)
Avec le fichier, c'est plus facile à comprendre.
Remarque: on ne peut pas tenir compte de la valeur de la colonne en M pour la somme (j'aurai une équation)
La colonne N donne la réponse souhaitée.
Je pense avoir bien passé une trentaine d'heures et ce n'est pas faute d'avoir chercher.
Merci d'avance pour votre aide.
Maxime
 

Pièces jointes

Un merci très sincère Monsieur le magicien. J'ai tellement cherché pendant des jours … Mais je dois avouer qu'il me reste un petit problème. Normalement, la réponse "M" ou "P" devait remplacer "X" dans la formule (0,8*1/(RACINE(X-1))). Je ne pouvais pas utiliser "M" mais "C"
=SI(B8<>"S_Total";C8;SOMME(C7:C$7)-SOMME.SI(B7:B$7;"S_Total";M7:M$7 ))
Je suis peut-être obligé de tricher avec deux colonnes ? Une que je vais cacher avec la réponse "M" et l'autre : =
(0,8*1/(RACINE(M-1)))
(0,8 * 1) 1 est une variable 🙂

Sinon, dans "=SI(B8<>"S_Total";C8;SOMME(M$7:M7)-2*SOMME.SI(B$7:B7;"S_Total";M$7:M7))" C'est le -2 que j'ai vraiment du mal à comprendre.
Encore un grand merci pour le temps que tu as passé à me répondre. Je te souhaite une belle fin d'année à toi et tes proches.
Maxime
 
Sinon, dans "=SI(B8<>"S_Total";C8;SOMME(M$7:M7)-2*SOMME.SI(B$7:B7;"S_Total";M$7:M7))" C'est le -2 que j'ai vraiment du mal à comprendre

SOMME(M$7:M7) ==> On fait le total des valeurs de la colonne M (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule). Ce total peut se décomposer comme étant la somme de :
  • des valeurs composant le premier sous-total
  • du premier sous-total
  • des valeurs composant le deuxième sous-total
  • du deuxième sous-total
  • ...
  • ...
  • des valeurs composant le dernier sous-total avant la ligne courant
  • du dernier sous-total avant la ligne courante
  • et enfin des valeurs composant le sous-total qu'on cherche à calculer.
On s’aperçoit qu'on somme :
  • le premier sous-total ainsi que les valeurs qui le composent (soit deux fois le premier sous-total)
  • le deuxième sous-total ainsi que les valeurs qui le composent (soit deux fois le deuxième sous-total)
  • ...
  • le dernier sous-total avant la ligne courante ainsi que les valeurs qui le composent (soit deux fois le sous-total avant la ligne courante)
  • et enfin des valeurs composant le sous-total qu'on cherche à calculer.
Donc qu'on somme :
  • deux fois tous les sous-totaux avant le sous-total de la ligne courante
  • et enfin des valeurs composant le sous-total qu'on cherche à calculer (notre but !)
Donc pour n'avoir que le dernier sous-total (celui qu'on recherche), il faut enlever du total de la colonne (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule) deux fois la somme des sous-totaux précédents (au-dessus de la ligne de la formule)

En espérant avoir été assez explicite 🙄
 
Dernière édition:
Re Ce lien n'existe plus 😉

Je n'ais pas ouvert le fichier post #2 , je viens de le faire et effectivement il y a peu de différence, si ce n'ai que je commence la formule en "M7"

Milles excuses 🙁 de ne pas avoir été plus curieux en n'ouvrant que le dernier fichier du post #5.

Bonnes Fêtes 🙂
 
Re @mdo100,

No problemo 😉. J'ai déjà constaté quelque fois la même chose alors que le différence était devant les yeux et m'avait echappée.

Cependant, si j'ai bien compris la question de Mle, Mle désirerait une formule sans utiliser la colonne M. C'était pour cela que j'ai pondu une formule matricielle (je suis allé à la facilité). Si tu as une formule non matricielle pour le faire, je suis preneur (je pense que c'est possible - et sans doute évident - mais pas le temps de m'y pencher pour l'instant)
 
Bonjour Ce lien n'existe plus et mdo100,
Merciii pour vos réponses. En effet, il faut une formule qui n'utilise pas la colonne dans laquelle on l'encode.
La réponse de la formule donnera un nombre ?. Ce nombre doit être intégré dans la formule (0,8*1/(RACINE(?-1))) :
M14 =
? = C14 si B14 est différent de "S_Total
sinon
? = la somme de C11:C13 car B10 = S_Total
M14 = (0,8*1/(RACINE(?-1)))
Avec le fichier en pièce jointe, cela sera certainement plus clair.
Cela est peut-être impossible à résoudre !
Belle journée !
 

Pièces jointes

Dernière édition:
Re MLe,

Je n'ais pas trouvé mieux que la formule matricielle de Ce lien n'existe plusce qui répond au post#11 😳

Donc si j'ai compris et avec la formule de Ce lien n'existe plus adaptée à la colonne "M"

En "M7" ta formule
Code:
=(0,8*$A$1/(RACINE(C7-1)))
Et en "M8"
Code:
=SI(SI(B8="S_Total";SOMME(C$7:C8)-SIERREUR(SOMME(DECALER(C$7;0;0;MAX( (B$7:B7="S_Total")*(LIGNE(B$7:B7)-LIGNE(B$7)+1));1));0);C8)>1;(0,8*$A$1/(RACINE(SI(B8="S_Total";SOMME(C$7:C8)-SIERREUR(SOMME(DECALER(C$7;0;0;MAX( (B$7:B7="S_Total")*(LIGNE(B$7:B7)-LIGNE(B$7)+1));1));0);C8)-1)));1)
Formule matricielle a valider avec les touches ctrl + maj + entrée
Et tirer vers le bas autant que nécessaire.
Voir dans le fichier joint:

Cordialement.
 

Pièces jointes

Oh mon dieu ... Cela fonctionne 🙂 C'est exactement cela. J'avoue ne pas comprendre la formule mais je vais essayer de passer quelques heures pour la digérer.
Merci à mdo100 et à Ce lien n'existe plus pour l'aide. Sans vous, je n'aurais certainement pas eu assez d'une vie pour trouver une solution. Merci à tous ces pros qui "Excel" dans les formules et qui donnent un peu de leur temps et leur savoir pour nous éviter de devenir fou à chercher pendant quelques nuits LA formule magique.
Belle fin d'année à vous et meilleurs vœux pour l'année 2019 !
Maxime
 
SOMME(M$7:M7) ==> On fait le total des valeurs de la colonne M (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule). Ce total peut se décomposer comme étant la somme de :
  • des valeurs composant le premier sous-total
  • du premier sous-total
  • des valeurs composant le deuxième sous-total
  • du deuxième sous-total
  • ...
  • ...
  • des valeurs composant le dernier sous-total avant la ligne courant
  • du dernier sous-total avant la ligne courante
  • et enfin des valeurs composant le sous-total qu'on cherche à calculer.
On s’aperçoit qu'on somme :
  • le premier sous-total ainsi que les valeurs qui le composent (soit deux fois le premier sous-total)
  • le deuxième sous-total ainsi que les valeurs qui le composent (soit deux fois le deuxième sous-total)
  • ...
  • le dernier sous-total avant la ligne courante ainsi que les valeurs qui le composent (soit deux fois le sous-total avant la ligne courante)
  • et enfin des valeurs composant le sous-total qu'on cherche à calculer.
Donc qu'on somme :
  • deux fois tous les sous-totaux avant le sous-total de la ligne courante
  • et enfin des valeurs composant le sous-total qu'on cherche à calculer (notre but !)
Donc pour n'avoir que le dernier sous-total (celui qu'on recherche), il faut enlever du total de la colonne (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule) deux fois la somme des sous-totaux précédents (au-dessus de la ligne de la formule)

En espérant avoir été assez explicite 🙄

Merci pour ces explications. C’est très clair et j’ai plaisir à comprendre. Je n’ai pas eu l’intelligence de remarquer que la somme de la colonne = deux fois le premier sous-total + deux fois le deuxième sous-total ... + le dernier sous-total (ce que l’on cherche)
Rrrr c’est frustrant de ne pas y avoir pensé ...mais il faut l’accepter.
Encore merci
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
12
Affichages
431
Réponses
3
Affichages
532
Retour