Somme selon plusieurs critère

[MAZETTE]

Bonjour le Forum,

Je désespère de ne pouvoir trouver réponse à mon problème.

Il est pourtant simple:

A partir d'une base de donné (sexe,ancienete,ct,motif d'absence...)

Je cherche à effectuer une somme selon plusieurs critères dont le critère période qui correspond à un mois de l'année

Dans le bout de fichier joint, j'ai utilisé une formule somme.si mais je pense qu'avec une formule sommeprod on peut y arriver mais là...je coince.

L'exemple en pièce jointe est sans doute plus explicite...enfin je crois

Merci à vous

 

[nat54]

Re : Somme selon plusieurs critère

Bonjour

omme.prod me parait compliqué (voire imopssible ?!) dans la mesure où vous avez un critère en tête de colonne et non pas dans le corps de la colonne

Un TCD répond par contre à votre demande
J'en ai crée un mais malheureusement je n'arrive pas
A joindre le fichier (trop volumineux même en zip..)

 

[GPLIONEL]

Re : Somme selon plusieurs critère

Bonjour MAZETTE,

Voilà à mon avis la formule à mettre en C3 :
=SOMMEPROD((Feuil1!$A$4:$A$1119=Feuil2!$B3)*(Feuil1!$S$4:$S$1119=Feuil2!$A3)*(Feuil1!E$4:E$1119))


Formule à recopier verticalement et horizontalement dans ton tableau.
Il faut supprimer la fusion de cellule de ta colonne A et mettre les mois de référence 2 fois de suite.

 

[Jocelyn]

Re : Somme selon plusieurs critère

Bonjour le Forum,
Bonjour MAZETTE,

Si j'ai bien compris a tester en C4 de la feuille 2


=SOMMEPROD((Feuil1!A4:A1119=Feuil2!$B4)*(Feuil1!$S$4:$S$1119=Feuil2!$A3)*DECALER(Feuil1!$A$4:$A$1119;;EQUIV(C$1;Feuil1!$A$3:$S$3;0)))

Jocelyn

