Somme si ens sur plusieurs feuilles avec Indirect

Bonjour à tous,

Je sais que ce sujet à déja été traité, mais je n'arrive pas à trouver de solutions sur la manière que je veux utiliser moi. Le soucis c'est que je tombe sur #valeur en permanence et je ne comprend pas pourquoi.

Je voudrais faire un somme.si.ens sur plusieurs sheet. J'ai créée une liste (nommé "Liste") contenant les sheet concernées par le calcul en question.

Sinon, sur chaque sheet qui va etre concernée par la somme, j'ai un double critère, un sur les mois, allant de H9 jusque V9, un qui va de B13 jusque B89, et le tableau lui meme qui va de H13 jusque V89.

J'ai donc cette formule :

=SOMME.SI.ENS(INDIRECT("'"&Liste&"'!$H$13:$V$89");INDIRECT("'"&Liste&"'!$B$13:$B$89");$B13;INDIRECT("'"&Liste&"'$H$9:$V$9");H$9))

Mais cela me retourne #valeur en permanence. J'ai déja effectué des vérifications de format sur chaque feuilles pour savoir s'il n'y avait pas de problèmes de valeur dans le tableau, et toutes les lignes sur mes critères ont été copiées collées donc il ne devrait pas y avoir de différence.

Est-ce que quelqu'un aurait une idée de comment procéder ?

Bonjour et bienvenu,

un fichier excel nous aiderait !

Voila le fichier en question,

L'idée serait de déverser les tableaux régionaux dans celui "global", à l'aide de la fonction Somme.si.ens. avec la fonction indirecte.

UPDATE : Somme.si.ens qui vient chercher des données dans une seule région ne fonctionne pas. le soucis doit etre le meme. Mais je n'arrive pas à le trouver

Ce qui est vraiment étrange c'est que quand j'utilise somme.si sur chacun des deux critères, je n'ai aucun problème, mais quand j'ssaie avec somme.si.ens cela ne fonctionne pas.

Si la structure des 3 onglets de base est toujours identiques, alors il suffit d'une formule dite "3D"

=SOMME('Marketing_FR:Marketing - AU'!H13)

en précisant le premier et le dernier onglet.

Parfois on encadre de 2 feuilles vierges que je nomme d pour début et f pour fin, ce qui donne alors

=SOMME(d:f!H13)

Alors justement, je sais qu'il existe des techniques beaucoup plus simples pour résoudre mon problème, mais ce n'est pas ce que je cherche. J'ai besoin de comprendre pourquoi cela ne fonctionne pas, parce que je serais amené à réutiliser cette formule par la suite. Et sans qu'il y ai la meme structure dans les autres fichiers.

J'ai donc besoin de cette identifications à double facteurs pour, dans le cas ou des lignes seraient ajoutées, ne pas avoir n'importe quoi dans mes sommes.

Voila le fichier en simplifié.

Je crois que j'ai compris le soucis. Un des critères se situe dans les colonnes et non dans des lignes.

Je ne sais pas comment régler le soucis en colonnes du coup. Est-ce que tu aurais une idée ?

Je vais voir, cela m'interpelle !

Comme dans SOMMEPROD, la taille des matrices doit bien correspondre, et cela semble être le cas !

H13:V89 avec d'un côté B13:B89 et de l'autre H9:V9

Donc je me demande quand même si ce n'est pas à cause du INDIRECT !

Peux-tu me donner la formule "somme.si sur chacun des deux critères" qui fonctionne ?

Je vais investiguer de mon côté.

J'ai réussi a faire ce que je veux, mais vraiment d'une façon qui ne me plait pas. Je t'envoie la formule en question :

=SOMME.SI.ENS(Marketing_FR!H$13:H$89;Marketing_FR!$B$13:$B$89;'Marketing_ Global'!$B14)+SOMME.SI.ENS(Marketing_HK!H$13:H$89;Marketing_HK!$B$13:$B$89;'Marketing_ Global'!$B14)+SOMME.SI.ENS(Marketing_USA!H$13:H$89;Marketing_USA!$B$13:$B$89;'Marketing_ Global'!$B14)+SOMME.SI.ENS(Marketing_AU!H$13:H$89;Marketing_AU!$B$13:$B$89;'Marketing_ Global'!$B14)

