Menu

Faire la somme d'une colonne selon plusières critères sous excel [Résolu]

Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
- - Dernière réponse : Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
- 9 mars 2019 à 10:53
Bonjour cher communauté,

je sollicite votre aide pour trouver une formule excel que je cherche sans succès depuis hier.
Voici mon problème:
Je cherche à faire la sommation des valeurs dans une feuil2 en fonction de plusieurs critères (selon les préfixes) des valeurs d'une colonne dans une feuil 1.
Exemple
Feuil 1
Colonne compte-------------------------------------Colonne Montant
217000---------------------------------------------------1000
218000---------------------------------------------------1500
218100---------------------------------------------------2000

Feuil2
Intitulé-------------------------------------------------------------Colonne Montant
Fonds commercial et droit au bail---------------= Somme des comptes commençant par 217, 218 (sauf 2181)

Ci-joint le tableau: https://www.cjoint.com/c/IChjFUAU3VB

Merci pour votre attention à ma demande
Afficher la suite 

Votre réponse

4 réponses

Messages postés
23665
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 mars 2019
5692
0
Merci
Bonjour
essayez cette formule là où vous voulez le résultat
=SOMMEPROD(((GAUCHE($A$4:$A$100;3)="217")+(GAUCHE($A$4:$A$100;3)="218"))*($C$4:$C$100))
n'oubliez aucun parenthèse
limitez les champs au nombre de ligne utile
si vous voulez remplacer 217 et 218 (obligatoirement entre guillemets), par des valeurs dans des cellules, entrez les adresses sans guillemets et entrez les code cherchés dans les cellules en les précédant de l'apostrophe pour les transformer en texte
crdlmnt

Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Mon cher Vaucluse
Super merci. Votre formule fonctionne bien sauf que l'exception "2181" n'est pas prise en compte.
Vous m'avez tiré d'affaires en attendant de perfectionné ma formule. Super merci mon cher
Vaucluse
Messages postés
23665
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 mars 2019
5692 -
C'est vrai que le 2181 m'avait échappé, mais voyez un peu plus bas dans le fil, le complément de formule de Patrice et la variante beaucoup plus directe de Eriiic.
ci-dessous
crdlmnt
Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Mon cher Vaucluse,

Effectivement le complément de Patrice et Eriic marche, sauf qu'il ya un détail qui m'echappe lorsque j'ai beaucoup d'exception par exemple "2181" et "2182".
Merci du fond de coeur à vous.
Vaucluse
Messages postés
23665
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 mars 2019
5692 -
la solution d'Eriiic fonctionne pour les codes de 217000 à 218000 inclus et ne compte donc pas les 218100 ou autres du même genre au delà de 218000
je pense que c'est cette option qu'il faut adopter.. sous réserve, bien entendu que tous vos codes en A soient bien des valeurs numériques
Toutefois vu votre dernier message, il n'y a rien à adapter dans cette formule, et surtout pas la limite maximum qui doit rester à 218000 (si votre demande est bien précisée de ne pas prendre en compte les > 218000)
Commenter la réponse de Vaucluse
Messages postés
2315
Date d'inscription
samedi 19 avril 2008
Dernière intervention
19 mars 2019
288
0
Merci
Bonjour a vous deux,

Et si tu avais une colonne ref en feuil1, ça serait plus facile, car la, tu est obligé de modifier la formule a chaque ref de la feuil2. Tu n'as pas de critéres de différenciation.

Crdlmt
Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Mon cher Djidji59430,

Merci pour votre contribution. Ma feuil 1 n'a pas de colonne réf et j'ai pas bien compris votre formule.
DjiDji59430
Messages postés
2315
Date d'inscription
samedi 19 avril 2008
Dernière intervention
19 mars 2019
288 -
Justement , devant chaque intilulé de la feuille1, il devrait y avoir la ref de la feuille 2 a laquelle il est attaché

Crdlmt
Commenter la réponse de DjiDji59430
Messages postés
7683
Date d'inscription
dimanche 13 juin 2010
Dernière intervention
22 mars 2019
1667
0
Merci
Ou
217+218-2181 :
=SOMMEPROD(((GAUCHE(Feuil1!$A$4:$A$75;3)="217")+(GAUCHE(Feuil1!$A$4:$A$75;3)="218")-(GAUCHE(Feuil1!$A$4:$A$75;4)="2181"))*Feuil1!C4:$C$75) 


Cordialement
Patrice
Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Mon cher Patrice33740,

La formule fonctionne super. Mais permets moi de poser la question de savoir les chiffres 3 (Feuil1!$A$4:$A$75;3) et 4 correspondent à quel arguments?
Aussi, si j'ai autant d'exception, est ce qu'il me suffirais de faire autant de soustraction.?
Merci beaucoup mes chers pour vos contributions ça m'aide vraiment.
Patrice33740
Messages postés
7683
Date d'inscription
dimanche 13 juin 2010
Dernière intervention
22 mars 2019
1667 -
le 3 (ou 4) correspond au nombre de caractères pris en compte dans l'intitulé du compte (à partir de la gauche)
Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Merci beaucoup mon cher Patrice pour votre aide précieuse.
Je fais le test sur plusieurs paramètres et vous revenir avec les résultats.
Commenter la réponse de Patrice33740
Messages postés
22571
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
22 mars 2019
7285
0
Merci
Bonjour à tous,

ou encore :
=SOMMEPROD((A4:A75>=217000)*(A4:A75<218100)*(C4:C75)) 

si tu as des codes de 218200 à 218999 il faudra les ajouter.
eric
Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Mon cher eriic,

Merci pour la super formule. Je teste ça marche avec l'exception "2181" mais si j'ajoute l'exception "2182" je ne m'en sort pas.
Voici comment j'ai fait : =SOMMEPROD((A4:A75>=217000)*((A4:A75<218100)*(A4:A75<218200))*(C4:C75))
Encore merci pour votre aide.
eriiic
Messages postés
22571
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
22 mars 2019
7285 -
en algèbre de Boole le OU c'est + :
=SOMMEPROD(((A4:A75>=217000)*(A4:A75<218100)+(A4:A75>=218200)*(A4:A75<219000))*(C4:C75))

eric
Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Mon tres cher Eriiic,
Merci pour la formule très cool et courte.
J'aime votre formule mais je ne suis pas un pro en algèbre. Pouvez vous me donner une petite explication simplifiée de la formule?
Désolé pour tout le dérangement.
eriiic
Messages postés
22571
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
22 mars 2019
7285 -
  • c'est ET

+ c'est OU
Recherche tables de vérités si tu veux les voir.

Les tests te font des matrices de VRAI/FAUX que tu opères avec des * (ET) et des + (OU) :
=SOMMEPROD(((plage>=217000) ET (plage<218100) 
OU (plage>=218200) ET (plage<219000))
  • (C4:C75))

Bon, le dernier multiplier est un... multiplier, puisque qu'on n'a plus affaire à des booléens (VRAI/FAUX) mais à des nombres
eric
Bonisam
Messages postés
121
Date d'inscription
mercredi 27 octobre 2010
Dernière intervention
9 mars 2019
-
Mon cher eriic,

Merci beaucoup pour cette leçon d'algèbre. Je comprends mieux maintenant.
Merci à vous très cher communauté pour votre aide.
Commenter la réponse de eriiic