Oup(s Bonjour Nat54 et Gplionel désolé pour la collision

 

[MAZETTE]

Re : Somme selon plusieurs critère

Re à tous,

Je n'ai pas essayer ta formule Jocelyn car celle de GPLIONEL fonctione à merveille.

Merci à toi quand même pour le temps consacrer et bien sur merci à GPLIONEL

Une autre question :

Dans le cas où je souhaierai rajouter un critère suplémentaire

ex: imaginons dans le cas où dans la feuil 1 "les postes des employés" se trouve en colonne T et que sur la feuille 2 je rajoute une cellule en dessous de M avec les postes.

Est-ce que la formule suivante fonctionnerai:

=SOMMEPROD((Feuil1!$A$4:$A$1119=Feuil2!$B3)*(Feuil 1!$S$4:$S$1119=Feuil2!$A3)*(Feuil1!$T$4:$T$1119=Feuil2!$C3)*(Feuil1!E$4:E$1119))

J"ai beaucoup de mal avec sommeprod

Combien de critère max, l'ordre des critère etc...

 

[Tibo]

Re : Somme selon plusieurs critère

Bonsoir,

On peut ajouter beaucoup de tests dans un SOMMEPROD. Je ne sais d'ailleurs pas quelle est la limite.

Sinon pour ta formule, où doit-elle être mise ?

Ca peut fonctionner, dès lors que les données de ta colonne T sont bien en rapport avec le contenu de ta cellule C3 de ta Feuil2.

Pour le moment, en C3, on a des données chiffrées. D'où un doute sur le :

(Feuil1!$T$4:$T$1119=Feuil2!$C3)

A te (re)lire avec plus de détails

@+

 

[MAZETTE]

Re : Somme selon plusieurs critère

Bonjour à tous,

Pour continuer le projet, j'ai utilisé la formule de GPLIONEL qui correspondait bien à ce que voulait trouvé.

J'ai également suivi les conseils de Tibo,Jocelyn et Nat54

Mais là, je rencontre un nouveau problème.

En effet, je souhaiterai cette-fois sur la feuille "Ct", retrouver la valeur de "Absence injustifié" en fonction de :

l'Etablissement(en C2 sur "Ct")
du Mois(en C4 sur "Ct")
de l'Ancieneté (calculer en mois sur la colonne U de Feuil1)

J'ai donc utilisé la formule suivante mais je ne retrouve pas la valeur correspondante(en orange sur la Feuil 1 du Fichier)

=SOMMEPROD((Feuil1!$A$4:$A$875=$C$2)*(Feuil1!$U$4:$U$875<=$E4)*(Feuil1!$T$4:$T$875=$C4)*(Feuil1!F$4:F$875))

Quelle est mon erreur?

 

[Monique]

Re : Somme selon plusieurs critère

Bonjour,

Il y a deux lignes répondant aux critères
la ligne 13 et la ligne 63
SommeProd fait la somme 2,13+11 = 13,13

Rien à voir (un peu, quand même)
c'est mieux d'écrire la formule comme ça
=SOMMEPROD((Feuil1!$A$4:$A$875=$C$2)*(Feuil1!$U$4:$U$875<=$E4)*(Feuil1!$T$4:$T$875=$C4);Feuil1!F$4:F$875)
au cas où il y aurait, dans la plage à sommer, du texte ou des formules renvoyant ""

 

[MAZETTE]

Re : Somme selon plusieurs critère

Merci Monique , je suis vraiment mauvais y pas à dire ....

Une dernière petite question ...

Comment faire si je cherhce une valeur dans une intervalle de mois?

Par exemple:

Si je recherche une absence en fonction de:

l'Etablissement(en C2 sur "Ct")
du Mois(en C4 sur "Ct")
de l'Ancieneté (calculer en mois sur la colonne U de Feuil1) compris entre 1 et 3 mois par exemple

Peut-on écrire quelquechose du genre:

=SOMMEPROD((Feuil1!$A$4:$A$875=$C$2)*(1<Feuil1!$U$4: $U$875<3)*(Feuil1!$T$4:$T$875=$C4);Feuil1!F$4:F$875)

ou 1 et 3 correspondant aux employés ayant entre 1 et 3 mois d'ancièneté


Merci à toi

 

[Monique]

Re : Somme selon plusieurs critère

Re,

Les critères d'ancienneté comme ça :
(Feuil1!$U$4:$U$875>1)*(Feuil1!$U$4:$U$875<3)

Le * équivaut à "ET"
ET supérieur à 1 ET inférieur à 3

 

[MAZETTE]

Re : Somme selon plusieurs critère

Merci Monique pour tes précieux coneils...tu es formidable...

Je sais que j'abuse mais bon, on va essayé....une question encore...

Comment faire dans le cas où je souhaite regrouper plusieurs établissement ?

Imaginons par exemple que 168 et 140 appartiennent à la même zone géographique( Nord).
Est-ce qu'il faut additionner les sommeprod ou faire autrement ....sachant qu'une même zone peut contenir jusqu'à 13 établissement...

Merci à toi et à tous

 

[Monique]

Re : Somme selon plusieurs critère

Bonjour,

Plages nommées par Insertion - Nom - Définir
Formules plus faciles à lire, ça allège le fichier, ça accélère le recalcul
J'ai quand même enlevé plein de lignes, ne serait-ce que pour vérifier plus facilement
Formule nommée à partir de la ligne 9 (Insertion - Nom - Définir), ça allège le fichier, ça accélère le recalcul

"Est-ce qu'il faut additionner les sommeprod ou faire autrement ....sachant qu'une même zone peut contenir jusqu'à 13 établissements..."

On peut le faire avec SommeProd((Nb.Si()*)
SOMMEPROD((NB.SI(ChxEtab ; Plage des établissements)) * (Critère 1) * (Critère 2) * (Critère 3) ; Plage à sommer)

ChxEtab est une plage nommée, elle contient la liste des établissements qui t'intéressent (ou tous les établissements)

Dans le fichier, ça donne :
=SOMMEPROD((NB.SI(ChxEtab;Etabl))*(AncMois>$C4)*(AncMois<=$D4)*(Periode=$B4);DECALER(Etabl;0;EQUIV(E$2;TitreHaut;0)-1))

DECALER(Etabl;0;EQUIV(E$2;TitreHaut;0)-1) c'est pour ne pas nommer 36 plages :
nommer une plage à gauche du tableau suffit et la formule "décale" cette plage vers la droite, selon la position de E$2 dans la plage des titres de la feuille1
Les titres des 2 lignes de titres doivent avoir une orthographe identique, pas besoin de les mettre dans le même ordre