Somme conditionnelle colonne

[Will-e-coyote]

Bonjour le forum !

Je cherche à simplifier la formule suivante :

{=SOMME(
SI(GAUCHE(Balance!$A$2:$A$1000;5)="46703";Balance!$E$2:$E$1000;0);
SI(GAUCHE(Balance!$A$2:$A$1000;5)="46703";Balance!$F$2:$F$1000;0)*-1)
}

Pour situer le contexte, il s'agit d'un tableau de synthèse d'une balance extraite d'une comptabilité. Non seulement, je cherche à la simplifier par pure efficience,😎 mais surtout, comme je ne sais jamais combien de ligne comprendra ma feuille de référence, je veux supprimer les numéro de ligne du style

{=SOMME(
SI(GAUCHE(Balance!$A:$A;5)="46703";Balance!$E:$E;0);
SI(GAUCHE(Balance!$A:$A;5)="46703";Balance!$F:$F;0)*-1)
}

SAUF QUE ça MARCHE PAS 😱

Merki pour vos gentilles réponses !!😱

 

[jeanpierre]

Re : Somme conditionnelle colonne

Bonsoir Will-coyote,

Ne serait-ce pas, plutôt :

=SOMME(SI(GAUCHE(Balance!$A$2:$A$1000;5)="46703";Balance!$E$2:$E$1000;0);SOMME(SI(GAUCHE(Balance!$A$2:$A$1000;5)="46703";Balance!$F$2:$F$1000;0)*-1))

ou :
=SOMME(SI(GAUCHE(Balance!A:A;5)="46703";Balance!E:E;0);SOMME(SI(GAUCHE(Balance!A:A;5)="46703";Balance!F:F;0)*-1))

Tu nous dis.

Bonne nuit.

Jean-Pierre

PS : sans les espace rajoutés par le forum

 

[Will-e-coyote]

Re : Somme conditionnelle colonne

Merci Jipé pour ta réponse....

Mais je ne comprends pas son sens vu que ma formule fonctionne très bien et donne un résultat juste. C'est une formule CSE que l'on peut obtenir en utilisant l'assistant de somme conditionnelle (macro complémentaire excel).

Bonne nuit

 

[Will-e-coyote]

Re : Somme conditionnelle colonne

Euuhhh... Ben non 😕

Ma première formule fonctionne très bien.

Seulement comme je ne connais pas par avance le nombre de ligne du tableau de données dans la feuille de référence, je pensais qu'il suffisait d'ôter les références de ligne pour que la formule ne déborde pas inutilement sur une plage non remplie.....

😉

 

[jeanpierre]

Re : Somme conditionnelle colonne

Re,


Tu es sous quelle version d'Excel ? En renseignant la version dans ton psudo, cela peut aider.

Je n'ai ni testé ta formule et la modification donnée, l'a été en direct dans le post.

Peut-être que cette fonction n'accepte pas les colonnes entières, (ce que tu as fait par A:A, par exemple), le cas de Sommeprod notamment.

Tu peux dans ce cas limiter la plage à 65000, ce qui est déjà pas mal.

Sinon, je regarde demain, maintenant.

Bonne nuit.

Jean-Pierre

 

[jeanpierre]

Re : Somme conditionnelle colonne

Bonjour Will-coyote,

Je viens de vérifier. Une chose sur laquelle je n'ai pas prêté attention, hier soir, c'est que c'est une formule matricielle et, dans ce cas, elle refuse une colonne entière.

Donc, comme je te l'ai dit, une limite à 65000 (A2:A65000), fonctionne sans te poser de problème. Si les cellules sont vides cela n'influencera pas le temps de calcul ni quoi que ce soit.

Bonne journée.

Jean-Pierre

 

[Gael]

Re : Somme conditionnelle colonne

Bonjour Will-e-coyote, salut Jean-Pierre,

Une solution en créant 2 plages variables nommées qui prendront en compte le nombre de lignes réel.

Exemple: avec insertion - nom - définir

1ère plage: NOCPT

=GAUCHE(DECALER(Feuil1!$A$1;1;;NBVAL(Feuil1!$A:$A)-1);5)

2ème plage: Balance

=DECALER(Feuil1!$E$1;1;;NBVAL(Feuil1!$A:$A)-1;2)

La 2ème plage prend en compte les colonnes E et F.

Et la formule devient:

=SOMMEPROD((Nocpt="46703")*Balance)

@+

Gael

 

[Will-e-coyote]

Re : Somme conditionnelle colonne

Effectivement c'est une formule matricielle (CSE parce-que pour la valider il faut faire Ctrl+Shift+Entrée)

Il semblerait bien en effet que cette fonction n'accepte pas les colonnes entières (bien dommage 🙁). En tout état de cause je te remercie beaucoup !

Quant à Gaël, tu es un magicien ! Même si je n'aime pas nommer des cellules ou des plages ta solution est lumineuse !
Je vais tout de même creuser avant avec le NBVAL() que je n'avais pas penser à utiliser !!

Mais où avais-je la tête 🙄🙄

Merki bôcoup les gens !!😀

 

[Will-e-coyote]

Re : Somme conditionnelle colonne

Euh...

Finalement je resollicite le forum (bonjour le forum 🙂) parce que la solution de gaël est forte interressante, mais elle ne fonctionne pas pour un type de besoin particulier :

SOMMEPROD((NoCpt=47303)*Balance)

Fonctionne très bien mais seulement pour un compte particulier, si je veux le faire pour une fourchette de comptes à racine commune, cela ne fonctionne pas.

SOMMEPROD((GAUCHE(NoCpt;3)=473)*Balance)

J'ai essayé avec ou sans guillemet, mais apparament, il ne prends en compte que le premier compte de la liste....

Avez vous des suggestions ?

Par avance, merci 😉

 

[Gael]

Re : Somme conditionnelle colonne

Bonjour Will-e-coyotte, bonjour à tous,

Chez moi ça marche avec les "":

SOMMEPROD((GAUCHE(NoCpt;3)="473")*Balance)

Sinon, essaye en formule matricielle en clicquant dans la formule et en faisant Ctrl-Maj-Entrée au lieu de Entrée. La formule se mettra automatiquement entre {}.

Sinon, envoie un exemple où ça ne marche pas.

@+

Gael

 

[Will-e-coyote]

Re : Somme conditionnelle colonne

Effectivement, ça fonctionne.

C'est juste moi qui fume, en fait, je devais obtenir un résultat que je n'avais pas car il manquait une partie de ma formule....

AU TEMPS POUR JE.... 🙄