Combiner une SOMMEPROD à un NB.SI.ENS Fermé

Question

Bonjour à tous,

Je mer permets de poser cette question car j'ai longuement cherché sur excel et dans le web, mais je ne trouve pas la solution.

J'ai un fichier excel avec plusieurs colonnes et j'aimerais afficher le nombre de cas ayant plusieurs conditions:
Voici les colonnes
Colonne J c'est des noms récurrents (demande ;  offre ; contrat; ...)
Colonne Q des dates (12.10.20201 ; 06.01.2022 ; ...)
Colonne W, la semaine de l'année (1 , 2 , 3)

Dans une cellule sur une autre page, j'aimerais afficher par exemple le nombre de fois ou dans la colonne J j'ai "offre" en semaine "2" et pour l'année "2021" (donc je veux toutes les fois ou en 2021, pas une date précise)

j'ai une formule Excel qui marche bien, mais elle m'embête au niveau de l'année. Car pour connaitre le nb de fois de 2021, j'ai du mettre dans la formule, que ça doit se situer entre le 01 janvier 2021 et le 31 décembre 2021)

Voici ma formule:
=NB.SI.ENS('Suivi SES'!J7000:J15000;'Indicateur - temps de passage'!D4;'Suivi SES'!W7000:W15000;'Indicateur - temps de passage'!$C$2;'Suivi SES'!Q7000:Q15000;">="&'Données indicateurs 1'!AL3;'Suivi SES'!Q7000:Q15000;"<= "&'Données indicateurs 1'!AM14)

J'aimerais maintenant garder la même formule, mais remplacer la partie qui cherche la date (en gras sur la formule ci-dessus) par exemple par une SOMMEPROD cherchant tous les cas de 2021.
J'ai réussis à faire le SOMME PROD séparément:
=SOMMEPROD((ANNEE('Suivi SES'!Q7000:Q15000)='Indicateur - temps de passage'!C3)*1)

J'aimerais maintenant le combiner à ma formule NB.SI.ENS
J'aurais ainsi mes 3 conditions: combien de fois "contrat" en semaine 2 et pour l'année 2021

Voilà.

PS: je ne peux pas mettre de fichier exemple car je suis sur le pc du bureau et l'ajout d'image/fichiers est bloqué.

Merci d'avance!

Vaucluse · Answer

Bonjour
Difficile à traiter avec ça. Pouvez vous déposer un modèle ici:
https://www.cjoint.com/
en y ajourant quelques infos sur les attendus et revenir coller le lien créé sur e site
à vous lire 
crdlmnt

Vaucluse · Answer

Re
et qu'est ce que cela donne si vous n'utilisez que des SOMMEPROD, sachant que NB.SI est moins disponible avec les dates
soit donc:
 =SOMMEPROD(('Suivi SES'!J6:J17='Indicateur - temps de passage'!D2)*('Suivi SES'!W6:W17='Indicateur - temps de passage'!B2)*('Suivi SES'!Q6:Q17>='Données indicateurs 1'!C8)*('Suivi SES'!Q6:Q17<='Données indicateurs 1'!D8))

crdlmnt

Vaucluse · Answer

et vous voulez quoi, exactement, calculer selon l'année ou selon des périodes de l'année parce qu'en D8 vous n'avez qu'une année?,
précisez un peu

giova.44 · Answer

En fait, j'ai remplacé la derniere phase de ta formule

J'ai fait ceci:

=SOMMEPROD(('Suivi SES'!J6:J17='Indicateur - temps de passage'!D2)*('Suivi SES'!W6:W17='Indicateur - temps de passage'!B2)*(ANNEE('Suivi SES'!Q6:Q17)=B3)*1)

Et maintenant ça fonctionne

Dommage que je puisse pas le faire avec NB.SI.ENS

Impossible dans une NB.SI.ENS de glisser, après quelques condition, un paranthèse avec une condition en sommeprod,?

giova.44 · Answer

Oui en effet, si sommeprod suffit, pas de soucis

J'aurais juste encore une autre question

