Somme avec critère

F

FROLLINDE

XLDnaute Occasionnel

• 26 Octobre 2014

• #2

Re : Somme avec critère

Bonjour Yarout,

Peut être que la fonction indirect peut résoudre ton problème en permettant de savoir ou doit être faire la somme...
Meme si dans tes tableaux pour chaque projet il me semble que tu n'a qu'une valeur à récupérer..!

En E3 de ta feuille consolidation..
=SIERREUR(SOMME.SI(INDIRECT("'" & "Annee "&STXT(E$1;2;3) & "'!C:C");Consolidation!$C3;INDIRECT("'" & "Annee "&STXT(E$1;2;3) & "'!E:E"));0)

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 26 Octobre 2014

• #3

Re : Somme avec critère

Bonjour Yarout, FROLLINDE,

En E2 de la feuille "Consolidation" :

Code:

=SOMME('Annee 1:Annee 3'!E2)

'Annee 1' est la 1ère feuille à sommer, 'Annee 3' la dernière.

A+

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 26 Octobre 2014

• #4

Re : Somme avec critère

Re,

Vous pouvez même utiliser 'Annee N' comme dernière feuille.

Enlevez les pointillés à la fin du nom de la feuille pour éviter des soucis.

En effet Excel regroupe (certaines fois) 3 pointillés en un seul caractère...

A+

 

Vote positif 0 Vote négatif

Y

Yarout

Guest

• 26 Octobre 2014

• #5

Re : Somme avec critère

Bonsoir Job 75 et Frollinde,

merci pour vos réponses, en fait il y' a plusieurs valeurs a récupérer, le but est d'additionner pour chaque programme les valeurs de l'année, la totalité de N1 en N1, toutes les valeurs de N2 en N2. Dans mon exemple j'ai bien 3 fois N3 à récupérer et à venir sommer en N3 de consolidation.
la feuille N... était là à titre d'exemple pour indiquer que j'ai plusieurs programme d'investissement et pas seulement 3 .

Merci.
Yarout

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 26 Octobre 2014

• #6

Re : Somme avec critère

Re,

J'espère que vous avez compris qu'il suffit de tirer la formule que j'ai donnée vers la droite et vers le bas !

A+

 

Vote positif 0 Vote négatif

Y

Yarout

Guest

• 26 Octobre 2014

• #7

Re : Somme avec critère

Job 75 ,

Oui j'avais bien compris, mais cela ne correspond pas à mon problème, la cellule E3 de année 1 = année 1 N1 mais la cellule E3 de mon année 2 = N2, ma cellule E3 de année 3 = N3 ...donc avec une somme onglet tirée N1 s'additione a N2 et N3 or N1 est seule puis N2 de année 1 (cellule F3) doit se sommer à E3 et ainsi de suite.

Merci.
Yarout

 

Vote positif 0 Vote négatif

Y

Yarout

Guest

• 26 Octobre 2014

• #8

Re : Somme avec critère

Il doit être possible d'utiliser un somme si ou une rechercheH avec somme et critère mais je bute...

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 26 Octobre 2014

• #9

Re : Somme avec critère

Re,

Bah il suffit de faire en sorte que dans toutes les feuilles les N1 N2 N3... soient dans les mêmes colonnes.

Voyez le fichier joint.

Par ailleurs vous savez sûrement qu'on peut masquer les valeurs zéro soit par option soit par format personnalisé.

A+

 

Pièces jointes

• Cumul test(1).xlsx

  17.6 KB · Affichages: 34

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 27 Octobre 2014

• #10

Re : Somme avec critère

Bonjour à tous,

Si l'on ne veut pas décaler les tableaux il faut se résoudre à une formule à rallonge en Consolidation!E2 :

Code:

=SOMME.SI('Annee 1'!$1:$1;E$1;'Annee 1'!2:2)+SOMME.SI('Annee 2'!$1:$1;E$1;'Annee 2'!2:2)+SOMME.SI('Annee 3'!$1:$1;E$1;'Annee 3'!2:2)

Pas terrible si l'on travaille sur une centaine d'années 🙄

Alors mieux vaut un petit coup de VBA avec cette fonction macro :

Code:

Function MaSomme#(c1 As Range, c2 As Range)
Application.Volatile
Dim w As Worksheet
For Each w In Worksheets
  If w.Name <> Application.Caller.Parent.Name Then _
  MaSomme = MaSomme + Application.SumIf(w.Rows(c1.Row), c1, w.Rows(c2.Row))
Next
End Function

Fichier joint (les valeurs zéro ne sont pas affichées).

A+

 

Pièces jointes

• Cumul test par VBA(1).xlsm

  25.7 KB · Affichages: 31
• Cumul test par VBA(1).xlsm

  25.7 KB · Affichages: 36
• Cumul test par VBA(1).xlsm

  25.7 KB · Affichages: 36

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 27 Octobre 2014

• #11

Re : Somme avec critère

Bonjour à tous,

Si l'on ne veut pas décaler les tableaux il faut se résoudre à une formule à rallonge en Consolidation!E2 :

Code:

=SOMME.SI('Annee 1'!$1:$1;E$1;'Annee 1'!2:2)+SOMME.SI('Annee 2'!$1:$1;E$1;'Annee 2'!2:2)+SOMME.SI('Annee 3'!$1:$1;E$1;'Annee 3'!2:2)

Pas terrible si l'on travaille sur une centaine d'années 🙄

Alors mieux vaut un petit coup de VBA avec cette fonction macro :

Code:

Function MaSomme#(c1 As Range, c2 As Range)
Application.Volatile
Dim i%, w As Worksheet
i = Application.Caller.Parent.Index
For Each w In Worksheets
  If w.Index <> i Then _
  MaSomme = MaSomme + Application.SumIf(w.Rows(c1.Row), c1, w.Rows(c2.Row))
Next
End Function

Edit : ajouté la variable "i", ce qui fait gagner 30% sur le temps de calcul.

Fichier joint (les valeurs zéro ne sont pas affichées).

A+

 

Pièces jointes

• Cumul test par VBA(1).xlsm

  26.3 KB · Affichages: 34
• Cumul test par VBA(1).xlsm

  26.3 KB · Affichages: 39
• Cumul test par VBA(1).xlsm

  26.3 KB · Affichages: 40

Dernière édition: 27 Octobre 2014

Vote positif 0 Vote négatif

Y

Yarout

Guest

• 19 Janvier 2015

• #12

Re : Somme avec critère

Salut Job75, je reviens tardivement et m'en excuse j'ai pus grâce a vos propos consolidés ce fichier.
Merci à vous.
A bientôt.

 

Vote positif 0 Vote négatif