faire un somme.si avec 2 critères

[benoua]

Je souhaiterai automatiser la somme d'éléments dans une liste très lourdes (plusieurs milliers de lignes), en prenant en compte 2 critères et cela sans passer par une macro, un filtre ou autre, ni même un tri mais juste avec une formule de base de Excel.
En dessous de chaque code affaire à partir de G17, je voudrai qu'il y'ait la somme de tous les montants se trouvant dans un des comptes de la liste présente en colonne E et cela pour chaque Code Affaire (Colonne B)
Merci d'avance pour votre aide,

Ben

P.S.: étant donnée la lourdeur du fichier comparée aux restrictions du forum j'ai du supprimer pas mal de lignes, j'ai essayé de laisser des lignes avec un maximum de comptes n'appartenant pas à la liste.

 

[Brigitte]

Re : faire un somme.si avec 2 critères

Bonjour,

Si j'ai tout compris, formule à mettre en H18 et à recopier vers la droite :

=SOMMEPROD(($B$2:$B$17=H17)*($D$2:$D$17))
ou bien
=SOMME.SI($B$2:$B$17;H17;$D$2:$D$17)

qui donne bien en Y18 le résultat semble t'il escompté :

213305 : -17680,15

A adapter ensuite pour le reste du tableau (vers le bas).

Tu devrais privilégier au lieu de cette longue liste horizontale de tes codes affaires, un tableau vertical, avec une liste déroulante (pour éviter des errreurs de saisie).

Bon courage...

PS : sommeprod et somme.si fonctionnent bien. En effet, tu n'as qu'un critère, le code affaire (et non pas deux), mais si tu en avais deux, sommeprod est tout indiqué.

 

[JCGL]

Re : faire un somme.si avec 2 critères

Bonjour à tous,
Salut T'ites Couettes,

Un essai avec TCD.
"Joue" avec les choix de Compte et Affaires
Limite : 65536 lignes...

A+

 

[benoua]

Re : faire un somme.si avec 2 critères

Merci pour vos reponses rapides, mais concernant le TCD c'est exclu, en fait le fichier que je vous ai envoyé n'est qu'un morceau d'un fhichier bien plus complexe avec un tableau récapitulatif de toutes ces charges et la formule est destinée à se trouver dans une cellule. Effectivement faire un TCD marche très bien mais le but est d'automatiser ca par une formule qui je suis sur est possible.
Brigitte tu ne prends pas en comtpe le fait que les numéros de compte doivent faire partie de la lsite en colonne E et que beaucoup de comptes sont à exclure (le fichier original contient près de 30000 lignes et seulement une centaine de lignes contiennent un des comptes qui m'interessent!).
J'aimerai pouvoir vous montrer le fichier original pour que vous puissez comprendre pourquoi j'ai placé les codes affaires dans une ligne horizontale!
En fait, j'ai un tableau avec une colonne par code affaire, et une ligne par agrégat budgetaire. LE probleme et que dans l'agregat Salaires et traitements, se trouve des charges qui ne devrait pas y etre! Il ne devrait se trouver que les charges d'interim et de remuneration de stages. C'est à dire les comptes correspondant aux comptes de la colonne E de mon fichier. Hors d'habitude, je procède de la façon suivante :
je concatenne dans la feuille avec les extractions brutes, la colonne Agregatbudgetaire avec le code affaire, et je fais la même chose dans la feuille avec mon tableau de synthese et après pour mon somme.si, le tour est joué!
Sauf que pour l'agregat salaire et traitements, je voudrais en plus qu'il ne prenne que les comptes listés en colonne E et cela sans avoir à trier, faire de filtre, de tcd, de macros... Juste une formule tout bête! N'existe t'il pas un moyen!!?

Merci encore,

Ben

 

[jeanpierre]

Re : faire un somme.si avec 2 critères

Bonjour benoua, Brigitte, Jean-Claude,

A lire et relire tes et ta dernière explication, sauf le SOMMEPROD proposé par Brigitte, sans tri, sans filtre, ni même TCD, je ne vois pas d'autres formules toutes bêtes....

As-tu essayé ?

Si oui, tu bloques où ?

A te relire.

Jean-Pierre

 

[Brigitte]

Re : faire un somme.si avec 2 critères

Bonjour,

Je comprends pas non plus... ;-) jeanpierre.

