Commande SOMMEPROD

Question

Bonjour,
je souhaite avoir la somme sur la feuille Synthèse! des nombres de la colonne C de la feuille resultats_2011 qui réponsent à deux critères. La formule : 
=SOMMEPROD(ANNEE(resultats_2011!A:A)=Synthèse!B8;resultats_2011!B:B="vélo";resultats_2011!C:C) me renvoit #VALEUR!
Pourqoi ?

Merci de votre aide.

Gyrus · Answer

Bonjour,

Tu peux essayer comme cela
=SOMMEPROD((resultats_2011!A:A=Synthèse!B8)*(resultats_2011!B:B="vélo")*(resultats_2011!C:C))
A+

Roya · Answer

Bonjour Gyrus.
Merci de m'aider.
J'ai essayer mais j'arrive au même résultat : #VALEUR!

tontong · Answer

Bonjour,
Avec les versions anciennes (antérieures à 2007 je crois ) on ne peut pas utiliser des colonnes entières avec Sommeprod. Il faut borner les champs A2:A500 au lieu de A:A, il faut de plus que tous les champs aient la même taille.

Roya · Answer

Merci Tontong.
J'ai la suite bureautique de windows 7. J'ai essayer aussi en bornant les colonnes, sans plus de succès. Ce sont mes tests qui ne fonctionnent pas.

Roya · Answer

Bonjour.
J'ai identifié le problème c'est ce test qui ne fonctionne pas :
ANNEE(resultats_2011!A$2:A$4000)=B8
Il y 2008 dans B8 et des dates de la forme 01/05/200X dans la colonne A.

tontong · Answer

Bonjour,
Il faut vérifier si on a bien des nombres et des dates (alignés par défaut à droite).
Dans une cellule libre tapez =ESTNUM(B8) la formule validée doit revoyer la valeur VRAI.
Faites de même avec A2,A3.....
Le 01/05/2008 doit devenir 39569 si on passe la cellule au format  Standard.

Roya · Answer

Bonjour.
Les =ESTNUM me renvoient bien vrai. Je pars une semaine en vacances, je recommence après.

tontong · Answer

Bonjour,
S'il y a des valeurs de texte dans le champ dont il faut faire la somme ( colonne C) il convient d'adopter cette syntaxe:
=SOMMEPROD((resultats_2011!A2:A500=Synthèse!B8)*(resultats_2011!B2:B500="vélo");(resultats_2011!C2:C500))
avec l'opérateur ; à la place du * pour le champ "à sommer".
Bonnes vacances.

Roya · Answer

Bonjour Tontong.
Si j'utilise ta formule elle me renvoie 0, ce qui est incohérent.

tontong · Answer

Bonjour,
Peut-on voir le fichier ou un extrait typique?

Vaucluse · Answer

Bonjour
si la formule renvoie valeur, c'est que vous avez dans une colonne  traitée des valeurs qui ne sont pas numériques. 
IUl semblerait d'"après vos messages que ce soit dans la colonne des dates.
Vérifier que toutes les valeurs dates soient bien numériques (formater la colonne en standard et voyez si toutes les dates deviennent des nombres)
crdlmnt

Roya · Answer

Bonjour.
effectivement, il doit y avoir des problèmes avec les dates.
Pour la SI"vélo" qui me renvoie 0 case C4 de la feuille Synthèse du fichier ci-joint, je ne comprends pas.

https://www.cjoint.com/?CIel57gZbZi

Roya · Answer

Merci à tous les deux.
Ca prend forme. Pour "Nota: Les totaux de la ligne 1761 pourraient être présentés en haut de la feuille.", je ne sais plus comment me débarrasser des totaux.
Maintenant, je vais essayer de faire pareil mais par mois.

Roya · Answer

Pas concluant.
Est ce que j'ai oublié quelque chose dans la syntaxe :
=SI((resultats_2011!J$2:J$1759="vélo");SOMMEPROD(ET(ANNEE(resultats_2011!I$2:I$1759)=J3;MOIS(resultats_2011!I$2:I$1759))=K2)*(resultats_2011!K$2:K$1759))

Comment me débarrasser des totaux "Nota: Les totaux de la ligne 1761 pourraient être présentés en haut de la feuille.".