Mais j'aimerais vraiment le faire avec des indirect, via une liste, parce que c'est vraiment plus propre, et surtout a deux critères, pour les mêmes raisons.

Pour le coup, si le somme.si.ens ne fonctionne pas, je prend autre chose. Mais j'aimerais vraiment un truc plus simple, notamment via une sélection a double critère, vertical et horizontale, ainsi que m'éviter de recopier la formule pour chaque sheet

Merci !

En fait, dans ta formule initiale, il manquait un ! dans le dernier terme.

=SOMME.SI.ENS(INDIRECT("'"&Liste&"'!$H$13:$V$89");INDIRECT("'"&Liste&"'!$B$13:$B$89");$B13;INDIRECT("'"&Liste&"'!$H$9:$V$9");H$9)

Pour autant cela ne semble pas résoudre le problème !

J'ai essayé un truc tout simple ...

=Marketing_FR!H13

ok bien sûr

=INDIRECT("Marketing_FR!H13")

ok aussi, ouf quand même

=SOMME('Marketing_FR:Marketing - AU'!H13)

fonctionne

=SOMME(INDIRECT("'Marketing_FR:Marketing - AU'!H13"))

ne fonctionne pas, INDIRECT ne semble pas apprécier le mutli-onglet !

C'est confirmé ici

https://www.qwant.com/?q=indirect%203D%20excel&t=web

https://stackoverflow.com/questions/42479758/excel-indirect-formula-not-working-with-3d-reference

Ah, ok l'enfer.

Et du coup tu as une idée pour ce que je veux faire ? A savoir déverser automatiquement avec deux critères a respecter des données hébergées sur plusieurs sheet ?

J'ai l'impression que je vais devoir me résoudre à passer par de grosses formules bien laides.

bonjour

pour faire une somme 3 D

=SOMMEROD(SOMME.SI( INDIRECT( les champs);le critère;INDIRECT( leschamps à sommer)))

a valider en matriciel

cordialement

Bonjour,

Merci pour ta réponse.

J'ai donc une formule qui ressemble à ça :

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&Liste&"'!$H$13:$V$89");INDIRECT("'"&Liste&"'!$B$13:$B$89");$B13;INDIRECT("'"&Liste&"'!$H$9:$V$9");H$9))

Néanmoins, le problème de #ref est bien présent.

Est-ce que tu as une idée de comment avancer ?

Je te met l'exemple en dessous

re

j'ai essayé un tas de truc en vain .......Mais si la config générale est respectée , c'est tout simple

cordialement

Plus simplement, on peut aussi encadrer les feuilles à prendre en compte, ce qui rend la formule indépendante du nom de l'onglet et permet par rangement des feuilles d'élargir ou restreindre le nombre de feuilles.

Steelson a écrit :

Si la structure des 3 onglets de base est toujours identiques, alors il suffit d'une formule dite "3D"

Parfois on encadre de 2 feuilles vierges que je nomme d pour début et f pour fin, ce qui donne alors

=SOMME(d:f!H13)

mais cela ne semblait pas convenir si en effet les feuilles étaient amenées à être changées et diverger.

jenpat a écrit :

je serais amené à réutiliser cette formule par la suite. Et sans qu'il y ai la meme structure dans les autres fichiers.

J'aurais tendance à amener le débat plus en amont.

Comment sont réalisées les différentes feuilles ?

Si ce sont déjà des TCD, peut-on accéder à la source qui elle doit être compilable en une seule feuille avant de reproduire les mêmes TCD ?

Et si ce n'est pas le cas, le mieux serait justement de tendre vers une reporting sous forme de "base de données" consolidable et ensuite présentable sous forme de TCD.

J'ai souvent eu à consolider des données de reporting de 11 établissements différents et cette façon de faire a permis d'atteindre un bon niveau de productivité administrative de tous les acteurs.

Ok merci beaucoup pour le temps que vous avez consacré à ma question.

Je pense que je vais rester sur ce que j'ai pu faire auparavant, a savoir enchainer bêtement les somme.si.ens et rester comme cela.

Merci beaucoup en tous cas