Problème à signaler:


Télécharger Manuel d'utilisation de la fonction somme.si Excel



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

/home/coursgratuit/public_html/administrator/com_doconv/htmlfiles/10812 Dans la série
Les tutoriels libres
présentés par le site FRAMASOFT
Sommes 
Conditionnelles 
Utilisation de la Fonction SOMMEPROD()
en remplacement de la fonction compteur 
et de la fonction 
Logiciel:
 Calc : Classeur 
Version:
2.0 sur WindowsXP; 2.0 sur Linux
Licence:
GNU-F.D.L.
Site:

Par
Jean-Yves Lucca
version du 27 octobre 2005
Framasoft 
« Partir de Windows pour découvrir le libre  »


Tutoriel Framasoft
Table des matières
 3
1.1.Licence de ce document  3
1.2.Objectifs .. 3
1Un petit rappel sur la fonction SOMME .. 3
2SOMME sous une seule condition  3
3Sommer sous plusieurs conditions : la Fonction SOMMMEPROD .. 3
4SOMMEPROD() dans toute sa puissance  .. 3
5Des pistes de découvertes .. 3
1.3.Typographie .. 3
 4
2.1.Désactiver l'AutoSaisie  4
 Sommes à calculer: Le problème posé  4
2.2.1.Somme de cellules non adjacentes  .. 4
2.2.2.Somme de cellules adjacentes ou plage de cellules . 5
2.3.Copier-Glisser .. 6
2.4.Références relatives ou absolues . 6
..6
 fonction () .. 7
3.1.1.Utilisation de l'Assistant de fonctions (facultatif) .. 7
3.2.Nommer les plages pour mieux visualiser  .10
3.3.Introduction aux formules matricielles .. 11
3.3.1.Définition d'une matrice . 11
3.3.2.Définition d'une formule de matrice . 11
3.3.3.Fonction matriciel e SOMME .. 11
 fonction=SOMMEPROD() . 13
3.4.1.Fonction conditionnelle de SOMMEPROD()  13
3.4.2.Utilisation de la syntaxe Sommeprod(matrice1;matrice2) (facultatif) .. 13
3.4.3.Utilisation de la syntaxe: SOMMEPROD((Matricesouscondition)*matriceàsommer) .. 14
3.5.Sommer deux colonnes sous une condition . 15
3.5.1.Modification du tableau d'exercice  15
éation de 2 noms de plages  .. 15
3.5.3.Somme partielle par catégorie et par jour   16
3.5.4.Somme globale par catégorie . 16
. 17
4.1.C'est à boire qu'il nous faut  .. 17
4.1.1.Proposition de tableau de travail  17
4.1.2.Nommez les plages (dernier rappel)  17
 Fonction SOMMEPROD attribut par attribut 18
4.2.1.Fonctionnement de SOMMEPROD . 18
1.Comme compteur sous une condition . 18
2.Comme compteur à deux conditions .. 19
3.Comme somme sous deux conditions:  19
4.2.2.Syntaxe de SOMMEPROD()  20
1.Comme compteur sous une condition . 20
2.Comme compteur sous des conditions multiples .. 20
3.Comme somme sous conditions multiples  20
4.2.3.Travailler avec des valeurs numériques:  21
1.Comparaison avec une valeur . 21
2.Encadrement de deux valeurs  23
4.2.4.Additionner sous condition deux 2 données d'une même plage: .. 26
.27
5.1.SOMMEPROD et les premiers caractères d'une plage 27
5.1.1.Rappel : La Fonction GAUCHE . 27
5.1.2.Extraire suivant les premiers caractères  27
5.2.SOMMEPROD et les derniers caractères d'une plage .28
5.2.1.Rappel : La Fonction DROITE  28
5.2.2.Extraire suivant les derniers caractères . 28
5.2.3.SOMMEPROD et les Directeurs d'Ecole .. 30

2/31

Tutoriel Framasoft
1. INTRODUCTION
1.1. Licence de ce document.
L'auteur de ce document est Jean-Yves Lucca. 
Ce document est sous licence GNU F.D.L. (Licence de Documentation Libre GNU).Vous 
êtes libre de le reproduire, le recopier, le réutiliser, l'améliorer, le modifier et le 
distribuer à condition de lui attribuer les mêmes libertés.
1.2. Objectifs.
Réaliser des sommes sous un ou plusieurs critères  
1 Un petit rappel sur la fonction SOMME
? Désactiver l'AutoSaisie
2 SOMME sous une seule condition
? SOMM .
E S (I) pour information.
? Nommer les plages pour mieux appréhender (lire) les formules.
? Notion de matrice appliquée à la fonction SOMME pour mieux comprendre
? SOMMEPROD()
3 Sommer sous plusieurs conditions : la Fonction SOMMMEPROD
4 Comprendre le fonctionnement de SOMMEPROD() 
5 Des pistes de découvertes.

