Pb SOMMEPROD et cellules date vides [Résolu]

[ericdev67]

Bonjour,

J’ai un petit souci :

Dans ma colonne A (A1 :A45), j’ai des dates. Par contre en A13 et A15, il n’y a pas de dates.

Je cherche à compter le nombre de dates de l’année 2011. En m’inspirant du post de Domi2, j’ai saisi la formule :

Code :

=SOMMEPROD(($A$2:$A$45>0)*(ANNEE($A$2:$A$45)=C45))

(valeur C45 = 2011), le résultat est ‘#VALEUR’, j’essaye :

Code :

1
2
=SOMMEPROD(($A$2:$A$45<>"")*(ANNEE($A$2:$A$45)=C45))
=SOMMEPROD((NBCAR($A$2:$A$45)>0)*(ANNEE($A$2:$A$45)=C45))

Toujours #VALEUR…..

Par contre si je fais :

Code :

=SOMMEPROD(($A$16:$A$45<>"")*(ANNEE($A$16:$A$45)=C45))

J’obtiens le bon comptage, mais à partir de A16……donc c’est bien la cellule A15 (vide) qui pose problème (la A13 aussi je pense…), à croire que ‘ANNEE(…..)’ se met en erreur car il n’arrive pas déterminer l’année d’une cellule vide…..

Comment puis-je contourner le problème ?

Une petite idée ?

(je sais que je peux passer par d’autres formules, type NBVAL, NB.SI, SOMME.SI, etc, mais j’ai des formules assez complexes utilisant SOMMEPROD, là, j’ai juste pris le début d’une formule...)

Merci
A+

[wilfried_42]

bonjour

peut etre en changeant de formule :

Code :

=somme(si(estvide($A$2:$A$45);0;si(annee($A$2:$A$45)=c45;1;0)))

Formule Matricielle à valider avec CTRL + MAJ + ENTREE, si la validation est correctement effectuée la formule est automatique encadrée avec des {}

[corona]

Bonjour,
As-tu vérifié que les cellules A13 & A15 sont réellement vides et ne contiendraient pas un ou des espaces ?
Dans l'affirmative c'est la fonction ANNEE() qui renvoie un message d'erreur #VALEUR et un message d'erreur multiplié par 0 renvoie également un message d'erreur.

[ericdev67]

Merci wilfried_42,
Merci corona,

wilfried_42, j'ai essayé, et j'ai le même message : #VALEUR

corona, j'ai suivi ton conseil, j'ai fait

Code :

=CELLULE("format";A15)

et j'ai la valeur

Code :

S

qui s'affiche, donc, effectivement, c'est bien année qui se met en erreur car 'ANNEE' n'arrive pas à le lire...maintenant, j'ai un début d'explication....
J'ai essayé de contourner le problème en essayant :

Code :

=SOMMEPROD((NBCAR($A$2:$A$45)>0)*(ESTERREUR(ANNEE($A$2:$A$45)=C45)<>FAUX))

, mais là, le résultat est =0....alors que j'ai 27 date en 2011....j'ai essayé avec 'ESTNONTEXTE' idem.....

Merci,
A+

[corona]

Bonjour,

J'ai essayé de contourner le problème en essayant :

Code :

=SOMMEPROD((NBCAR($A$2:$A$45)>0)*(ESTERREUR(ANNEE($A$2:$A$45)=C45)<>FAUX))

, mais là, le résultat est =0....alors que j'ai 27 date en 2011....j'ai essayé avec 'ESTNONTEXTE' idem.....

Essaye ceci cela devrait fonctionner.

Code :

=SOMMEPROD((NBCAR($A$2:$A$45)>0)*(NON(ESTERREUR(ANNEE($A$2:$A$45)=D1))<>FAUX))

corona, j'ai suivi ton conseil, j'ai fait
Code : =CELLULE("format";A15)
et j'ai la valeur

Pour savoir s'il y a un ou des espaces dans une cellule (cad des caractères) il faut mettre comme formule

Code :

=NBCAR(A15)

ET dans ce cas, il suffit simplement de faire delete dans cette cellule, pour effacer les espaces parasites (excepté si c'est le résultat d'une autre formule évidemment).

[ericdev67]

Merci corona,

J'ai essayé ton

Code :

=SOMMEPROD((NBCAR($A$2:$A$45)>0)*(NON(ESTERREUR(ANNEE($A$2:$A$45)=D1))<>FAUX))

et ça fonctionne nickel !!! j'étais sur la bonne voie grâce à toi (et tes anciennes réponses à des post), mais c'était la formulation qui était à revoir.....merci de m'avoir montrer comment contourner le problème et surtout dans quel sens il faut l'écrire !!

