Somme avec critère

[Yarout]

Bonjour à tous,

Je bute sur une formule me permettant d'additionner plusieurs sommes de plusieurs onglets années vers un onglet de consolidation par année. J'ai positionné un fichier en PJ pour l'exemple. j'ai un programme en année 1 qui va courir sur plusieurs années 1,2,35,6...
En année 2 un nouveau programme qui s'écoule sur plusieurs années et ainsi de suite. L'idée étant que dans l'onglet consolidation une somme récapitule les sommes par année.

Merci pour votre aide.

Yarout.

 

[FROLLINDE]

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)

 

[job75]

Re : Somme avec critère

Bonjour Yarout, FROLLINDE,

En E2 de la feuille "Consolidation" :

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

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

A+

 

[job75]

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+

 

[Yarout]

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

 

[job75]

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+

 

[Yarout]

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

 

[Yarout]

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...

 

[job75]

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+

 

[job75]

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 :

=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 :

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+

 

[job75]

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 :

=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 :

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+

 

[Yarout]

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.