J'ai aussi besoin d'une moyenne, j'ai également un formule qui marche mais seulement si j'entre à nouveau une date comprise entre un début et une fin.

Voici la formule:
=MOYENNE.SI.ENS('Suivi SES'!X7046:X10902;'Suivi SES'!W7046:W10902;'Indicateur - temps de passage'!$C$2;'Suivi SES'!J7046:J10902;'Indicateur - temps de passage'!D4;'Suivi SES'!Q7046:Q10902;">="&'Données indicateurs 1'!AL3;'Suivi SES'!Q7046:Q10902;"<= "&'Données indicateurs 1'!AM14)

Ma question est la meme: j'aimeais supprimer la partie en gras, par une sommeprod qui me sélectionne l'année.
Mais je ne sais pas comment faire la moyenne sans utiliser Moyenne.Si.Ens

Dans ce cas, est-il possible combiner le sommeprod à la place de la partie en gras de la formule? opu je dois faire toute une autre formule?

Vaucluse · Answer

comme ceci peut être:
 =SOMMEPROD(('Suivi SES'!X6:X17)*(ANNEE('Suivi SES'!Q6:Q17)=B3)*('Suivi SES'!W6:W17=B2)*('Suivi SES'!J6:J17=D2))/SOMMEPROD((ANNEE('Suivi SES'!Q6:Q17)=B3)*('Suivi SES'!W6:W17=B2)*('Suivi SES'!J6:J17=D2))
qui fait la somme des valeurs selon conditions et divise par le nombre de lignes remplissant ces conditions

giova.44 · Answer

Salut

Merci pour la réponse

J'ai essayé ta formule SOMMEPROD, mais ça me met: #DIV/0!

Si tu l'essaye dans le fichier que j'ai joint, ça te met quoi?

giova.44 · Answer

Je n'ai rien dit...ça marche

C'est juste que comme le résultat était zézo, ça mettait #DIV/0!

Merci beaucoup!

J'ai essayé dans mon fichier test.
Maintenant j'essai sur mon fichier pro

Vaucluse · Answer

Bonjour
pour éviter cet affichage #DIV/0 vous pouvez écrire:
=SIERREUR(formule complète);"")
qui renverra une cellule blanche
crdlmnt

giova.44 · Answer

re

Sur mon fichier, ça fonctionne pas!

Enfin, si je fais la formule sur mon fichier, avec la liste totale des colonnes concernées, je vais de 7046 à 10902

Donc ça donne ça:
=SOMMEPROD(('Suivi SES'!X7046:X10902)*(ANNEE('Suivi SES'!Q7046:Q10902)='Indicateur - temps de passage'!C3)*('Suivi SES'!W7046:W10902='Indicateur - temps de passage'!C2)*('Suivi SES'!J7046:J10902='Indicateur - temps de passage'!D4))/SOMMEPROD((ANNEE('Suivi SES'!Q7046:Q10902)='Indicateur - temps de passage'!C3)*('Suivi SES'!W7046:W10902='Indicateur - temps de passage'!C2)*('Suivi SES'!J7046:J10902='Indicateur - temps de passage'!D4))

Et le resultat est: #valeur!

Par contre, si je prends exactement la même formule, mais que je cherche dans une petite zone de 9248 à 9253, ça fonctionne!
Donc ci dessous ça fonctionne:
=SOMMEPROD(('Suivi SES'!X9248:X9253)*(ANNEE('Suivi SES'!Q9248:Q9253)='Indicateur - temps de passage'!C3)*('Suivi SES'!W9248:W9253='Indicateur - temps de passage'!C2)*('Suivi SES'!J9248:J9253='Indicateur - temps de passage'!D4))/SOMMEPROD((ANNEE('Suivi SES'!Q9248:Q9253)='Indicateur - temps de passage'!C3)*('Suivi SES'!W9248:W9253='Indicateur - temps de passage'!C2)*('Suivi SES'!J9248:J9253='Indicateur - temps de passage'!D4))

