Sommeprod renvoie #Ref
Bonjour,
Ma formule Sommeprod renvoie des résultats #Ref! à cause de #Ref! contenus dans la feuille source.
Comment exclure les #ref! pour que la formule prenne en considération que les valeurs "correctes" ?
=SUMPRODUCT(('[Source]BP4'!$E$11:$E$200=$A13)*('[Source]BP4'!$I$9:$DP$9="BP4")*('[Source]BP4'!$I$10:$DP$10=CO4)*('[Source]BP4'!$I$11:$DP$200))Merci
Bonjour
Le fichier?
Cordialement
Je me demande s'il est nécessaire de mettre le fichier dans la mesure où il fait 16mo, et je pensais que c'était un problème récurrent et que la réponse était simple. S'il le faut, je peux simuler un tableau.
Bonjour
Le problème est qu'il est impossible de déterminer quelles colonnes avec les erreurs #REF! contiennent des valeurs numériques et celles qui contiennent des valeurs de texte
Par exemple, en introduisant la condition ESTNUM, on élimine la prise en compte des valeurs d'erreurs
Ex dans cette formule
=SOMMEPROD(($A$5:$A$14=$G5)*($C$5:$C$14=ENT(H5))*(ESTNUM($E$5:$E$14));$E$5:$E$14) Cordialement
J'ai essayé un index-equiv sauf que ça n'additionne pas les lignes qui se répètent... Il y a moyen de jouer avec ça ?
Bonjour
La formule en B2 à incrémenter est
=SOMMEPROD((Source!$A$2:$A$14=Formule!$A2)*(DECALER(Source!$A$2:$A$14;;EQUIV(B$1;Source!$B$1:$M$1;0))))Je dois m'absenter, je regarderai pour les valeurs d'erreurs.
Cordialement
En effet, il y a des #Ref! encore, l'idéal serait bien sur d'avoir des "2" partout et un "1" en cellule F4.
Bonjour
Avec une formule matricielle (Validation avec Ctrl+Maj+Entrée)
En B2
=SOMME(SI(ESTNUM(DECALER(Source!$A$2:$A$14;;EQUIV(Formule!B$1;Source!$B$1:$M$1;0)));(Source!$A$2:$A$14=Formule!$A2)*DECALER(Source!$A$2:$A$14;;EQUIV(Formule!B$1;Source!$B$1:$M$1;0))))Cordialement
Bonjour,
La formule fonctionne c'est excellent, merci !
Je n'avais pas pensé au estnum...
Maintenant ma question est la suivante : J'ai appliqué le estnum à ma formule et elle a l'air de fonctionner. Dans ce cas, quel est le rôle du DECALER et pourquoi la valider en matricielle alors que ma formule fonctionne sans.
=SUMPRODUCT((Source!$A$2:$A$14=Formule!$A2)*(Source!$B$1:$M$1=Formule!B$1)*ISNUMBER(Source!$B$2:$M$14))Merci
Au fait non.
Je pense qu'il me faut absolument une fonction avec sommeprod parce que celle que tu me proposes ne fonctionne pas quand le fichier n'est pas ouvert.
Et c'est bien pour cette raison que je veux changer ma formule qui à la base est un somme.si et qui me renvoie des #N/A quand le fichier source est fermé.
Merci
Bonjour
Les Fonctions Sommeprod et Somme(Si, en validation matricielles fonctionnent toutes deux classeur source ouvert ou fermé.
Toutefois, Classeur source fermé, il est nécessaire d'inclure dans la formule le chemin d'accès du fichier source.
Sinon
=SOMME(SI(ESTNUM(DECALER(Source!$A$2:$A$14;;EQUIV(Formule!F$1;Source!$B$1:$M$1;0)));(Source!$A$2:$A$14=Formule!$A2)*DECALER(Source!$A$2:$A$14;;EQUIV(Formule!F$1;Source!$B$1:$M$1;0))))et
=SOMMEPROD(SI(ESTNUM(DECALER(Source!$A$2:$A$14;;EQUIV(Formule!F$1;Source!$B$1:$M$1;0)));(Source!$A$2:$A$14=Formule!$A2)*DECALER(Source!$A$2:$A$14;;EQUIV(Formule!F$1;Source!$B$1:$M$1;0))))sont identiques
cordialement
La formule ne fonctionne malheureusement pas quand le fichier source est fermé, elle me retourne "#Value!"
Pourtant, le chemin du fichier est bien indiqué dans la formule.
=SUMPRODUCT(IF(ISNUMBER(OFFSET('I:\FPnA\[BP4_2014 Load Basis.xlsb]BP4'!$E$10:$E$2630,,MATCH(CONCATENATE(CN$4,"BP4"),'I:\FPnA\[BP4_2014 Load Basis.xlsb]BP4'!$F$8:$DP$8,0))),('I:\FPnA\[BP4_2014 Load Basis.xlsb]BP4'!$E$10:$F$2630=$A13)*OFFSET('I:\FPnA\[BP4_2014 Load Basis.xlsb]BP4'!$E$10:$E$2630,,MATCH(CONCATENATE(CN$4,"BP4"),'I:\FPnA\[BP4_2014 Load Basis.xlsb]BP4'!$F$8:$DP$8,0))))Je tiens à préciser quand même que le chemin apparaît quand le fichier est fermé, mais disparaît quand le fichier est ouvert.
Merci
Bonjour
Si Sommeprod fonctionne classeur source fermé, ni Equiv, et encore moins Concatener ne fonctionnent classeur source fermé.
Il faut te résigner classeur fermé à utiliser des formules 100% Sommeprod du style
=SOMMEPROD(('D:\Mes documents\Forum Excel\2_En recherche\[source.xls]Source'!$A$2:$A$14=Formule!$A2)*(ESTNUM('D:\Mes documents\Forum Excel\2_En recherche\[source.xls]Source'!$B$2:$B$14));'D:\Mes documents\Forum Excel\2_En recherche\[source.xls]Source'!$B$2:$B$14)Cordialement
C'est bizarre parce que j'ai cette formule dans un autre fichier, elle utilise equiv et elle fonctionne parfaitement même si le classeur est fermé :
=IFERROR(IF(BM$6="Plan",INDEX('I:\FPnA\[BP4''Opex.xlsx]SG&A'!$B$1:$EB$78,MATCH($C16,'I:\FPnA\[BP4''Opex.xlsx]SG&A'!$B$1:$B$78,0),MATCH(BM$7,'I:\FPnA\[BP4''Opex.xlsx]SG&A'!$B$1:$EB$1,0)),IF(BM$6="ACT",-INDEX('I:\FPnA\\[DataPack_NIM V2 HGL.xlsb]Feuil_€'!$B$8:$JU$216,MATCH($C16,'I:\FPnA\\[DataPack_NIM V2 HGL.xlsb]Feuil_€'!$B$8:$B$216,0),MATCH(BM$7,'I:\FPnA\\[DataPack_NIM V2 HGL.xlsb]Feuil_€'!$B$8:$JU$8,0)),0)),0)Dans mon cas c'est donc impossible de trouver une formule qui fonctionne classeur fermé et qui pourrait ignorer les #Ref! contenus dans ce dernier ?
Merci
Bonjour
Sais pas, je n'ai pas assez d'éléments pour me rendre compte.
Cordialement