[Résolu] Somme avec critères #N/A

[vividou]

Bonjour,

Je souhaiterai vos conseils pour le calcul suivant:

3 colonnes:
la première contient le nom d'une categorie,
la seconde contient une valeur,
la troisième contient soit une valeur soit #N/A.

exemple ci dessous:
A |B |C
FR|1 |15
FR|5 |100
FR|2 |#N/A
RU|5 |6
CN|3 |0
CN|1 |20
...

J'aimerai faire, pour chaque catégorie (valeur identique de la colonne A), la somme de la colonne B en excluant les valeurs pour lesquelles la colonne C vaut 0 ou #N/A.

J'ai regardé du côté de SOMMEPROD mais je n'arrive pas à exclure les cellules valant #N/A.

Avez-vous une idée pour résoudre ce problème?
En vous remerciant.

[Pierre-Yves Samyn]

Bonjour et bienvenue sur ce forum

Une solution via insertion d'une colonne "test" (qui pourrait être masquée) pour ne pas "propager" le "non-disponible"

[gerard24]

Bonsoir,

Comme l'indique Pierre-Yves, il n'est pas possible d'avoir plusieurs critères sur une même colonne dont l'un est une valeur d'erreur.
On ne peut donc pas utiliser NON(ESTNA(plage))*(plage<>0 même avec la fonction SI : SI(NON(ESTNA(plage));SI(plage<>0;1;.... ne fonctionneras pas.

Mais outre la colonne masquée, il y a une autre possibilité de contournement dans ce cas précis.
L'autre valeur à exclure étant 0, il suffit de provoquer une autre erreur par exemple en divisant un nombre par les valeurs de la colonne C
Les cellules contenant #N/A renverront #N/A, celle contenant 0 renverront #DIV/0! il suffit alors d'utiliser NON(ESTERREUR()) pour exclure ces valeurs.

Code : Tout sélectionner

=SOMMEPROD(($B$1:$B$7)*($A$1:$A$7=A11)*NON(ESTERREUR(1/$C$1:$C$7)))

A+

[vividou]

Bonjour,

Merci pour les réponses.

En suivant l'exemple:

Code : Tout sélectionner

=SOMMEPROD(($B$1:$B$7)*($A$1:$A$7=A11)*NON(ESTERREUR(1/$C$1:$C$7)))

je pense avoir trouver encore plus simple:

Code : Tout sélectionner

=SOMMEPROD(($B$1:$B$7)*($A$1:$A$7=A11)*ESTNUM(1/$C$1:$C$7))

Maintenant j'essaie de faire la somme de la colonne C toujours en fonction des catégories de la colonne A, mais ces #N/A posent toujours problème... comment faire?


Y a t'il un tutorial expliquant comment gerer les différentes erreurs?

[gerard24]

Plutôt que de chercher à contourner le problème #N/A, il serait sans doute plus simple de s'attaquer à la source.
Si ces #N/A sont le résultat d'une formule, le principe est d'utiliser la fonction SI.

=SI(ESTNA(ta_formule);"";ta_formule) ou tu peux renvoyer 0 a la place de "" (chaine de caractère vide).

Il est ensuite plus facile de traiter les données sans valeurs d'erreurs.

Y a t'il un tutorial expliquant comment gerer les différentes erreurs?

En dehors de l'aide F1, peut-être mais je n'en connais pas.

A+

[vividou]

Les #N/A ne proviennent pas du resultat d'une formule, c'est juste que la valeur pour cette cellule n'est pas définit donc j'y met =NA() qui traduit bien le fait de ne pas être définit. Dans mon cas, non définit et 0 n'ont pas la même signification quant à une case vise, cela peut signifier qu'elle n'a pas été remplie...
C'est pour cela que je souhaiterai dans la mesure du possible laisser #N/A qu'en il y en a mais pouvoir tout de même faire des calculs, notament des sommes, sur mes données.

[Pierre-Yves Samyn]

Les #N/A ne proviennent pas du resultat d'une formule, c'est juste que la valeur pour cette cellule n'est pas définit donc j'y met =NA() qui traduit bien le fait de ne pas être définit. Dans mon cas, non définit et 0 n'ont pas la même signification quant à une case vise, cela peut signifier qu'elle n'a pas été remplie...
C'est pour cela que je souhaiterai dans la mesure du possible laisser #N/A qu'en il y en a mais pouvoir tout de même faire des calculs, notament des sommes, sur mes données.

Ok mais dans ce cas pourquoi ne pas simplement mettre "NR" ce qui ne poserait plus de problème de "propagation" ?

Par parenthèse NR (non renseigné) est peut-être plus "parlant" que #N/D (texte affiché sur ma version française par la fonction ESTNA) et en tout cas plus court à saisir...

Quoiqu'il en soit, NR, #N/A ou #N/D nous t'avons donné des solutions me semble-t-il...

[gerard24]

Ok mais dans ce cas pourquoi ne pas simplement mettre "NR" ce qui ne poserait plus de problème de "propagation" ?

C'est vrai que tu te compliques la vie.

Une autre solution quand même avec la formule matricielle suivante :

Code : Tout sélectionner

=SOMME(($A$1:$A$7=A11)*SI(ESTNUM($C$1:$C$7);$C$1:$C$7))

Sinon si les développeurs parvenaient à faire fonctionner la fonction N() sous forme matricielle, cela simplifierait tout.

A+

[vividou]

Merci pour votre aide.
J'essaie de faire au mieux et je pense faire la bonne utilisation de la fonction =NA() même si ca peut compliquer un peu les choses.

Par contre je n'arrive pas à faire fonctionner la formule suivante:

Une autre solution quand même avec la formule matricielle suivante :

Code : Tout sélectionner

=SOMME(($A$1:$A$7=A11)*SI(ESTNUM($C$1:$C$7);$C$1:$C$7))

[jjk]

la formule matricielle suivante

As-tu bien saisi cette formule en la validant comme une formule matricielle, à savoir par Ctrl+Maj+Entrée ?
Toutes les formules matricielles se valident de cette manière.
Si oui, la formule doit être entourée d'accolades {...} dans la barre de formule.
Si tu ne vois pas les accolades, c'est que ta formule n'est pas matricielle.

[vividou]

oui c'était bien la validation de la fonction matricielle qui posait problème, la dernière formule fonctionne parfaitement!

Merci beaucoup!