Roya · Answer

Je n'ai copié la bonne formule dans mon post, je vérifie et je recommence.

Roya · Answer

Bonjour.
J'ai réessayé mais j'ai du oublier quelque chose.
Ma formule feuille "synthèse" case F3 encadrée en rouge ne fonctionne pas
=SOMMEPROD((resultats_2011!B$2:B$1759="vélo")*(ANNEE(resultats_2011!A$2:A$1759)=B3)*(MOIS(resultats_2011!I$2:I$1759)=F1);(resultats_2011!C$2:C$1759))

https://www.cjoint.com/?CIjpQnAoa9X

tontong · Answer

Bonjour,
Bonjour,
Seulement 2 erreurs pour le critère MOIS: le champ n'est pas en colonne I mais en colonne A et F1 contient un texte.La fonction MOIS renvoie un nombre de 1 à 12 donc dans le critère (MOIS(resultats_2011!I$2:I$1759)=F1)   il faudrait que F1 contienne un nombre... comme je l'avais déjà précisé au #19.

Pour arranger ça je propose d'ajouter en ligne 2: E2 =1 ,F2=2,.........P2=12 en dessous de Janvier, Février,......et de recopier en E3 la formule
=SOMMEPROD((resultats_2011!$B$2:$B$1759="vélo")*(ANNEE(resultats_2011!$A$2:$A$1759)=$B3)*(MOIS(resultats_2011!$A$2:$A$1759)=E$2);(resultats_2011!$C$2:$C$1759))
Les quelques $ de plus la rende recopiable!!! ;-)

Roya · Answer

Merci beacoup de votre aide.
Je viens d'essayé mais je ne trouve pas ce qui coince.

https://www.cjoint.com/?CIlls6njr2T

tontong · Answer

Bonjour,
On va y arriver, ne désespérez pas.
En E4 vous avez =SOMMEPROD((resultats_2011!B$2:B$1759="vélo")*(ANNEE(resultats_2011!A$2:A$1759)=B4)*(MOIS(resultats_2011!A$2:A$1759)=E1)*(resultats_2011!C$2:C$1759))
Il faudrait ..*(MOIS(resultats_2011!A$2:A$1759)=E2)..

En F4 vous avez ...*(MOIS(resultats_2011!I$2:I$1759)=F2)
Il faudrait ..*(MOIS(resultats_2011!A$2:A$1759)=F2)..

Faites, en E4, un copier/coller de cette formule:
=SOMMEPROD((resultats_2011!$B$2:$B$1759="vélo")*(ANNEE(resultats_2011!$A$2:$A$1759)=$B4)*(MOIS(resultats_2011!$A$2:$A$1759)=E$2)*(resultats_2011!$C$2:$C$1759))
et recopiez sur la ligne 4.

Roya · Answer

Merci tontong,
Ca fonctionne. 
Dommage que les copier-glisser fonctionnent mal avec ma formule et que je ne puisse pas selectionner des colonnes entières (présence de mots en début de colonne).
En tous cas, merci de votre patience.

Roya · Answer

Ah, j'oublié. Il me semble qu'il y a un moyen de "bloquer" les références à une plage de cellules pour ne pas qu'elles changent lors 'un copier-glisser.
Le connaissaez-vous ?

Roya · Answer

Merci pour toutes ces précisions.

Je vais finir tout seul...La première des 2 formules ci-dessous fonctionne, pas la seconde.
Il y a 1 et 2 dans E2 et F2.


=SOMMEPROD((resultats_2011!$B$2:$B$5000="course à 
pieds")*(ANNEE(resultats_2011!$A$2:$A$5000)=$B$4)*(MOIS(resultats_2011!$A$2:A$5000)=E$2);(resultats_2011!$C$2:$C$5000)) 		
=SOMMEPROD((resultats_2011!$B$2:$B$5000="course à 
pieds")*(ANNEE(resultats_2011!$A$2:$A$5000)=$B$4)*(MOIS(resultats_2011!$A$2:B$5000)=F$2);(resultats_2011!$C$2:$C$5000))

Roya · Answer

Super merci beaucoup.

Commande SOMMEPROD - Page 2

Discussions similaires

Newsletters