Pb SOMMEPROD et cellules date vides

**ericdev67** · 19/01/2011, 13h00

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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** · 19/01/2011, 13h34

bonjour

peut etre en changeant de formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 {}

**Philippe Tulliez** · 19/01/2011, 13h53

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** · 19/01/2011, 14h42

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=CELLULE("format";A15)

et j'ai la valeur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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+

**Philippe Tulliez** · 19/01/2011, 15h24

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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** · 19/01/2011, 15h49

Merci corona,

J'ai essayé ton

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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** · 19/01/2011, 17h17

Oups,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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+

**Philippe Tulliez** · 19/01/2011, 18h03

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** · 19/01/2011, 18h28

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** · 19/01/2011, 18h32

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** · 19/01/2011, 18h56

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** · 19/01/2011, 20h03

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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.....

**Philippe Tulliez** · 19/01/2011, 20h09

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

**ericdev67** · 19/01/2011, 20h23

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** · 20/01/2011, 04h50

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** · 20/01/2011, 08h18

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+

Pb SOMMEPROD et cellules date vides

Excel

Discussions similaires

Partager

Partager