Je ne comprends pas :(

De plus, dans ma formule qui fonctionne pas, si je laisse juste le début ça fonctionne:
=SOMMEPROD(('Suivi SES'!X7046:X10902))

Mais si j'ajoute la 2ème partie, ça fonctionne déjà plus et ça met #valeur!:
=SOMMEPROD(('Suivi SES'!X7046:X10902)*(ANNEE('Suivi SES'!Q7046:Q10902)='Indicateur - temps de passage'!C3))

Je ne comprends pas

giova.44 · Answer

J'ai compris pourquoi ça marque #valeur!:

Si j'ai une cellule vide entre (ANNEE('Suivi SES'!Q7046:Q10902)='Indicateur - temps de passage'!C3)


Mais dans le fichier test que je t'ai donné en exemple, si je mets une cellule vide entre (ANNEE('Suivi SES'!Q6:Q17)='Indicateur - temps de passage'!C3), ça fonctionne quand même!

giova.44 · Answer

Si je met des cellules vides dns nimporte quelle autre colonne, ça fonction

Mais dès que j'enleve ici: (ANNEE('Suivi SES'!Q7046:Q10902)
ça met #valeur!

Tandis que dans le fichier test, si j'enlève ici: (ANNEE('Suivi SES'!Q6:Q17)='Indicateur - temps de passage'!C3)
ça fonctionne quand meme

je ne comprends pas l'erreur

Vaucluse · Answer

Alors sans le modèle on ne saura pas vous dire:

vérifier le format de cette cellule, on ne sait jamais
vérifier que cette cellule ne risque pas de renvoyer un "" dans une cellule des autres champs
sinon, il faudra nous soumettre le modèle

 tester la solution que je vous ai proposée avec MOYENNE.SI.ENS, en rajoutant de ligne 7046 à 1092 une colonne avec la formule:
=ANNEE(Q7046)
et utilisez la dans la formule (voyez en passant ce que renvoie la cellule vide incriminée)
cette colonne peut être sur n'importe quelle feuille, ou masquée 

sinon il faudra trouver une solution pour soumettre ce fichier

crdlmnt

Vaucluse · Answer

Alors il va falloir faire de la divination!!!?
dans votre fichier, y a t il (comme déjà demandé) un lien entre la cellule date et d'autres cellules (par exemple pour les n° de semaine
Si oui, repasser votre modèle exemple avec ce lien en place sur la plage utilisée
je ne peux rien de plus

Vaucluse · Answer

ne nous fâchons pas, ce n'était pas de la moquerie, ce n'est pas du tout mon genre, mais simplement le fait d'évoquer (peut-être avec un peu trop d'humour, mais rien de méprisant, loin de là) que sans autres informations sur le fichier, je ne peux pas vous aider plus.
Je ne répond jamais par obligation mais juste pour rendre service dans la mesure de mes moyens

ce que je vous demande par contre, c'est de vérifier, à défaut de pouvoir disposer du fichier, si la cellule date qui pose problème n'est pas liée par formule à un des champs que vous utilisez dans la formule
je pense par exemple au calcul de semaine à partir de la date, en colonne W

à vous lire si vous voulez continuer. pour moi ce sera avec plaisir!
Il y a aussi une autre option  si vous voulez passer en plus masqué:
inscrivez vous sur le site
vous pourrez alors m'envoyer un message personnel en cliquant sur l'icone en haut à droite et nous traiterons ça en masqué
Mais c'est vous qui voyez. En tous cas, rassurez vous sur mes prises de positions vis à vis de ce que vous appelez des nuls, ce que je connais bien puisque je l'étais en fait il n'y a pas si longtemps.
A vous lire?
Bien cordialement dans tous les cas

Vaucluse · Answer

tout dépend du "comment" est entrée la valeur dans X. Quelle est la formule?
pour exemple de ce qui peut se passer voyez le fichier joint avec les explications, en espérant que cela vous aidera à résoudre votre problème
Peux pas faire plus..à moins qu'en bidonnant votre fichier pour ne diffuser que les colonnes concernées, vous puissiez le déposer
en attendant bonne chance
https://www.cjoint.com/c/LAosXtJ27Ce
crdlmnt

giova.44 · Answer

Bonjour,

J'espère passé un bon weekend

