SommeProd par l'exemple

Dénombrement

Soit le tableau des ventes réalisés par la société MPFE

 
A
B
C
1 Dates Vendeurs Qtés
2 14/02/2006 Jean 100
3 18/02/2006 Paul 90
4 07/03/2006 Jacques 90
5 12/03/2006 Annie 75
6 20/03/2006 Pierre 50
7 22/03/2006 Pierre 100
8 03/04/2006 Paul 120
9 08/04/2006 Annie 100
10 12/04/2006 Michel 100

soit :
A2:A10 la plage nommée "LesDates"
B2:B10 la plage nommée "LesVendeurs"
C2:C10 la plage nommée "LesQtes"

Une condition sur une seule plage

Quantités vendues avant le 1er avril 2006

=SOMMEPROD( LesQtes * (LesDates<--"31/03/2006"))

le double "opérateur unaire" intervient ici pour que Excel interprete la date "Texte" en N° de serie compatible avec les valeurs dates.
  • Dans le cas de saisie de dates sous forme texte, il convient de respecter le format de date correspondant aux options locales,
  • sinon on indiquera la date via la fonction DATE(Année;Mois;Jour).

=SOMMEPROD( LesQtes * (LesDates<DATE(2006;3;31)))

Multiples conditions sur une seule plage (Combinatoire OU)

Quantités vendues par Jean "et"(*) Annie

=SOMMEPROD( LesQtes * (LesVendeurs="Jean")) + SOMMEPROD( LesQtes * (lesVendeurs="Annie"))

cette formulation qui découle de la précédente péche par sa non-évolutivité, il faudra rajouter un SOMMEPROD pour chaque nouvelle condition.
(*): ce "et" littéraire est en fait un "OU logique" qui s'exprime dans la formule par le signe "+"
ajoutons un plage E1:E6 nommée "Qui"
  • en E1 mettons Jean
  • en E2 mettons Annie
  • en E4 mettons René

on notera que :

toutes les cellules de la plage "Qui" ne sont pas remplies.
René ne fait pas partie des vendeurs répertoriés.
La plage "Qui" n'a pas la même dimension que les plages nommées précédement.

Quantités vendues par "Qui"

=SOMMEPROD(LesQtes * (ESTNUM(EQUIV(LesVendeurs;Qui;0))))

l'astuce ici est la constitution d'une matrice booléenne des vendeurs appartenant à la plage "Qui"

La fonction EQUIV retourne la position d'un vendeur dans la plage "LesVendeurs"
La fonction ESTNUM intervient pour exclure les #N/A (non appartenance "René" et les "vides")

rappel : les fonctions utilisées dans un SOMMEPROD sont implicitement de type matricielle.

Multiples conditions sur plusieurs plages (Combinatoire ET)

Quantités vendues avant le 1er avril 2006 par Jean

=SOMMEPROD( LesQtes * (LesDates<--"31/03/2006") * (LesVendeurs="Jean"))
ou
=SOMMEPROD( LesQtes * (LesDates<DATE(2006;3;31)) * (LesVendeurs="Jean"))

Quantités vendues avant le 1er avril 2006 par plusieurs vendeurs "Qui"
=SOMMEPROD( LesQtes * (LesDates<--"31/03/2006") *(ESTNUM(EQUIV(LesVendeurs;Qui;0))))
ou
=SOMMEPROD( LesQtes * (LesDates<DATE(2006;3;31)) *(ESTNUM(EQUIV(LesVendeurs;Qui;0))))

  • autre exemple avec des données similaires présentées différement :
 
A
B
C
D
E
1 dates
Jean
Paul
Pierre
Annie
2 mercredi 1 mars 2006
2
8
2
3
3 jeudi 2 mars 2006
1
7
9
7
4 vendredi 3 mars 2006
2
5
 
4
5 lundi 6 mars 2006
6
 
 
2
6 mardi 7 mars 2006
8
8
 
3
7 mercredi 8 mars 2006
10
 
 
8
8 jeudi 9 mars 2006
4
 
 
1
9 vendredi 10 mars 2006
6
 
8
3
10 lundi 13 mars 2006
10
 
9
10

A2:Axx la plage nommée "LesDates"
B2:Bxx la plage nommée "VenteJean"
C2:Cxx la plage nommée "VentePaul"
D2:Dxx la plage nommée "VentePierre"
E2:Exx la plage nommée "VenteAnnie"
toutes ces plages comportent le même nombre de lignes

Quantités vendues par Jean les lundi

=SOMMEPROD(VenteJean; --(JOURSEM(LesDates;2)=1))
Quantités vendues par Paul les lundi
=SOMMEPROD(VentePaul; --(JOURSEM(LesDates;2)=1))

Il est alors facile de cumuler les ventes de Jean et de Paul les lundi.

=SOMMEPROD(VenteJean; --(JOURSEM(LesDates;2)=1)) + SOMMEPROD(VentePaul; --(JOURSEM(LesDates;2)=1))

Mais s'il s'agit cumuler les ventes de nombreux autres vendeurs, il devient fastidieux de cumuler alors un SOMMEPROD par vendeur.

L'utilisation du OU logique vient à notre secours :

=SOMMEPROD(VenteJean + VentePaul + VenteAnnie; --(JOURSEM(LesDates;2)=1))

Combinaison de critères avec recherche de texte

Comment trouver le NOMBRE de CELLULES répondant en même temps aux deux critères suivants :
Dans la colonne A1:A100, cellules contenant le code 1234
et
Dans la colonne B1:B100, cellules contenant une référence produit contenant les 3 lettres *ABC* (consécutives mais n'importe où dans la référence)
=SOMMEPROD((A1:A100=1234)*(ESTNUM(CHERCHE("ABC";B1:B100))).

Mots clefs associés à cette page : ,