1.3. Typographie
Le vocabulaire employé par la feuille calque Ooo est signalé par les couleurs 
suivantes:
Nom d'une fenêtre d'Ooo comme "Définir des Noms" ou "Navigateur".
Icônes Bouton comme ">>Suivant" ou "Formulaire".
Onglet dans une fenêtre comme "Données" ou "Fonctions".
Liste de choix dans un cadre ouvert sous un onglet comme "Catégories" , "Format" 
etc.
Menu Format> Cellule > indique d'ouvrir un des menus de la barre des Menus.
Les données à taper au clavier sont indiquées sans quottes ( guillemets) en gras pour 
éviter les confusions avec les chaînes de caractères: Taper : Client   ,  "00000".
Aide .:Les raccourcis-clavier, les combinaisons de touches (appui simultané sur 
plusieurs touches et les touches du clavier n'ont pas de signe particulier . F4 est la 
touche Fonction, Maj est la touche Majuscule. Maj+F4 est une combinaison de 
touche.

Explicatifs 

3/31

Tutoriel Framasoft
2. Fonction Somme :le B.A.-BA
2.1. Désactiver l'AutoSaisie.
Lorsqu'il s'agit d'écrire dans le Champs de saisie de la Barre de formule,
Barre des Menus
Barre de fonctions
Barre d'objets
Barre de formule
    Champs  de  saisie 
ou Ligne de saisie
la complémentation automatique des textes ou des nombres peut être très gênante. 
Je vous conseille de la désactiver.
Aide: Complémentation automatique des textes ou nombres
Un élément (texte, nombre) existant déjà dans une colonne peut être repris dans une cellule de 
la même colonne au moment de la saisie :  Calc le propose automatiquement. 
Il s'agit de la fonction AutoSaisie.
L'AutoSaisie peut être activée (marque) ou désactivée (pas de marque) sous 
Menu> Outils> Contenu des cellules>AutoSaisie.
Nomenclature: Barre de formule = Barre de calcul ; Champs de saisie =ligne de saisie, etc.
Si la barre n'apparaît pas: Menu>Affichage>Barre d'outils>Cocher Barre de formule (tout en 
bas)

2.2. Des Sommes à calculer: Le problème posé
Je cherche à calculer pour cette journée, le 
Catégorie
Heures
nombre d'heures effectuées en catégorie 
P
1
Prestataire, le nombre d'heures effectuées en 
Matin
M
2
catégorie Mandataire et le Total des heures ainsi 
effectuées.
P
3
Après-midi
Pour ce B.A.-BA, P, M et P sont fixes.
Seules les heures sont variables dans le respect 
Prestataires (P)
des conventions collectives !
Mandataires(M)
Total des heures
Ce tableau peut être copié sur une feuille Calc en A1, il suffira de réajuster la largeur 
des colonnes puis de la copier dans les feuilles suivantes pour expérimenter les 
formules. Enregistrer votre classeur sous le nom qu'il vous plaira.
2.2.1. Somme de cellules non adjacentes 
La fonction SOMME est utilisée pour l'affichage dans une cellule choisie de la somme 
de plusieurs cellules. Sa syntaxe est :
=SOMME(Nombre1;Nombre2; )
Nombre1 à Nombre30 représentent de 1 à 30 arguments dont la somme est à 
calculer.

4/31

Tutoriel Framasoft
1.Comme les heures P ne sont pas 
adjacentes, écrire dans la cellule C6 où 
doit apparaître le résultat : =SOMME( 
Sélectionner ensuite à la souris la 
cellule C2 taper  ;  sélectionner 
ensuite à la souris la cellule C4 
fermer la parenthèse et.
valider avec la touche Entrée 
=SOMME(C2;C4)
On aurait pu écrire aussi:
=SOMME(C2+C4)
2.2.2. Somme de cellules adjacentes ou plage de cellules
=SOMME(Plage)
Plage représente toutes les cellules adjacentes figurant entre deux cellules extrêmes 
séparées par deux points :  
2.Comme les heures en M sont uniques, faites 
un lien entre C7 et C3 en tapant en C7 :
=C3
Valider avec la touche Entrée
3.La somme Total des heures peut être 
calculée avec les deux cellules adjacentes 
C6 et C7
Cliquer sur C8, 
Cliquer sur le 
bouton 
Grand Sigma ?de 
la barre de 
formule 
La plage de cellule C6:C7 s'affiche en 
contrasté entre parenthèses dans la formule
=SOMME(C6:C7)
Il suffit de valider avec la touche Entrée.
Pour calculer le Total des heures,on 
peut également utiliser les cellules 
C2+C3+C4
Choisir une cellule par exemple D8 , 
cliquer sur Grand Sigma ? ,il s'affiche
Sélectionner avec la souris la plage 
de cellules C2 à C4 et valider, ce qui 
donne la formule  =SOMME(C2:C4)

5/31

Tutoriel Framasoft
2.3. Copier-Glisser
Pour recopier une formule vers le bas:
Sélectionner la cellule à 
C'est une poignée. Placé sur la  Cliquer sur la poignée et faire 
copier. Le coin inférieur droit 
poignée, le curseur se 
glisser jusqu'à la cellule de fin 
arbore un petit carré noir
transforme en croix  +
de calcul. Valider par Entrée.
2.4. Références relatives ou absolues
La référence C1:C3 est une référence relative: Elle s'adapte automatiquement dès que 
l'on copie les formules.
Par opposition , la référence absolue $C$1:$C$3 est utilisée lorsqu'un calcul fait 
référence à une cellule spécifique de la feuille qui ne doit pas être adaptée lors de la 
copie de formule.
 $C1  La lettre de la colonne ne changera pas,le numéro de la ligne s'adaptera 
 C$1  La lettre de la colonne s'adaptera, le numéro de la ligne ne changera pas.
 $C$1 Ni .La lettre de la colonne ni le numéro de la ligne ne changeront.
Pour convertir la référence actuelle (dans laquelle le curseur est placé dans le champs 
de saisie) de relative en absolue et vice versa : il suffit d'appuyer simultanément sur 
(Maj)+(F4). Soit l'adresse relative commençant par C1 , le curseur entre C et 1: la 
première fois que vous appuyez sur ces deux touches, ligne et colonne deviennent 
absolues ($C$1) ; la seconde fois, seule la ligne devient absolue (C$1) ; la troisième 
fois, uniquement la colonne ($C1), et une quatrième action rend la référence 
relative dans les deux sens (C1).

3. Somme sous une condition
Je cherche à calculer pour cette journée, 
Catégorie
Heures
le nombre d'heures de chaque catégorie 
et le Total des d'heures. 
P
1
Matin
M
2
P et M sont variables  mais ne peuvent 
P
3
être triées car elles appartiennent à un 
Après-midi
document complexe à imprimer).
Prestataires (P)
Le tableau peut être copié sur une feuille Calc en  
Mandataires(M)
A1, il suffira de réajuster la largeur des colonnes
Total des heures

6/31

Tutoriel Framasoft
3.1. La fonction ()
Cette fonction permet d'additionner plusieurs cellules en fonction d'un critère (ou 
condition) déterminé. Sa syntaxe est:
(plage;critère;somme_plage)
plage est la plage de cellules contenant le critère à retenir
critère est LE critère à retenir (chiffre ou texte)
somme_plage est la plage de cellules où se trouvent les nombres à 
additionner.

Pour les Heures de Prestataires en C6
Je dois sommer la plage des heures que si la plage catégorie contient P
donc plage sous condition est la Catégorie soit B2:B5
critère est que la cellule contienne P soit "P" puisque c'est un 
caractère alphabétique( ou chaîne de caractères)
somme_plage est la plage des heures à sommer soit C2:C5

Remarque : Les deux plages doivent comporter le même nombre de cellules.
La formule est : 

(B2:B5;"P";C2:C5)
3.1.1. Utilisation de l'Assistant de fonctions (facultatif)
Cliquer en C6
La fenêtre Assistant Fonctions apparaît:
Sous l'onglet Fonctions,  Catégorie  Choisir 
Cliquer sur le bouton Assistant de Fonction  Toutes dans la liste déroulante.
(en fait c'est une fonction Mathématique)
Fonction  choisir    dans   la   liste 
déroulante.
Déplacer   légèrement   la   fenêtre   pour   pouvoir 
choisir les plages à l'aide de la souris.

7/31

Tutoriel Framasoft
Cliquer sur >>Suivant, remplir successivement les champs Plage ,Critères et 
Plage_somme (sans faire Entrée) et cliquer sur OK

8/31

Tutoriel Framasoft
La formule s'affiche 
(B2:B5;"P";C2:C5)
Pour les heures de Mandataires en C7
Le raisonnement est le même.
Je ne dois sommer la plage des heures que si la plage Catégorie contient M
 plage sous condition est celle de Catégorie soit toujours B2:B5
 critère est que la cellule contienne M soit "M"
 somme_plage est la plage des heures à sommer C2:C5
la formule est donc 
(B2:B5;"M";C2:C5)
Si vous utilisez de nouveau l'Assistant Fonction, 
désormais dans la fenêtre Assistant Fonctions,
Sous l'onglet Fonctions choisir dans la Catégorie Les 
dernières utilisées  
( il faut remonter l'ascenseur au 
plus haut à chaque fois, là pas d'amélioration dans la 
version 2.0)
puis choisir la Fonction  etc.
Voilà le résultat:
Note: Pour ce tutoriel, la formule employée est copiée-collée sur la cellule adjacente 
avec un espace vide devant le signe égal (=) afin de la visualiser. Cette aide 
n'apparaît donc pas sur votre feuille de calcul.
Vérifier en intervertissant  les P et M,.en ajoutant un P ou un M en fin d'après-midi en 
B5  et remplir C5.

9/31

Tutoriel Framasoft
3.2. Nommer les plages pour mieux visualiser 
Pour éviter d'utiliser les 
références absolues,dans 
les glisser-copier de 
formules, donnons un NOM 
aux différentes plages.
Menu>Insertion>Noms > 
Définir
(ou Ctrl+F3 )
Pour ce tutoriel, nous 
utiliserons 
systématiquement les 
noms des plages afin de 
mieux visualiser les 
formules. 
Celles-ci seront 
surlignées en gris clair 
Il sera toujours possible 
de remplacer un nom 
par la référence relative 
ou absolue 
correspondante.
Dans la fenêtre Définir des Noms , 
taper le nom désiré : Catégorie
Avec la souris, sélectionner la plage 
des cellules à nommer : B2:B5
Ne pas s'inquiéter, le programme 
affiche aussi le nom de la feuille de 
calcul et automatiquement les 
références absolues.
Cliquer sur le bouton Ajouter
Dans la fenêtre Définir des Noms , 
taper le nom désiré : Heures
Avec la souris, sélectionner la plage 
des cellules à nommer : C2:C5
Cliquer sur le bouton OK

10/31

Tutoriel Framasoft
3.3. Introduction aux formules matricielles
3.3.1. Définition d'une matrice
Dans une feuille de calcul, une matrice est une plage de cellules liées contenant des 
valeurs, 
La plage de cellules 
La matrice la plus petite qui puisse 
comme B2:B5 est une 
Une plage carrée composée 
matrice.
de 3 lignes et de 3 colonnes 
exister est la matrice 1 x 2 ou 2 x 
1. Elle est constituée de deux 
La plage C2:C5 des heures  est une matrice 3 x 3 :
en est une autre.
cellules adjacentes.
3.3.2. Définition d'une formule de matrice
Il s'agit d'une formule permettant d'évaluer les différentes valeurs d'une plage de 
cellules. Une formule de matrice se différencie des autres formules par le fait que, 
contrairement à celles-ci, elle est capable de traiter plusieurs valeurs à la fois.
De plus, elle peut renvoyer plusieurs valeurs. Le résultat d'une formule de matrice est 
une autre matrice.
3.3.3. Fonction matricielle SOMME
Explicatif: Je compare chaque cellule de la matrice Catégorie à "P"
Le résultat de la comparaison est 1 si le critère est satisfait, et 0 s'il ne l'est 
pas.
Les résultats de la comparaison forment une matrice {E2:E5} qui est utilisée 
dans une multiplication avec la matrice des Heures 
Ce produit scalaire forme la matrice résultat de la multiplication {H2:H5}.
L'addition de ces valeurs donne le nombre d'heures recherchées. 
La syntaxe de cette fonction est:
SOMME((matrice=critère)*matrice à sommer)

11/31

Tutoriel Framasoft
1.Pour les heures de Prestataires en C6,
la forme devient : SOMME((matrice de Catégorie="P")*matrice des Heures)
Taper en C6 la formule entièrement à la main en faisant attention à la double 
parenthèse après SOMME qui est imposée par la parenthèse qui sépare la 
quotte" du signe *
=SOMME((Catégorie="P")*Heures)
ou   =SOMME((B2:B5="P")*C2:C5)
Pour que la formule soit bien interprétée comme une formule de matrice, 
vous devez la fermer en appuyant sur les touches Maj +Ctrl+Entrée

2.Pour les heures de Mandataires en C7,
la forme devient : SOMME((matrice de Catégorie="M")*matrice des Heures)
Taper en C7 la formule entièrement à la main en faisant très attention aux 
parenthèse et aux quottes.
=SOMME((Catégorie="M")*Heures)
ou        =SOMME((B2:B5="M")*C2:C5)
Valider par  Maj +Ctrl+Entrée
Si vous vous trompez, effacez et recommencez.
L'édition et la modification des formules matricielles est expliquée dans l'aide de Ooo Calc :
Menu>Aide>Sous l'onglet Index : rechercher Taper Matrice;Fonctions : c'est le 6ème sous-titre.
Ce n'est jamais évident ce qui explique mon conseil ci-dessus.
Attention à l'endroit où se trouve votre curseur quand vous cliquez lorsque 
vous êtes en train d'écrire une formule dans le Champs de saisie de la Barre 
des formules.
Toute écriture manuelle est un exercice d'attention sur les quottes et le 
nombre de parenthèses.
Avec les fonctions matricielles, le réflexe du Maj +Ctrl+Entrée s'impose.
Le logiciel rajoute automatiquement les {   } pour indiquer que la formule est 
matricielle , n'essayez pas d'écrire manuellement les { }.

12/31

Tutoriel Framasoft
3.4. La fonction=SOMMEPROD()
Découvrons la fonction SOMMEPROD() appliquée à notre problème à une seule 
condition.
Comme la fonction , elle appartient à la catégorie Mathématique, mais il ne 
faut pas utiliser l'Assistant de fonction car celui-ci ne considère que les attributs de la 
fonction sous la syntaxe SOMMEPROD(matrice1;matrice2;matrice3; .)
alors que nous allons utiliser cette fonction avec des attributs conditionnels.
3.4.1. Fonction conditionnelle de SOMMEPROD()
Pour montrer l'analogie avec . sa syntaxe est:.
=SOMMEPROD(plage=critère;somme_plage) ou
=SOMMEPROD((plage=critère)*somme_plage)

plage est la plage de cellule contenant le critère à retenir
critère est LE critère à retenir (chiffre ou texte)
somme_plage est la plage de cellule où se trouvent les nombres à 
additionner.

En attendant que les « Aides » fixent leur syntaxe (aussi bien Excel que 0oo, 
je préférerais en fait cette syntaxe très personnelle:
=SOMMEPROD(matrice=critère;matriceàsommer)
ou celle ci équivalente qui est la plus utile:
=SOMMEPROD((matrice=critère)*matriceàsommer) 
car le raisonnement s'inspire des fonctions matricielles : la matrice sous 
condition est comparée au critère, cellule après cellule, et donne soit 1 (VRAI) 
soit 0 (FAUX) puis est multiplié matriciellement par la matrice des valeurs à 
additionner.
OpenOffice en version 1.1.4 ou 1.1.5 ne l'acceptait que si on la considérait 
comme fonction matricielle (validation Maj+Ctrl+Entrée), mais dans la version 
2.0, ceci est réparé! Donc taper simplement la touche Entrée pour valider.
Je vous propose cet autre syntaxe qui peut satisfaire certains si le terme 
critère vous gêne:
=SOMMEPROD(plage=cond)*(plageàsommer)
3.4.2. Utilisation de la syntaxe Sommeprod(matrice1;matrice2) 
(facultatif)

avec le signe point virgule; entre les deux attributs.
Pour les Heures de Prestataires en C6:
matrice sous condition est la plage de la Catégorie : B2:B5="P" ou Catégorie="P"
matrice à sommer est la plage des Heures : C2:C5 ou Heures
Taper en C6,
=SOMMEPROD(Catégorie="P";Heures)
Valider par la touche Entrée

13/31

Tutoriel Framasoft
Notez que le signe ; est un séparateur donc pas de parenthèse entre quotte et la suite.
?Pour les Heures de Mandataires, en C7
matrice sous condition est la plage de la Catégorie : B2:B5="M" ou Catégorie ="M"
matrice à sommer est la plage des Heures : C2:C5 ou Heures
Cliquer C6, faites un glisser-coller vers le bas  pour remplir la cellule C7, et valider par 
Entrée.
Dans le Champs de saisie de la Barre de formules , remplacer P par M et valider.
=SOMMEPROD(Catégorie="M";Heures)
3.4.3. Utilisation de la syntaxe: 
SOMMEPROD((Matricesouscondition)*matriceàsommer).

Avec le signe * : Cette écriture sera utilisée par la suite pour les conditions multiples.
Mais attention aux deux parenthèses indispensables puisqu'il faut en mettre une pour 
séparer la deuxième quotte "du signe *.
Pour les Heures de Prestataires, en C6
Taper dans le Champs de saisie de la Barre de formule
Attention aux deux parenthèses indispensables puisqu'il faut en mettre une pour 
séparer la deuxième quotte "du signe *.
=SOMMEPROD((Catégorie="P")*Heures)
Pour les heures de Mandataires, en C7
Cliquer C6, faites un glisser-coller vers le bas pour remplir la cellule C7, et valider par 
Entrée.
Dans le Champs de saisie de la Barre de formule , remplacer P par M et Valider.
=SOMMEPROD((Catégorie="M")*Heures)

14/31

Tutoriel Framasoft
3.5. Sommer deux colonnes sous une condition
Si je veux sommer deux colonnes par exemple les heures de lundi et de mardi, la 
matrice à sommer doit être la somme des deux matrices des heures du lundi et du 
mardi.
La matrice à sommer s'écrit (matrice1à sommer+matrice2à sommer).
La formule prend  cette syntaxe:
=SOMMEPROD((matrice=critère)*(matrice1àsommer+matrice2àsommer))
3.5.1. Modification du tableau d'exercice
Modifiez en C1 Heures en Heures lundi
Heures  Heures 
en D1 tapez Heures mardi,
Catégorie
lundi
mardi
taper un P en B5 et compléter les cellules des heure 
P
1
2
suivant l'exemple:
Matin
M
2
1
Pour reformater les cellules de la ligne 1:
Après-
P
3
2
Sélectionner la plage A1:D1
midi
P
1
Menu>Format>Cellules >
Prestataires (P)
Fenêtre Formatage des cellules
Onglet Alignement 

Mandataires(M)
>Alignement du texte>
Horizontal:Centré
Total des heures
Vertical: Milieu
  Propriété  > Cocher Renvoi à la ligne automatique 
OK

Prestataire (P)
Mandataire (M)
3.5.2. Création de 2 noms de plages 
Nous   allons   créer   2   nouveaux   noms   : 
Heureslundi   pour   la   plage   C2:C5   et 
Heuresmardi pour la plage D2:D5
Menu>Insertion>Noms>Définir
Dans la fenêtre Définir des noms,
laissons Heures et tapons Heureslundi
sélectionnons CE2:C5 à la souris,
Valider avec Ajouter
Tapons Heuresmardi
et sélectionnons D2:D5 
Valider avec OK
Les plages assignées aux noms sont 
immédiatement transposées en références 
absolues.
Avec ces nouveaux noms, écrivons les 
formules partielles comme en 3.4.3

15/31

Tutoriel Framasoft
3.5.3. Somme partielle par catégorie et par jour 
Pour les Prestataire du lundi en C6,
Taper la formule puis Valider par Entrée
=SOMMEPROD((Catégorie="P")*Heureslundi)
Pour les Mandataires du lundi en C7,
Cliquer C6 et copier-glisser vers le bas en C7, Valider.
Remplacer le P par le M. Valider
=SOMMEPROD((Catégorie="M")*Heureslundi)
Pour les Prestataires du mardi en D6,
Cliquer. C6 et copier-glisser à droite en D6 et Valider.
Remplacer heureslundi par Heuresmardi et Valider.
=SOMMEPROD((Catégorie="P")*Heuresmardi)
Pour les Mandataires du mardi en D7,
Cliquer D6 et copier-glisser vers le bas en D7 et Valider.
Remplacer le P par le M. et Valider.
=SOMMEPROD((Catégorie="P")*Heuresmardi)
3.5.4. Somme globale par catégorie
Nous pouvons faire la somme totale des heures de lundi et mardi suivant les 
catégories sans utiliser les résultats partiels:
Pour le Total Prestataire en C10,
la forme est SOMMEPROD((matrice catégorie="P")*(matrice des heures du 
lundi + matrice des heures du mardi)) et la formule est 
=SOMMEPROD((Catégorie="P")*(Heureslundi+heuresmardi)) 
Pour le Total Mandataire en C11,
Cliquer C10, copier-glisser vers le bas en C11 et Valider
Changer le P en M et Valider.
=SOMMEPROD((Catégorie="M")*(Heureslundi+heuresmardi)) 

16/31

Tutoriel Framasoft
4. Comprendre la Fonction SOMMEPROD : 
Revenons à la fonction SOMMEPROD() avec un autre exemple pour mieux comprendre 
son fonctionnement.
4.1. C'est à boire qu'il nous faut 
4.1.1. Proposition de tableau de travail
Client
Mois
Floc Blanc
Floc Rosé
Cave Antibes
Mars
24
12
Cave Auch
Mars
36
24
Durand
Mars
12
12
Cave Antibes
Mars
60
36
Cave Marciac
Mai
36
24
Martin
Mai
60
60
Durand
Mai
24
12
Cave Auch
Mai
24
12
Martin
Juin
12
12
Cave Antibes
Juin
36
24
Durand
Juin
60
36
Cave Auch
Juin
24
12
 Vous pouvez le copier sur une feuille calque en A1
4.1.2. Nommez les plages (dernier rappel)
 Pour nommer les plages : Ctrl+F3 ou
 Menu>Insertion>Noms>Définir

17/31

Tutoriel Framasoft
Dans la fenêtre Définir des noms,
taper Client
sélectionner A2:A13  à la souris,
Valider avec Ajouter, et de même pour les 3 
autres noms.
Mois pour la plage B2:B13
Blanc pour la plage C2:C13
Rosé pour la plage D2:D13
Valider avec le bouton  OK
Ouvrir le navigateur 
dans la barre de 
fonctions
et vous avez vos Noms de plage
4.2. La Fonction SOMMEPROD attribut par attribut.
4.2.1. Fonctionnement de SOMMEPROD
1.C
  omme compteur sous une condition
Appliquons la formule avec son premier attribut:
=SOMMEPROD(plage=critère)
I. Par exemple aux ventes Client de la Cave 
d'Antibes 
soit
Résultat de la 
=SOMMEPROD(Client="Cave Antibes")
Client
Condition Cave 
Antibes {1}
 La fonction renvoie 3
Cave Antibes
1
Explicatif : Je compare chaque cellule de la plage Client à 
Cave Auch
0
"Cave Antibes"
Durand
0
Le résultat de la comparaison est 1 si le critère est satisfait, 
Cave Antibes
1
et 0 s'il ne l'est pas.
Cave Marciac
0
J'obtiens la plage « Résultat de la Condition »)  dont la 
Martin
0
somme est3.
Durand
0
Cave Auch
0
Note1 : Les valeurs de {1} sont obtenues avec la 
Martin
0
formule =(A2="Cave Antibes")*1   Le *1 renvoie 1 
si VRAI et 0 si FAUX 
Cave Antibes
1
Note 2: La fonction joue ici le même rôle que 
Durand
0
(Client;"Cave Antibes") 
Cave Auch
0
Somme
3

18/31

Tutoriel Framasoft
II. Si je m'intéresse aux ventes 
Résultat  Résultat 
du mois de Mars,
de la 
de la 
Produit 
=SOMMEPROD(plage=critère)
Condition 
Client
Mois
Condition  scalaire 
Cave 
=SOMMEPROD(Client="Mars")
Mars
{1}*{2}
Antibes 
{2}
{1}
la fonction renvoie 4
Cave Antibes Mars
1
1
1
Cave Auch
Mars
0
1
0
Explicatif: Je compare chaque cellule de la 
Durand
Mars
0
1
0
matrice mois à "Mars"
Cave Antibes Mars
1
1
1
Le résultat de la comparaison est 1 si le 
Cave Marciac Mai
0
0
0
critère est satisfait, et 0 s'il ne l'est pas. 
Martin
Mai
0
0
0
J'obtiens la matrice « Résultat Condition 
Mars {2} »dont la somme est 4.
Durand
Mai
0
0
0
Cave Auch
Mai
0
0
0
2.C
  omme compteur à deux 
Martin
Juin
0
0
0
conditions.
Cave Antibes Juin
1
0
0
 En recherchant la double 
Durand
Juin
0
0
0
condition: nombre de ventes à 
Cave Auch
Juin
0
0
0
Antibes et en Mars:
Somme
3
4
2
Je fais le produit matriciel: Je multiplie, cellule par cellule, les 2 matrices résultats {1] *{2} et 
fais la somme du résultat qui est 2.
=SOMMEPROD((Client="Cave Antibes")*(Mois="Mars")
La fonction renvoie 2
3.C

  omme somme sous deux conditions:
Et si je multiplie les deux matrices  {1}*{2} par le nombre de bouteilles de Floc Blanc,cellule 
par cellule, et en fais la somme, j'obtiens le nombre de bouteilles vendues en mars à la Cave 
d'Antibes.
Résultat  Résultat 
de la 
de la 
Produit 
Produit 
Condition 
Floc 
Client
Mois
Condition  scalaire 
scalaire 
Cave 
Blanc
Mars
{1}*{2}
{1}*{2}*Blanc
Antibes 
{2}
{1}
Cave Antibes Mars
1
1
1
24
24
Cave Auch
Mars
0
1
0
36
0
Durand
Mars
0
1
0
12
0
Cave Antibes Mars
1
1
1
60
60
Cave Marciac Mai
0
0
0
36
0
Martin
Mai
0
0
0
60
0
Durand
Mai
0
0
0
24
0
Cave Auch
Mai
0
0
0
24
0
Martin
Juin
0
0
0
12
0
Cave Antibes Juin
1
0
0
36
0
Durand
Juin
0
0
0
60
0
Cave Auch
Juin
0
0
0
24
0
Somme
3
4
2
84
 =SOMMEPROD((Client="Cave Antibes")*(Mois="Mars")*Blanc)

19/31

Tutoriel Framasoft
Voici la copie d'écran de cette recherche:
Bien sûr, ces formules fonctionnent avec les références des plages à la place de leur 
nom:

Nombre de Ventes Antibes
3  =SOMMEPROD(A2:B13="Cave Antibes")
Nombre de Ventes Mars
4  =SOMMEPROD(B2:B13="Mars")
Nombre de ventes Antibes en mars?
2  =SOMMEPROD((A2:A13="Cave Antibes")*(B2:B13="Mars"))
Quantité de Floc Blanc d'Antibes en mars ?
84  =SOMMEPROD((A2:A13="Cave Antibes")*(B2:B13="Mars")*C2:C13)
4.2.2. Syntaxe de SOMMEPROD()
La syntaxe (toujours très personnelle) est :
1.C
  omme compteur sous une condition
=SOMMEPROD((plage=critère)
2.C
  omme compteur sous des conditions multiples
=SOMMEPROD((plage=critère1)*(plage=critère2)*(plage=critère3)
3.C
  omme somme sous conditions multiples
=SOMMEPROD((plage=critère1)*(plage=critère2)*(plage=critère3)*(plageàsommer)
plage est la plage de cellule (ou la matrice)contenant le critère à retenir
critère1 est LE critère à retenir (chiffre ou texte)dans cette même plage.

plageàsommer  est la plage de cellules (ou matrice) où se trouvent les 
nombres à additionner.

20/31

Tutoriel Framasoft
4.2.3. Travailler avec des valeurs numériques:
1.C
  omparaison avec une valeur.
Compter le nombre de fois où les ventes de Floc Blanc sont 
supérieures ou égales à 36 bouteilles

Résultat 
de la 
Produit 
Floc 
Client
Mois
Condition  scalaire 
Blanc
>=36
{1}*Blanc
Explicatif: Je compare chaque 
{1}
cellule de la matrice Blanc à >=36
Cave Antibes Mars
24
0
0
Le résultat de la comparaison est 1 
si le critère est satisfait, et 0 s'il ne 
Cave Auch
Mars
36
1
36
l'est pas. J'obtiens la matrice 
Durand
Mars
12
0
0
« Résultat Condition >=36 
Cave Antibes Mars
60
1
60
{1} »dont la somme est 6.
Cave Marciac Mai
36
1
36
Martin
Mai
60
1
60
Je multiplie {1} par le nombre de 
Durand
Mai
24
0
0
bouteilles de Floc Blanc,cellule par 
Cave Auch
Mai
24
0
0
cellule.
Martin
Juin
12
0
0
J'en fais la somme, j'obtiens le 
Cave Antibes Juin
36
1
36
nombre de bouteilles vendues sous 
Durand
Juin
60
1
60
cette condition.
Cave Auch
Juin
24
0
0
Somme
6
288
Le compteur SOMMEPROD est
=SOMMEPROD((plage=critère)
=SOMMEPROD(Blanc>=36)
 ou =SOMMEPROD(C2:C13>=36)
et renvoie 6
La quantité de Floc Blanc sous cette condition est le produit du compteur avec la 
plage à sommer Blanc:
=SOMMEPROD((plage=critère)*(plageàsommer)
=SOMMEPROD((Blanc>=36)*Blanc)
 ou  =SOMMEPROD((C2:C13>=36)*C2:C13)
renvoie 288.

21/31

Tutoriel Framasoft
Compter le nombre de fois où les ventes de Floc Blanc sont 
supérieures ou égales à 36 bouteilles avec un deuxième critère, 
comme celui du mois de Mars:

Explicatif: Je compare chaque 
Résultat  Résultat 
cellule de la matrice mois à 
de la 
de la 
Floc 
{1}*{2} 
"Mars"
Client
Mois
Condition  Condition  {1}*{2}
Blanc
*{Blanc}
Le résultat de la comparaison 
>=36
Mars
est 1 si le critère est satisfait, 
{1}
{2}
et 0 s'il ne l'est pas. J'obtiens 
Cave Antibes Mars
24
0
1
0
0
la matrice « Résultat 
Cave Auch
Mars
36
1
1
1
36
Condition Mars {2}.
Durand
Mars
12
0
1
0
0
Cave Antibes Mars
60
1
1
1
60
Je multiplie {1} par {2}, 
Cave Marciac Mai
36
1
0
0
0
cellule par cellule.
Martin
Mai
60
1
0
0
0
Je multiplie le résultat{1} 
Durand
Mai
24
0
0
0
0
*{2} par le nombre de 
bouteilles de blanc ligne par 
Cave Auch
Mai
24
0
0
0
0
ligne.
Martin
Juin
12
0
0
0
0
J'en fais la somme, j'obtiens 
Cave Antibes Juin
36
1
0
0
0
le nombre de bouteilles 
Durand
Juin
60
1
0
0
0
vendues sous ces deux 
Cave Auch
Juin
24
0
0
0
0
conditions.
Somme
6
4
2
96
=SOMMEPROD((plage=critère1)*(plage=critère2)
=SOMMEPROD((Blanc>=36)*Blanc)*(Mois="Mars")
renvoie 2
La quantité de Floc Blanc sous  ces deux conditions est 
=SOMMEPROD((plage=critère1)*(plage=critère2)*(plageàsommer)
=SOMMEPROD((Blanc>=36)*Blanc)*(Mois="Mars")*Blanc)
et renvoie 96
Nombre de ventes de floc blanc supérieure ou égale  à 36 ?
6  =SOMMEPROD(Blanc>=36)
Quantité de floc blanc supérieureou égale à 36 ?
288  =SOMMEPROD((Blanc>=36)*Blanc)
Nombre de vente de floc blanc > ou = à 36 en mars ?
2  =SOMMEPROD((Blanc>=36)*(Mois="Mars"))
Quantité de floc blanc> ou = à 36 en mars ?
96  =SOMMEPROD((Blanc>=36)*(Mois="Mars")*Blanc)

22/31

Tutoriel Framasoft
2.En
   cadrement de deux valeurs.
Compter le nombre de ventes comprises entre deux valeurs >12 mais 
< 60   

Résultat  Résultat 
de la 
de la 
Produit 
Floc 
Client
Mois
Condition  Condition  {2}*{3}
scalaire 
Blanc
>12
<60
{2}*{3}*Blanc
{2}
{3}
Cave Antibes Mars
24
1
1
1
24
Cave Auch
Mars
36
1
1
1
36
Durand
Mars
12
0
1
0
0
Cave Antibes Mars
60
1
0
0
0
Explicatif: Même 
Cave Marciac Mai
36
1
1
1
36
raisonnement
Martin
Mai
60
1
0
0
0
Durand
Mai
24
1
1
1
24
Cave Auch
Mai
24
1
1
1
24
Martin
Juin
12
0
1
0
0
Cave Antibes Juin
36
1
1
1
36
Durand
Juin
60
1
0
0
0
Cave Auch
Juin
24
1
1
1
24
Somme
10
9
7
204
 
Le compteur:
=SOMMEPROD((plage=critère1)*(plage=critère2)
=SOMMEPROD((Blanc>12)*(Blanc<60)
 ou 
=SOMMEPROD((C2:C13>12)*(C2:C13<60) 
renvoie 7
La quantité de Floc Blanc sous deux conditions est 
=SOMMEPROD((plage=critère1)*(plage=critère2)*(plageàsommer)
 ou 
=SOMMEPROD((C2:C13>12)*(C2:C13<60)*C2:C13)
renvoie 204
Nb. de commandes Blanc supérieur à 12 et inférieur à 60
7  =SOMMEPROD((Blanc>12)*(Blanc<60))
Quantité de Blanc supérieur à 12 et inférieur à 60
204  =SOMMEPROD((Blanc>12)*(Blanc<60)*Blanc)

23/31

Tutoriel Framasoft
Compter le nombre de ventes comprises entre deux valeurs >=24 
mais < =60

Le compteur:
=SOMMEPROD((plage=critère1)*(plage=critère2)
=SOMMEPROD((Blanc>=24)*(Blanc=<60)
Pour ceux qui sont arrivés jusqu'ici, je voudrais donner une autre piste de la fonction 
SOMMPROD appliquée à ce cas
Considérons cet encadrement suivant le schéma suivant:
A boire vite! est égal aux Blancs <=60 diminué des Blancs inférieur à 24:
(Blanc=<60)
60
(Blanc<24)
24
À boire , vite !
et essayer cette formule avec le signe moins -
=SOMMEPROD((Blanc<=60)-(Blanc<24))
Naturellement cela donne la même valeur mais il faut aimer soustraire !
Résultat  Résultat  Résultat 
de la 
de la 
de la 
Produit 
Produit 
Floc 
Client
Mois
Condition  Condition  Condition  {1}*{2} {1}-{3}
scalaire 
scalaire
Blanc
<=60
>=24
<24
{1}*{2}*Blanc {1}-{3}*Blanc
{1}
{2}
{3}
Cave Antibes Mars
24
1
1
0
1
1
24
24
Cave Auch
Mars
36
1
1
0
1
1
36
36
Durand
Mars
12
1
0
1
0
0
0
0
Cave Antibes Mars
60
1
1
0
1
1
60
60
Cave Marciac Mai
36
1
1
0
1
1
36
36
Martin
Mai
60
1
1
0
1
1
60
60
Durand
Mai
24
1
1
0
1
1
24
24
Cave Auch
Mai
24
1
1
0
1
1
24
24
Martin
Juin
12
1
0
1
0
0
0
0
Cave Antibes Juin
36
1
1
0
1
1
36
36
Durand
Juin
60
1
1
0
1
1
60
60
Cave Auch
Juin
24
1
1
0
1
1
24
24
Somme
12
10
2
10
10
384
384
Blanc compris entre 24 inclus et 60 inclus
10  =SOMMEPROD((Blanc<=60)*(Blanc>=24))
Blanc compris entre 24 inclus et 60 inclus
10  =SOMMEPROD((Blanc<=60)-(Blanc<24))
Et attention aux parenthèses pour la quantité de bouteilles !
=SOMMEPROD((Blanc>=12)*(Blanc=<60)*Blanc)
=SOMMEPROD(((Blanc<=60)-(Blanc<24))*Blanc)
Quantité de Blanc compris entre 24 inclus et 60 inclus
384  =SOMMEPROD((Blanc<=60)*(Blanc>=24)*Blanc)
Quantité de Blanc compris entre 24 inclus et 60 inclus
384  =SOMMEPROD(((Blanc<=60)-(Blanc<24))*Blanc)

24/31

Tutoriel Framasoft
Compter le nombre de ventes inférieures à 24 ET supérieures à 48
Explicatif: Même 
Résultat  Résultat 
raisonnement pour 
de la 
de la 
Floc 
Produit scalaire 
{1}et{2}.
Client
Mois
Condition  Condition  {1}+{2}
Blanc
({1}+{2})*Blanc
Le produit {1}* 
<24
>48
{2} rend 0
{1}
{2}
Je réalise un test 
Cave Antibes Mars
24
0
0
0
0
logique « OU » : Je 
Cave Auch
Mars
36
0
0
0
0
fais l'addition des 
Durand
Mars
12
1
0
1
12
deux résultats 
Cave Antibes Mars
60
0
1
1
60
ligne par ligne, 
Cave Marciac Mai
36
0
0
0
0
c.à.d. quand l'une 
Martin
Mai
60
0
1
1
60
ou l'autre des 
Durand
Mai
24
0
0
0
0
expressions est 
Cave Auch
Mai
24
0
0
0
0
VRAIE (=1).
Martin
Juin
12
1
0
1
12
Ce qui se traduit 
Cave Antibes Juin
36
0
0
0
0
par l'addition des 
deux matrices 
Durand
Juin
60
0
1
1
60
{1}+{2}.
Cave Auch
Juin
24
0
0
0
0
Somme
2
3
5
204
Le compteur:
=SOMMEPROD((plage1=critère1)+(plage1=critère2))
La fonction devient
=SOMMEPROD((Blanc<24)+(Blanc>48))
ou
=SOMMEPROD((C2:C13<24)+(C2:C13>48))
renvoie  5
=SOMMEPROD(((Blanc<24)+(Blanc>48))*Blanc)
ou
=SOMMEPROD(((C2:C13<24)+(C2:C13>48))*C2:C13)
renvoie effectivement 204
Nb. de commandes Blanc inférieur à 24 et supérieur à 48
5  =SOMMEPROD((Blanc<24)+(Blanc>48))
Quantité de Blanc  inférieur à 24 et supérieur à 48
204  =SOMMEPROD(((Blanc<24)+(Blanc>48))*Blanc)

25/31

Tutoriel Framasoft
4.2.4. Additionner sous condition deux 2 données d'une même 
plage:

Compter le nombre des ventes pour Durand et Martin 
Explicatif: Je compare 
Résultat  Résultat 
chaque cellule de la 
de la 
de la 
Produit 
matrice Client à 
Floc 
Client
Mois
Condition  Condition  {1}+{2}
scalaire 
"Durand"
Blanc
Le résultat de la 
Durand
Martin
{1}+{2}*Blanc
comparaison est 1 si le 
{1} 
{2} 
critère est satisfait, et 0 
s'il ne l'est pas. J'obtiens 
Cave Antibes Mars
24
0
0
0
0
la matrice « Résultat 
Cave Auch
Mars
36
0
0
0
0
Condition Durand {1} »
Durand
Mars
12
1
0
1
12
Je compare chaque 
Cave Antibes Mars
60
0
0
0
0
cellule de la matrice 
Client à "Martin" 
Cave Marciac Mai
36
0
0
0
0
J'obtiens la matrice {2} 
Martin
Mai
60
0
1
1
60
Je réalise un test 
Durand
Mai
24
1
0
1
24
logique « OU » : 
addition des deux 
Cave Auch
Mai
24
0
0
0
0
résultats ligne par ligne, 
Martin
Juin
12
0
1
1
12
c.à.d. quand l'une ou 
Cave Antibes Juin
36
0
0
0
0
l'autre des expres- sions 
est VRAIE (=1).
Durand
Juin
60
1
0
1
60
Ce qui se traduit  par 
Cave Auch
Juin
24
0
0
0
0
l'addition des deux 
Somme
3
2
5
168
matrices {1}+{2}.
La formule est donc traduite par un + entre les attributs.
Le compteur:
=SOMMEPROD((plage1=critère1)+(plage1=critère2))
La formule:
=SOMMEPROD((Client="Durand")+(Client="Martin")) 
ou
=SOMMEPROD((A2:A13="Durand")+(A2:A13="Martin"))
et le nombre de leurs achats de Floc blanc se traduit par:
=SOMMEPROD((plage=critère1)+(plage=critère2))*(plageàsommer)
=SOMMEPROD(((Client="Durand")+(Client="Martin"))*Blanc) 
ou
=SOMMEPROD(((A2:A13="Durand")+(A2:A13="Martin"))*Blanc)
3  =SOMMEPROD(Client="Durand")
2  =SOMMEPROD(Client="Martin")
5  =SOMMEPROD((Client="Durand")+(Client="Martin"))
168  =SOMMEPROD(((Client="Durand")+(Client="Martin"))*Blanc)

26/31

Tutoriel Framasoft
5. SOMMEPROD et les Chaînes de caractères
Je cherche dans le même ordre d'idée, les ventes pour les Caves Vinicoles. Il y en a 
quatre; je pourrais utiliser la formule précédente avec l'addition des 4 plages sous 
critère. Il y a plus simple en utilisant les fonctions de traitement des chaînes de 
caractères.
5.1. SOMMEPROD et les premiers caractères d'une plage
5.1.1. Rappel : La Fonction GAUCHE
Renvoie le ou les premiers caractères d'une chaîne de texte.
Syntaxe
GAUCHE(texte;no_caractère)
Texte est le texte dans lequel les mots partiels initiaux sont déterminés.
Nombre (facultatif) indique le nombre de caractères du texte de départ. Si ce 
paramètre n'est pas défini, un seul caractère est renvoyé.
Exemple : Gauche("Cave Antibes";2) renvoie Ca.
5.1.2. Extraire suivant les premiers caractères
Travailler avec les seules Caves:
Résultat 
 =Gauche  Compare 
de la 
Explicatif: J'applique la fonction 
GAUCHE à la matrice Client avec 4 
Client
(Client;4)
{1} à 
Condition 
caractères ligne par ligne qui me 
{1}
« Cave » 
Cave
rend la matrice {1}
{2}
Je compare chaque cellule de la 
Cave Antibes Cave
VRAI
1
matrice {1} à la chaîne de caractère 
"Cave" 
Cave Auch
Cave
VRAI
1
On obtient la matrice avec ses VRAI 
Durand
Dura
FAUX
0
et FAUX qui se visualise mieux.
Cave Antibes Cave
VRAI
1
Je traduis: chaque résultat de la 
comparaison est 1 si le critère est 
Cave Marciac Cave
VRAI
1
satisfait, et 0 s'il ne l'est pas. 
Martin
Mart
FAUX
0
Durand
Dura
FAUX
0
J'obtiens la matrice « Résultat de la 
Cave Auch
Cave
VRAI
1
Condition Cave{2} »dont la somme 
est 7.
Martin
Mart
FAUX
0
Cave Antibes Cave
VRAI
1
En fait, on remplace la matrice 
Durand
Dura
FAUX
0
Client par la matrice 
Cave Auch
Cave
VRAI
1
GAUCHE(Client;4)
Somme 
7
Le compteur conditionnel est donc=
=SOMMEPROD(GAUCHE(Client;4)="Cave")

27/31

Tutoriel Framasoft
Il ne reste plus qu'à appliquer les autres conditions et les plages à sommer suivant la syntaxe 
connue.
Client
Mois
Floc Blanc
Floc Rosé
Plages nommées
Cave Antibes
Mars
24
12
Client =A2:A13
Cave Auch
Mars
36
24
Mois=B2:B13
Durand
Mars
12
12
Blanc=C2:C13
Cave Antibes
Mars
60
36
Rosé=D2:D13
Cave Marciac
Mai
36
24
Martin
Mai
60
60
Durand
Mai
24
12
Cave Auch
Mai
24
12
Martin
Juin
12
12
Cave Antibes
Juin
36
24
Durand
Juin
60
36
Cave Auch
Juin
24
12
Pour les caves
Formule
donne
Le nombre de caves
=SOMMEPROD(GAUCHE(Client;4)="Cave")
7
Nb de ventes de Blanc
=SOMMEPROD((GAUCHE(Client;4)="Cave")*Blanc)
240
Nb de ventes de Rosé
=SOMMEPROD((GAUCHE(Client;4)="Cave")*Rosé)
144
Nb de ventes Blanc et Rosé
=SOMMEPROD((GAUCHE(Client;4)="Cave")*(Blanc+Rosé
384
))
NB de vente de Blanc en mars =SOMMEPROD((GAUCHE(Client;4)="Cave")*(Mois="Mars
120
")*(Blanc))
Etc.
5.2. SOMMEPROD et les derniers caractères d'une plage
Travailler avec les noms des caves en utilisant la partie droite des données d'une 
plage.
5.2.1. Rappel : La Fonction DROITE
Définit le ou les derniers caractères d'une chaîne de texte.
Syntaxe
DROITE(texte;no_caractère)
Texte est le texte dont la partie de droite doit être déterminée.
Nombre (facultatif) est le nombre de caractères de gauche.
Exemple
DROITE("Antibes";3) renvoie bes.
5.2.2. Extraire suivant les derniers caractères
Travailler avec une seule cave bien déterminée : exemple Auch
DROITE("Auch";4) renvoie Auch.

28/31

Tutoriel Framasoft
Résultat 
Explicatif: J'applique la fonction 
 =Droite  Compare 
de la 
DROITE à la matrice Client avec 4 
Client
(Client;4)
{1} à 
Condition 
caractères ligne par ligne qui me 
{1}
« Auch » 
Auch
rend la matrice {1}
{2}
Je compare chaque cellule de la 
Cave Antibes
ibes
FAUX
0
matrice {1} à la chaîne de 
caractère "Auch" 
Cave Auch
Auch
VRAI
1
On obtient la matrice avec ses VRAI 
Durand
rand
FAUX
0
et FAUX qui se visualise mieux.
Cave Antibes
ibes
FAUX
0
Je traduis: chaque résultat de la 
comparaison est 1 si le critère est 
Cave Marciac
ciac
FAUX
0
satisfait, et 0 s'il ne l'est pas. 
Martin
rtin
FAUX
0
Durand
rand
FAUX
0
J'obtiens la matrice « Résultat de la 
Condition Cave{2} »dont la somme 
Cave Auch
Auch
VRAI
1
est 3.
Martin
rtin
FAUX
0
Cave Antibes
ibes
FAUX
0
En fait, on remplace la matrice 
Durand
rand
FAUX
0
Client par la matrice 
Cave Auch
Auch
VRAI
1
DROITE(Client;4)
Somme 
3
Il ne reste plus qu'à appliquer les autres conditions et les plages à sommer suivant la syntaxe 
connue.
Client
Mois
Floc Blanc
Floc Rosé
Plages nommées
Cave Antibes
Mars
24
12
Client =A2:A13
Cave Auch
Mars
36
24
Mois=B2:B13
Durand
Mars
12
12
Blanc=C2:C13
Cave Antibes
Mars
60
36
Rosé=D2:D13
Cave Marciac
Mai
36
24
Martin
Mai
60
60
Durand
Mai
24
12
Cave Auch
Mai
24
12
Martin
Juin
12
12
Cave Antibes
Juin
36
24
Durand
Juin
60
36
Cave Auch
Juin
24
12
Pour Auch
Formule
donne
Nombre de ventes
=SOMMEPROD(DROITE(Client;4)="Auch")
3
Nb de ventes de Blanc
=SOMMEPROD((DROITE(Client;4)="Auch")*Blanc)
84
Nb de ventes de Rosé
=SOMMEPROD((DROITE(Client;4)="Auch")*Rosé)
48
Nb de ventes Blanc et Rosé
=SOMMEPROD((DROITE(Client;4)="Auch")*(Blanc+Rosé))
132
Nb de vente de Blanc en 
=SOMMEPROD((DROITE(Client;4)="Auch")*(Mois="Mars")*
36
mars
(Blanc))
Etc.

29/31

Tutoriel Framasoft
5.2.3. SOMMEPROD et les Directeurs d'Ecole
Dans les statistiques de rentrée, il est demandé, entre autre, de sommer les élèves 
par niveaux CP , CE , CM etc. à partir des effectifs des classes.
Effectifs proposés d'une Ecole élémentaire à 11 classes:
Classes
Filles
Garçons
Total
Plages nommées
CP1
13
11
24
Classes=A2:A12
CP2
13
12
25
Fil es=B2:B12
CE1a
12
12
24
Garçons=C2:C12
CE1b
12
13
25
CE2a
15
16
31
CE2b
14
14
28
CM1a
13
14
27
CM1b
15
13
28
CM2a
13
15
28
CM2b
14
15
29
CLIS
6
8
14
140
143
283
Extraire suivant les premiers caractères
Par exemple, je cherche à additionner le nombre d'élèves du niveau CE, soit les 
quatre classes de CE 
Explicatif: J'applique la fonction 
Résultat de 
 =Gauche 
GAUCHE à la matrice Classes avec 2 
Compare {1}  la Condition 
Classes (Classes;2)
caractères ligne par ligne qui me rend 
à «CE » 
CE
{1}
la matrice {1}
{2}
Je compare chaque cellule de la 
CP1
CP
FAUX
0
matrice {1} à la chaîne de caractère 
CP2
CP
FAUX
0
"CE" 
CE1a
CE
VRAI
1
On obtient la matrice avec ses VRAI et 
CE1b
CE
VRAI
1
FAUX (qui se visualise mieux) que je 
CE2a
CE
VRAI
1
traduis: chaque résultat de la 
CE2b
CE
VRAI
1
comparaison est 1 si le critère est 
CM1a
CM
FAUX
0
satisfait, et 0 s'il ne l'est pas. 
CM1b
CM
FAUX
0
J'obtiens la matrice « Résultat de la 
CM2a
CM
FAUX
0
Condition CE{2} »dont la somme est 
CM2b
CM
FAUX
0
4.
CLIS
CL
FAUX
0
FAUX
0
En fait, on remplace la matrice 
Somme 
4
Classes par la matrice 
GAUCHE(Classes;2)
Le compteur des Classes commençant par CE soit GAUCHE(Classes;2)est:
=SOMMEPROD(GAUCHE(Classes;2)="CE")
qui renvoie 4
Il ne reste plus qu'à appliquer éventuellement les autres conditions et les plages à 
sommer suivant la syntaxe connue.

30/31

Tutoriel Framasoft
Nombre de filles de CE:
=SOMMEPROD((GAUCHE(Classes;2)="CE")*Filles)
Nombre de garçons de CE:
=SOMMEPROD((GAUCHE(Classes;2)="CE")*Garçons)
Nombre total des élèves de CE:
=SOMMEPROD((GAUCHE(Classes;2)="CE")*(Filles+Garçons))
Niveau Fil es Garçons Total
CP
26
 =SOMMEPROD((GAUCHE(Classes;2)="CP")*(Filles))
23
 =SOMMEPROD((GAUCHE(Classes;2)="CP")*(Garçons))
49  =SOMMEPROD((GAUCHE(Classes;2)="CP")*(Filles+Garçons))
CE
53
 =SOMMEPROD((GAUCHE(Classes;2)="CE")*(Filles))
55
 =SOMMEPROD((GAUCHE(Classes;2)="CE")*(Garçons))
108  =SOMMEPROD((GAUCHE(Classes;2)="CE")*(Filles+Garçons))
CM
55
 =SOMMEPROD((GAUCHE(Classes;2)="CM")*(Filles))
57
 =SOMMEPROD((GAUCHE(Classes;2)="CM")*(Garçons))
112  =SOMMEPROD((GAUCHE(Classes;2)="CM")*(Filles+Garçons))
Utilisation de la fonction NBCAR
En référenciant les niveaux CP, CE et CM, j'évite l'emploi des quottes dans ces 
formules. mais j'utilise la fonction NBCAR(texte) qui renvoie le nombre de caractères y 
compris les espaces vides à la place du nombre 2.
Exemple:
Si CE est référencié en A20,
le compteur de Classes de CE devient:
=SOMMEPROD(GAUCHE(Classes;NBCAR(A20)=A20).
Et le nombre de filles de CE est 
=SOMMEPROD((GAUCHE(Classes;NBCAR(A20)=A20)*Filles))
L'avantage est de pouvoir traiter le cas des CM1, c'est à dire de 3 caractères 
significatifs, sans modifier la formule hormis la référence. 
Si CM1 est référenciée en A31,
le compteur des classes de CM1 est
=SOMMEPROD(GAUCHE(Classes;NBCAR(A31)=A31)
Le Nombre de Filles de CM1
=SOMMEPROD((GAUCHE(Classes;NBCAR(A31)=A31)*Filles))
etc.
CM1
28
 =SOMMEPROD((GAUCHE(Classes;NBCAR($A$31))=$A$31)*(Fil es))
27
 =SOMMEPROD((GAUCHE(Classes;NBCAR($A$31))=$A$31)*(Garçons))
55  =SOMMEPROD((GAUCHE(Classes;NBCAR($A$31))=$A$31)*(Fil es+Garçons))

31/31

Document Outline

  • 1.INTRODUCTION
    • 1.1.Licence de ce document.
    • 1.2.Objectifs.
      • 1Un petit rappel sur la fonction SOMME
      • 2SOMME sous une seule condition
      • 3Sommer sous plusieurs conditions : la Fonction SOMMMEPROD
      • 4Comprendre le fonctionnement de SOMMEPROD() 
      • 5Des pistes de découvertes.
    • 1.3.Typographie
  • 2.Fonction Somme :le B.A.-BA
    • 2.1.Désactiver l'AutoSaisie.
    • Sommes à calculer: Le problème posé
      • 2.2.1.Somme de cellules non adjacentes 
      • 2.2.2.Somme de cellules adjacentes ou plage de cellules
    • 2.3.Copier-Glisser
    • 2.4.Références relatives ou absolues
  • 3.Somme sous une condition
    • fonction ()
      • 3.1.1.Utilisation de l'Assistant de fonctions (facultatif)
    • 3.2.Nommer les plages pour mieux visualiser 
    • 3.3.Introduction aux formules matricielles
      • 3.3.1.Définition d'une matrice
      • 3.3.2.Définition d'une formule de matrice
      • 3.3.3.Fonction matricielle SOMME
    • fonction=SOMMEPROD()
      • 3.4.1.Fonction conditionnelle de SOMMEPROD()
      • 3.4.2.Utilisation de la syntaxe Sommeprod(matrice1;matrice2) (facultatif)
      • 3.4.3.Utilisation de la syntaxe: SOMMEPROD((Matricesouscondition)*matriceàsommer).
    • 3.5.Sommer deux colonnes sous une condition
      • 3.5.1.Modification du tableau d'exercice
      • éation de 2 noms de plages 
      • 3.5.3.Somme partielle par catégorie et par jour 
      • 3.5.4.Somme globale par catégorie
  • 4.Comprendre la Fonction SOMMEPROD : 
    • 4.1.C'est à boire qu'il nous faut 
      • 4.1.1.Proposition de tableau de travail
      • 4.1.2.Nommez les plages (dernier rappel)
    • Fonction SOMMEPROD attribut par attribut.
      • 4.2.1.Fonctionnement de SOMMEPROD
        • 1.Comme compteur sous une condition
        • 2.Comme compteur à deux conditions.
        • 3.Comme somme sous deux conditions:
      • 4.2.2.Syntaxe de SOMMEPROD()
        • 1.Comme compteur sous une condition
        • 2.Comme compteur sous des conditions multiples
        • 3.Comme somme sous conditions multiples
      • 4.2.3.Travailler avec des valeurs numériques:
        • 1.Comparaison avec une valeur.
        • 2.Encadrement de deux valeurs.
      • 4.2.4.Additionner sous condition deux 2 données d'une même plage:
  • 5.SOMMEPROD et les Chaînes de caractères
    • 5.1.SOMMEPROD et les premiers caractères d'une plage
      • 5.1.1.Rappel : La Fonction GAUCHE
      • 5.1.2.Extraire suivant les premiers caractères
    • 5.2.SOMMEPROD et les derniers caractères d'une plage
      • 5.2.1.Rappel : La Fonction DROITE
      • 5.2.2.Extraire suivant les derniers caractères
      • 5.2.3.SOMMEPROD et les Directeurs d'Ecole



2