- SOMMEPROD(plage1; plage2) multiplie les cellules deux à deux puis additionne les produits : quantité × prix, montant × délai, sans colonne intermédiaire.
- Avec des conditions multipliées, SOMMEPROD((plage>=min)*(plage<=max)*montants) cumule les montants d’une tranche : c’est le moteur d’une balance âgée.
- Une moyenne pondérée s’obtient par SOMMEPROD(valeurs; poids) / SOMME(poids) ; la moyenne simple des prix donne un résultat différent et faux dès que les quantités diffèrent.
- Toutes les plages doivent avoir la même taille ; le texte dans une plage de calcul provoque #VALEUR! quand on utilise l’opérateur *.
SOMME.SI.ENS additionne les lignes qui répondent à des critères. Mais comment calculer un coût moyen pondéré, où chaque prix compte en proportion de sa quantité ? Ou un retard moyen pondéré par les montants ? Il faudrait une colonne intermédiaire (quantité × prix), puis une somme. SOMMEPROD supprime l’intermédiaire : elle multiplie et additionne en une seule formule.
Ce que vous saurez faire à la fin de l’épisode
- expliquer ce que fait SOMMEPROD ;
- calculer une moyenne pondérée ;
- cumuler les montants d’une tranche avec des conditions multipliées ;
- compter ou additionner selon une condition sur un calcul ;
- éviter les erreurs de plages et de types.
Le principe
=SOMMEPROD(plage1; plage2; …) (anglais : SUMPRODUCT)La fonction multiplie les cellules de même rang des plages (la 1re de plage1 par la 1re de plage2, etc.), puis additionne les produits. Avec plage1 = quantités et plage2 = prix : SOMMEPROD(Q;P) = Σ(quantité × prix), la valeur totale d’un stock, sans écrire une seule colonne de valeurs.
Le coût moyen pondéré
Atlas Négoce a acheté trois lots d’une même marchandise : 100 unités à 50 MAD (6 janvier), 250 unités à 52 MAD (18 janvier), 150 unités à 55 MAD (2 février). Quel est le coût unitaire moyen du stock ?
Figure 1 : trois lots, une valeur totale de 26 250,00 et un coût moyen pondéré de 52,50. Repère 2 : la moyenne simple des prix, qui ne tient pas compte des quantités.
=SOMMEPROD(C2:C4; D2:D4) / SOMME(C2:C4)Calcul : (100 × 50) + (250 × 52) + (150 × 55) = 5 000 + 13 000 + 8 250 = 26 250 MAD de valeur totale, pour 500 unités ; 26 250 ÷ 500 = 52,50 MAD le coût moyen unitaire pondéré (CMUP). La moyenne simple des trois prix (50, 52 et 55) donne 52,33 : l’écart peut paraître faible ici, mais il grandit avec l’hétérogénéité des quantités, et c’est le CMUP qui reflète le coût réel du stock (pour la place de cette méthode dans l’évaluation des stocks, voir l’article sur la comptabilité des stocks). Toute moyenne de prix, de taux ou de marges doit être pondérée par les quantités, les montants ou les durées auxquels elle se rapporte.
Cumuler par tranche : la balance âgée
On suit huit clients avec leur solde et leur retard en jours (négatif ou nul = non échu). On veut le total des soldes par tranche d’ancienneté.
Figure 2 : les clients à gauche, les tranches à droite. Repère 1 : la formule de la tranche 1 à 30 jours ; repère 2 : le retard moyen pondéré.
Pour la tranche « 1 à 30 jours », la formule est :
=SOMMEPROD( (C2:C9>=1) * (C2:C9<=30) * B2:B9 )Lecture : chaque comparaison donne VRAI ou FAUX pour chaque client ; multipliées entre elles et par le solde, elles produisent le solde du client s’il est dans la tranche, zéro sinon ; la somme donne le total. C’est un « ET » de deux conditions. Les cinq tranches :
| Tranche | Condition | Clients concernés | Montant |
|---|---|---|---|
| Non échu | retard ≤ 0 | A (12 000), H (7 800) | 19 800,00 |
| 1 à 30 j | 1 ≤ retard ≤ 30 | B (8 400), C (15 000) | 23 400,00 |
| 31 à 60 j | 31 ≤ retard ≤ 60 | D (6 300) | 6 300,00 |
| 61 à 90 j | 61 ≤ retard ≤ 90 | E (22 000) | 22 000,00 |
| Plus de 90 j | retard > 90 | F (9 500), G (4 100) | 13 600,00 |
| Total | 85 100,00 |
Contrôle : le total des cinq tranches doit égaler la somme des soldes (12 000 + 8 400 + 15 000 + 6 300 + 22 000 + 9 500 + 4 100 + 7 800 = 85 100) : la dernière cellule de la figure, à 0,00, le vérifie. Si les tranches ne se recouvrent pas et couvrent toutes les valeurs possibles, le contrôle tombe toujours à zéro.
La part de chaque tranche dans le total s’obtient par division : 23,3 % pour le non-échu, 27,5 % pour 1 à 30 jours, 7,4 %, 25,9 % et 16,0 %. La lecture est immédiate : 41,8 % de l’encours (soit 35 600 sur 85 100) a plus de 60 jours de retard, ce qui mérite une relance (nous détaillerons la balance âgée à l’épisode 37).
Le retard moyen pondéré
Un retard moyen simple (la moyenne des retards des huit clients) traite un client qui doit 4 100 comme un client qui doit 22 000. On préfère pondérer chaque retard par le montant dû :
=SOMMEPROD(B2:B9; C2:C9) / SOMME(B2:B9)Numérateur : 0 + 100 800 + 375 000 + 283 500 + 1 540 000 + 902 500 + 533 000 − 39 000 = 3 695 800 ; dénominateur : 85 100. Résultat : 43,4 jours. Ce chiffre est un indicateur de suivi du recouvrement, à comparer avec les délais contractuels.
Les trois usages classiques de SOMMEPROD
- Multiplier deux colonnes puis additionner : quantités × prix, taux × assiette, montant × durée.
- Cumuler selon des conditions (ET par multiplication) :
SOMMEPROD((Compte="6111")*(Statut="Payé")*Montant). - Conditions sur un calcul que
SOMME.SI.ENSne peut pas exprimer :SOMMEPROD((ANNEE(A2:A500)=2026)*(MOIS(A2:A500)=2)*D2:D500)cumule les montants de février 2026 sans colonne auxiliaire pour l’année ou le mois.
Pour un OU, on additionne les comparaisons : SOMMEPROD(((B2:B9="6111")+(B2:B9="6131"))*D2:D9) cumule les comptes 6111 et 6131. Pour compter, on omet les montants : SOMMEPROD((B2:B9="6111")*(E2:E9="Non payé")).
Les pièges
- Plages de tailles différentes :
#VALEUR!. Tout doit avoir la même hauteur. - Texte dans les plages de calcul : avec la syntaxe à point-virgule (
SOMMEPROD(B;C)), le texte vaut zéro ; avec l’opérateur*, il provoque#VALEUR!. Une cellule contenant « — » ou « n.d. » dans une colonne de montants fait ainsi échouer la formule. - Cellules vides : traitées comme zéro, sans problème.
- Lisibilité : une formule de cinq conditions est difficile à relire. Au-delà, une colonne auxiliaire explicite ou un tableau croisé dynamique est préférable.
- Plages entières (
B:B) :SOMMEPRODsur des colonnes entières ralentit fortement le classeur. Utilisez des plages bornées ou un tableau Excel. - Pondération oubliée : calculer la moyenne simple d’un taux ou d’un prix par habitude. Si les poids diffèrent, utilisez
SOMMEPROD.
À vous de jouer
Dans le classeur d’exercice :
- Onglet Stock : calculez le CMUP des trois lots. Comparez avec la moyenne simple des prix.
- Un quatrième lot arrive : 300 unités à 58 MAD. Quel est le nouveau CMUP ?
- Onglet Balance âgée : complétez les cinq tranches, les parts, le total et le retard moyen pondéré.
- Écrivez une formule qui donne le total dû par les clients ayant plus de 60 jours de retard.
- Le client H règle 7 800 : mettez son solde à 0. Que deviennent le retard moyen pondéré et la tranche « non échu » ?
Correction. CMUP : 52,50 (moyenne simple : 52,33). Avec le quatrième lot : valeur = 26 250 + (300 × 58 = 17 400) = 43 650 pour 800 unités, soit 54,5625 MAD, arrondi à 54,56. Les cinq tranches donnent 19 800 ; 23 400 ; 6 300 ; 22 000 ; 13 600 (total 85 100). Plus de 60 jours : =SOMMEPROD((C2:C9>60)*B2:B9) = 22 000 + 9 500 + 4 100 = 35 600,00. Si le solde de H passe à 0, le non-échu tombe à 12 000, le total à 77 300 et le retard moyen pondéré devient (3 695 800 + 39 000) ÷ 77 300 = 48,3 jours : le solde négatif de H (retard −5) tirait la moyenne vers le bas.
À retenir
SOMMEPROD= multiplier puis additionner, sans colonne intermédiaire.- Moyenne pondérée :
SOMMEPROD(valeurs; poids) / SOMME(poids). La moyenne simple des prix, des taux ou des délais est presque toujours fausse. - Tranches :
SOMMEPROD((plage>=min)*(plage<=max)*montants); contrôlez que le total des tranches égale le total général. - Conditions sur calculs (année, mois) et OU : réservées à SOMMEPROD.
- Mêmes tailles de plages, pas de texte dans les colonnes multipliées.
Fin du module 4 : vous savez chercher, additionner sous conditions, rapprocher. Le module 5 est celui de la synthèse : le tableau croisé dynamique, qui fait en trois clics ce que nous venons de faire en formules.
Questions fréquentes
À quoi sert SOMMEPROD quand on a SOMME.SI.ENS ?
SOMME.SI.ENS additionne une colonne selon des critères ; SOMMEPROD sait aussi multiplier des colonnes entre elles (quantité × prix), combiner des conditions OU, ou appliquer une condition sur un calcul (par exemple l’année d’une date). Quand une simple somme conditionnelle suffit, SOMME.SI.ENS est plus lisible.
Pourquoi mes conditions sont-elles multipliées avec * ?
Une comparaison produit VRAI ou FAUX ; multipliée par un nombre, elle devient 1 ou 0. Le produit de plusieurs comparaisons vaut 1 seulement quand toutes sont vraies : c’est un ET. Pour un OU, on additionne les comparaisons (avec des parenthèses).
SOMMEPROD fonctionne-t-elle sans Ctrl + Maj + Entrée ?
Oui, elle traite nativement des plages de cellules sans validation matricielle. En revanche, avec une syntaxe à deux plages séparées par un point-virgule, elle traite le texte comme un zéro, alors qu’avec l’opérateur * elle renvoie #VALEUR!.
Comment compter avec SOMMEPROD ?
En omettant les montants : =SOMMEPROD((B2:B100="Non payé")*(D2:D100>10000)) compte les lignes non payées de plus de 10 000. C’est l’équivalent de NB.SI.ENS, avec en plus la possibilité de conditions sur des calculs.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.