Discussion :

[Dédé6621]

Bonjour,

Je dois faire une somme de produits selon une condition. Ça donne cette formule de base:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(($B3:$B$28=$B$29)*$Y$3:$Y$28*AB$3:AB$28)

Mais la première colonne à multiplier change selon une liste déroulante en $T$29. La plage $Y$3:$Y$28 devient ainsi $Z$3:$Z$28 ou $AA$3:$AA$28 selon le choix fait. Pour obtenir la bonne plage, j'utilise ADRESSE, COLONNE, INDEX et EQUIV:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ADRESSE(3;COLONNE(INDEX($Y$1:$AA$1;;EQUIV($T$29;$Y$1:$AA$1;0))))&":"&ADRESSE(28;COLONNE(INDEX($Y$1:$AA$1;;EQUIV($T$29;$Y$1:$AA$1;0))))

EQUIV va chercher la position du choix fait dans la plage d'en-tête. INDEX donne la référence de la cellule selon cette position dans la plage d'en-tête. COLONNE donne le numéro de colonne de cette référence. Le reste vous comprenez, je pense.

Le problème, c'est qu'en remplaçant cette formule dans la précédente entourée de INDIRECT, la cellule renvoie l'erreur #VALEUR!:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(($B3:$B$28=$B$29)*INDIRECT(ADRESSE(3;COLONNE(INDEX($Y$1:$AA$1;;EQUIV($T$29;$Y$1:$AA$1;0))))&":"&ADRESSE(28;COLONNE(INDEX($Y$1:$AA$1;;EQUIV($T$29;$Y$1:$AA$1;0)))))*AB$3:AB$28)

J'ai conclu en lisant d'autres discussions que INDIRECT ne fonctionnait pas avec SOMMEPROD.

Est-ce qu'il y a une solution alternative ou une manière détournée de l'utiliser?

Sans les SI insérés les uns dans les autres, svp, d'autant que la formule va se complexifier encore avec une deuxième plage dans INDEX selon une autre liste déroulante...

Cordialement

[hben1961]

Bonjour à tous,

Utilise plutôt la fonction DECALER au lieu de la fonction INDIRECT, ta formule s'écrira peut-être sous la forme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(($B3:$B$28=$B$29)*DECALER($Y$3:$Y$28;;EQUIV($T$29;$Y$1:$AA$1;0)-1)*AB$3:AB$28)

Cordialement

[hben1961]

Bonjour,

Une petite correction à ta formule en y supprimant les deux fonctions COLONNE et INDEX qui devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(($B3:$B$28=$B$29)*INDIRECT(ADRESSE(3;24+EQUIV($T$29;$Y$1:$AA$1;0))&":"&ADRESSE(28;24+EQUIV($T$29;$Y$1:$AA$1;0)))*AB$3:AB$28)

Le 24 dans la formule remplace le nombre de colonnes de A à X...

Et c'est aussi pour conclure que la fonction INDIRECT fonctionnait bien avec la fonction SOMMEPROD.

Cordialement

[Dédé6621]

Utilise plutôt la fonction DECALER au lieu de la fonction INDIRECT, ta formule s'écrira peut-être sous la forme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(($B3:$B$28=$B$29)*DECALER($Y$3:$Y$28;;EQUIV($T$29;$Y$1:$AA$1;0)-1)*AB$3:AB$28)

C'est exactement la solution élégante que j'imaginais. Merci beaucoup!

Une petite correction à ta formule en y supprimant les deux fonctions COLONNE et INDEX qui devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(($B3:$B$28=$B$29)*INDIRECT(ADRESSE(3;24+EQUIV($T$29;$Y$1:$AA$1;0))&":"&ADRESSE(28;24+EQUIV($T$29;$Y$1:$AA$1;0)))*AB$3:AB$28)

Le 24 dans la formule remplace le nombre de colonnes de A à X...

Et c'est aussi pour conclure que la fonction INDIRECT fonctionnait bien avec la fonction SOMMEPROD.

En effet, SOMMEPROD fonctionne si on retire les fonctions COLONNE et INDEX, malgré la fonction INDIRECT. Sait-on pourquoi?
Après, il me semble que COLONNE et INDEX améliorent l'adaptabilité de la formule en cas d'ajout de colonnes. Mais c'est vrai que c'est secondaire.