Pour

Citation:
Code : =CELLULE("format";A15)
et j'ai la valeur "S"

c'était pour voir ce qu'il y avait dans la cellule : elle apparaissait vide, et lorsque je faisais 'ANNEE(A15)' j'avais #VALEUR, alors que pour une cellule vide, j'aurai dû avoir : '1900'.

Par contre, je vais rester sur ta formule car il m'est impossible (sans passer par VBA) d'effacer le contenu de la cellule manuellment :c'est un tableau que j'importe, 5000 lignes, dont ~200 sans dates, donc.....

Merci encore

A+ pour de prochaines aventures

[ericdev67]

Oups,

J'ai peut-être été trop rapide à enlever le "résolu" : j'ai fait

Code :

=SOMMEPROD((((NBCAR($F$2:$F$153)>0)*(NON(ESTERREUR(MOIS(($F$2:$F$153))=1))<>FAUX)))*($N$2:$N$153="P"))

le résultat est qu'il me donne bien le nombre de "P" (34), mais pas dans le bon mois ... : il y a 34 "P" dans l'ensemble des dates, mais il y en a que 19 dans le mois de janvier, j'ai essayé des combinaisons de parathèse, mais ça ne fonctionne pas....une idée ?

Merci,
A+

[corona]

Je vois d'où vient l'erreur mais pour l'instant je ne vois pas comment la résoudre.
Comme on détecte l'erreur provenant du fait que certaines cellules ne sont pas numériques toutes les cellules contenant une date sont à VRAI.
Je sèche pour l'instant.

[ericdev67]

Merci corona,

Je vois que mes cellules vides (qui ne le sont pas en fait....) posent problème....je continue de chercher....

Merci,
A+

[rtg57]

Bonjour,

je constate que vos formules contenues dans les cases sont assez conséquentes...chacun son truc mais parfois le code est plus clair et donc plus facile à comprendre, en décomposant par des calculs intermédiaires sur plusieurs colonnes, quitte à les masquer ensuite.
Par exemple, il suffirait de passer par une colonne intermédiaire qui ne serait que la copie de la colonne contenant les dates. ( = Case d'àcôté )
Cela a l'avantage de fournir un '0' (valeur numérique) lorsque la cellule est vide.
Cela évite donc de mettre encore une batteries de tests dans les formules.
Ainsi, pour les formules numériques qui en découle, au pire, elles calculent avec un '0' comme base de travail, au lieu d'un vide.

A voir... il y a tellement de possibilités...

@ bientôt...

[ericdev67]

Merci rtg57,

C'est effectivement une solution que j'avais envisagé, mais....en fait mes formules SOMMEPROD vont chercher les données dans des classeurs fermés, dans lesquels je ne souhaite pas rajouter des colonnes : il y a déjà 52 feuilles (=52 semaines) et je ne souhaite pas passer par VBA (qui effectivement pourrait me transformer mes cellules vides en "0"), mais merci quand même, ça fait partie des pistes envisageables....

Merci,
A+

[ericdev67]

Ayé, j'ai résolu mon problème :

Code :

=sommeprod(((stxt(texte(f2:f153;"jj/mm/aaaa");4;2)="01")*1)

Pas très orthodoxe, mais ça fonctionne :

Je convertis ma cellule en texte-date, je sélectionne mm (= le mois) et si c'est "01" (=janvier), je compte.....

Une journée pour y arriver.....

Merci aux tutos de Pierre qui m'ont inspiré indirectement, mais qui m'ont donné l'idée de convertir.....

A+ pour de prochaines aventures.....

[corona]

Super, comme quoi on peux toujours y arriver avec les fonctions d'excel

[ericdev67]

Oups, merci aussi à toi corona

(c'est aussi avec tes conseils de ESTERREUR, et les fonctions EST des autres post que ça m'a aidé à trouver une solution....)

A+ pour de prochaines aventures

[wilfried_42]

re:

attention : =TEXTE(A1;"JJ/MM/AAAA") si A1 est vide, le résultat donne 00/01/1900.
bien tester si la cellule est vide aussi sinon tu auras un mois de janvier faux

[ericdev67]

Merci wilfried_42 pour l'alerte,

Mais dans mon cas justement, la cellule n'est pas vide : elle affiche "vide", mais elle contient des caractères, qui d'ailleurs me mettaient "MOIS()", "ANNEE()" en erreur, c'est un tableau extrait d'une base, donc, il doit y avoir des conversions qui "déco......ent".
Merci pour l'alerte, mais pas de souci....pour l'instant

A+