Me revoici au bureau...

Alors, voici quelle formule viens prendre des infos dans la colonne Q (cette formule se trouve trouve dans la colonne X:
=SI(Q9258="";"";NB.JOURS.OUVRES.INTL(D9258;Q9258;1;'Indicateur - temps de passage'!E$47:E$57))

C'est vrai que dans mon fichier test, la colonne X n'a pas de formule, pour l'essai j'ai entré à la main.

Penses-tu que que cette formule soit la raison du résultat #valeur! lorsque la colonne Q est vide?

Vaucluse · Answer

Bonjour
désolé , mais il doit y avoir dans votre fichier quelque chose qui se modifie lorsque vous effacer quelque chose, et sans lui, je ne peux pas en dire plus 
Sauf que selon ce que vous nous dites là, effectivement si la colonne Q est complétement vide, l'affichage du défaut est normal, puisque l'on divise par 0
je vous soumet un second modéle qui peut utiliser MOYENNE.SI.ENS mais qui renverra toujours un défaut si le résultat des conditions en Q est vide
 https://www.cjoint.com/c/LArhjtldcke
à suivre?
crdlmnt

giova.44 · Answer

Salut

Je sais pas trop quoi dire.
La colonne Q est jamais complètement vide.
Mais dans la plage Q7046:Q10902, il y a certaines cellules vides. Dans toutes les autres je rentre la date manuellement.
Je n'efface jamais rien dans mon fichier, c'est juste que les cellules dans Q sont vides et au fur et à mesure je les remplie.
Comme mentionné, la colonne X se sert de la colonne Q pour un calcul.

Donc si je résume:

Tout est identique entre mon fichier test et mon fichier pro, sauf la colonne X.
Dans le fichier test , je rentre la valeur à la main
Dans le fichier pro, c'est une formule qui effectivement vient piocher dans Q:
=SI(Q9258="";"";NB.JOURS.OUVRES.INTL(D9258;Q9258;1;'Indicateur - temps de passage'!E$47:E$57))

Dans le fichier test, si il y a des cellules vides dans la plage Q, la moyenne fonctionne
Dans le fichier pro, dès qu'il y a une  seule cellule vide dans la plage Q, ça met #VALEUR!

Je ne peux vraiment pas partager ce fichier dsl :(

Je vais essayer dans mon fichier test, de mettre la formule dans la colonne X , au lieu d'entrer les valeurs à la main. Pour voir si l'erreur sera là

giova.44 · Answer

Je vais essayer dans mon fichier test, de mettre la formule dans la colonne X , au lieu d'entrer les valeurs à la main. Pour voir si l'erreur sera là

Voilà je viens d'essayer... même dans mon fichier test, si je mets la formule dans la colonne X au lieu de rentrer les données à la main, dès qu'il y a une cellule vide dans la colonne Q, ça ne fonctionne pas et ça met #valeur!


:(

giova.44 · Answer

Je crois avoir trouvé la solution

dans la formule de la colonne X, je change ceci:
=SI(Q9258="";""...
Par ceci:
=SI(Q9258="";"0"....

Du coup, il y a un zéro dans chaque cellule de X tant que je n'ai pas entré de date manuellement dans Q

Qu'en penses-tu

ça a l'air de marcher!

Vaucluse · Answer

alors effectivement, si vous avez des valeurs ""en X renvoyées par une formule, SOMMEPROD vous renverra #VALEUR

Sachant que une cellule vide en Q renverra l'année 1900 qui ne pourra en aucun cas être incluse dans vos limites et donc dans le calcul de la moyenne mais reste une valeur numérique, si vous pouvez, modifier votre formule comme suit soit remplacer ;"";"";  par ;"";0;

=SI(Q9258="";0;NB.JOURS.OUVRES.INTL(D9258;Q9258;1;'Indicateur - temps de passage'!E$47:E$57))
testé chez moi ça fonctionne

et au-delà, je ne peux pas faire grand chose de plus sans le fichier

crdlmnt

Combiner une SOMMEPROD à un NB.SI.ENS

22 réponses

Discussions similaires