Erreur sommeprod avec dates issues d'un autre classeur

[help75]

Bonjour,

J'ai un petit souci avec sommeprod sur un classeur et j'ai du mal à comprendre ce qui ne va pas. En fait, j'ai un classeur source où je reprends toutes les données d'un département. Ensuite j'ai fait plusieurs classeurs par sous-groupe. Le seul fichier incrémenté au fur et à mesure est le fichier source, les autres sont renseignés par la mise à jour des liaisons.

Dans mes classeurs de sous-groupe, je fais des sommeprod à partir des éléments repris depuis le fichier source. Pas de pb, je peux calculer les marges, coûts, prix etc... par sous-groupe. Les plages que je choisies dans mes formules sont largement plus grandes que ce qui est effectivement rempli, afin de ne pas avoir à modifier mes formules au fur et à mesure que le fichier est complété (par exemple, comme le fichier se remplit mois après mois, je fais un sommeprod sur une plage allant jusqu'à la ligne 200 même si au final sur le premier trimestre les données ne vont que jusqu'à la ligne 50).

Mais sur un des onglets je voudrais calculer le nombre de dossiers traités en fonction des années. Et là, sommeprod me renvoie une erreur de valeur dès que la plage choisie est plus importante que la plage effectivement remplie. Par exemple, si j'ai 30 lignes de remplies (et donc le reste vide, mais avec des formules dedans renvoyant au classeur source) :
=SOMMEPROD((ANNEE(J1:J30)=2009)*1)
fonctionne, mais
=SOMMEPROD((ANNEE(J1:J200)=2009)*1)
ne marche pas.

Je suppose que c'est une histoire de format mais je n'arrive pas à résoudre cela. J'espère que j'ai été claire. Si quelqu'un a une idée, je suis preneuse.

Merci d'avance !

 

[tdenfert]

Re : Erreur sommeprod avec dates issues d'un autre classeur

Bonjour,

Je pense que le mieux serai d'utiliser des plages nommées et les rendre dynamique, comme cela tu ne prend pas en compte les cellules vide en fin de plage. du genre pour ta colonne date en j tu la nomme DateX et en ref =DECALER($J$1;0;0;NBVAL($J:$J);1)
A tester sinon met un bout de fichier

 

[tdenfert]

Re : Erreur sommeprod avec dates issues d'un autre classeur

Bonjour,
sinon avec ça =SOMME(SI(ANNEE(J1:J200)=2009;1;0)) à valider avec ctrl maj entree

 

[help75]

Re : Erreur sommeprod avec dates issues d'un autre classeur

Bonjour,

Merci beaucoup pour ces réponses. La formule Somme(si rencontre le même problème que sommeprod : ça marche si je limite ma plage aux valeurs apparaissant sur le fichier, mais pas si je définis une plage plus grande.
Je ne connaissais pas DECALER, ça a l'air super cette fonction. Malheureusement, ça me mets la même chose :#valeur!
Ce qui perturbe, c'est vraiment la formule qui est dans les cellules vides. Pour info, cette formule est :
=SI(LIGNES($15:31)>NB('[reporting.xls]dossiers'!$C$17:$C$200);"";RECHERCHEV(LIGNES($15:31);'[reporting.xls]dossiers'!$C$17:$X$200;COLONNE()+2;0))

Je ne sais pas si ça peut aider... Il s'agit de recopies de lignes depuis un autre classeur.