Ou tu n'as même pas essayé la formule donnée (car chez moi elle fonctionne très très bien) ou bien si tu nous donnes un fichier dans lequel ca marche, comment veux tu qu'on y arrive ?

Ma proposition de mettre les codes en verticale (et en liste déroulante) était juste une question de bon sens... Excel a ses limites en nombre de colonnes alors qu'en lignes, on a plusss de possibilités.

Si ton "vrai" tableau est trop long et lourd pour faire ainsi, à part une macro, je vois pas.

Bon courage.

Bisous les J C et P

 

[JCGL]

Re : faire un somme.si avec 2 critères

Bonjour à tous,
Salut JP (Content de te lire ),

Peux-tu essayer avec en H18 et tirer vers la droite :

=SOMMEPROD((($C$2:$C$30000=$E$2)+($C$2:$C$30000=$E$3)+($C$2:$C$30000=$E$4)+($C$2:$C$30000=$E$5)+($C$2:$C$30000=$E$6)+($C$2:$C$30000=$E$7)+($C$2:$C$30000=$E$8))*($B$2:$B$30000=G$17)*$D$2:$D$30000)

Je pense que cette formule peut être réduite (un ProduitMat() ??)

A+

 

[Tibo]

Re : faire un somme.si avec 2 critères

Bonsoir,

J'ai entendu ma MP sonner et j'ai vu un message de JCGL pour me demander de jet un oeil sur la formule.

Bon, je ne connais pas trop la fonction PRODUITMAT (mais un jour, je l'aurais, cette fonction ...).

Cela dit je propose une simplification de la formule basée sur SOMMEPROD :

=SOMMEPROD(($B$2:$B$260=G17)*(ESTNUM(EQUIV($C$2:$C$260;E2:E8;0))*
(D2:D260)))

A tester quand même

@+

 

[JCGL]

Re : faire un somme.si avec 2 critères

Re,
Re Tibo ,

Mis à part le fait qu'il manque des $ pour un recopie vers la droite :

=SOMMEPROD(($B$2:$B$260=G$17)*(ESTNUM(EQUIV($C$2:$C$260;$E2:$E8;0))*($D$2:$D$260)))

BRAVO Tibo

A+

 

[benoua]

Re : faire un somme.si avec 2 critères

Re tous!

Merci merci!!
Même si mes explications n'étaient pas très claires vous avez bien saisie mon problème! La première solution par un SommeProd n'incluait pas le fait que le compte devait être parmi ceux listés en colonne E! Et maintenant c'est bon!
La formule de Tibo marche au poil! Merci beaucoup vous me rendez un grand service!
Une dernière question cependant, est ce que quelqu'un peut m'expliquer un peu le fonctionnement de la formule SommeProd svp? Je ne comprends pas bien pourquoi il y'a des "*" entre chaque matrice, et comment Excel sait laquelle il doit utiliser pour la somme!
merci d'avance,

Ben

 

[JCGL]

Re : faire un somme.si avec 2 critères

Re,

Les * sont des multipliés
Pourquoi la somme : SommeProd égale la Somme des Produits (Somme au sens Addition et Produits au sens Multiplication)

Pas clair.... arf Regarde l'aide Excel

A+

PS : Tibo : PRODUITMAT() doit avoir des plages de matrices équivalentes donc, je pense, que sur ce coup là, elle était inutile... Mais tu continues à tenter de la maitriser... arf

 

[Monique]

Re : faire un somme.si avec 2 critères

Bonjour,

Avec SommeProd((Nb.Si()

=SOMMEPROD((NB.SI($E$2:$E$8;$C$2:$C$30000)*($B$2:$B$30000=G$17)*$D$2:$D$30000))

Edit
C'est mieux comme ça :
=SOMMEPROD(NB.SI($E$2:$E$8;$C$2:$C$30000)*($B$2:$B$30000=G$17);$D$2:$D$30000)