INFORMATIQUE

SOMMEPROD SI sur Excel : calculer avec une ou plusieurs conditions

Pour utiliser SOMMEPROD avec une condition dans Excel, saisissez =SOMMEPROD((A2:A6="Paris")*D2:D6*E2:E6). Cette formule multiplie les quantités par les prix unitaires, puis additionne uniquement les ventes réalisées à Paris. Résultat : 70 €.

La fonction SI n’est pas nécessaire : le test A2:A6="Paris" filtre directement les lignes. Vous pouvez appliquer le même principe à plusieurs critères, à une période ou à du texte contenu dans une cellule. Tout repose sur la façon dont Excel sélectionne les valeurs avant de les additionner.

Le principe : multiplier puis additionner

Un gestionnaire reçoit un export Excel pour préparer le bilan des ventes d’une région. Il dispose des villes, des produits, des dates, des quantités et des prix unitaires. Pour calculer le chiffre d’affaires, il pourrait ajouter une colonne « Montant », multiplier chaque quantité par son prix, puis faire la somme. Mais SOMMEPROD réalise tout cela dans une seule cellule.

Son fonctionnement tient en deux opérations : multiplier les valeurs situées à la même position dans plusieurs plages, puis additionner les produits obtenus. Sa syntaxe est =SOMMEPROD(tableau1;[tableau2];[tableau3];…). Seul le premier argument est obligatoire, et la fonction accepte jusqu’à 255 matrices.

Voici le tableau « Ventes » utilisé dans la suite de ce tutoriel. La première ligne de la feuille contient les en-têtes.

LigneA : VilleB : ProduitC : DateD : QuantitéE : Prix unitaire
2ParisStylo05/03/2026102 €
3LyonCahier12/03/202654 €
4ParisCahier20/03/202684 €
5LilleStylo02/04/2026202 €
6ParisStylo15/04/202663 €

Pour obtenir le chiffre d’affaires total, entrez =SOMMEPROD(D2:D6;E2:E6). Excel calcule les montants de chaque ligne, soit 20, 20, 32, 40 et 18 €, puis les additionne. Le total est de 130 €.

Vous pouvez également écrire =SOMMEPROD((D2:D6)*(E2:E6)). Les opérateurs comme la multiplication, l’addition, la division ou la soustraction remplacent alors les séparateurs entre plages. Microsoft recommande d’entourer les arguments de parenthèses dans ce type d’expression. Pour revoir les calculs de base, consultez notre guide pour faire des multiplications dans Excel.

SOMMEPROD SI : ajouter une condition sans fonction SI

Le gestionnaire veut maintenant connaître uniquement les ventes de Paris. Son export contient aussi Lyon et Lille, mais il n’a aucune raison de supprimer ces lignes. Il suffit d’ajouter un test à SOMMEPROD :

=SOMMEPROD((A2:A6="Paris")*D2:D6*E2:E6)

La partie (A2:A6="Paris") vérifie la ville de chaque ligne. Elle renvoie VRAI pour Paris et FAUX pour les autres villes. Lorsqu’Excel multiplie ces résultats par les quantités et les prix, VRAI devient 1 et FAUX devient 0.

Les lignes de Lyon et de Lille comptent donc pour zéro. Les trois lignes parisiennes conservent leurs montants : 20 + 32 + 18 = 70 €. C’est tout le principe d’une formule SOMMEPROD SI : le test logique joue le rôle du filtre, sans qu’il soit nécessaire d’imbriquer la fonction SI d’Excel.

Schéma SOMMEPROD avec condition : chaque ligne testée (VRAI ou FAUX), convertie en 1 ou 0, multipliée par la quantité et le prix, total Paris 70 €
Le calcul de =SOMMEPROD((A2:A6= »Paris »)*D2:D6*E2:E6) ligne par ligne : seules les ventes de Paris comptent, pour un total de 70 €.

Pour changer de ville, remplacez simplement « Paris » par le critère voulu. Dans un fichier que vous réutilisez souvent, placez plutôt la ville dans une cellule, par exemple G1, et écrivez =SOMMEPROD((A2:A6=G1)*D2:D6*E2:E6). Vous pourrez modifier le filtre sans toucher à la formule.

Calculer avec plusieurs conditions : ET et OU

Multiplier les tests lorsque tous les critères doivent être remplis

Pour isoler les ventes de stylos à Paris, il faut réunir deux critères : la ville doit être Paris ET le produit doit être Stylo. La multiplication des tests traduit cette exigence :

=SOMMEPROD((A2:A6="Paris")*(B2:B6="Stylo")*D2:D6*E2:E6)

Une ligne n’est retenue que si les deux tests valent 1. Les ventes concernées sont celles de 20 € et de 18 €, soit 38 €. Cette méthode fonctionne de la même manière lorsque vous ajoutez d’autres conditions : chaque nouveau test est multiplié aux précédents.

Additionner les tests pour exprimer OU, sans doublons

Juste avant une réunion, une formule affiche un total étonnamment élevé. Le gestionnaire cherchait les ventes réalisées à Paris OU les ventes de stylos, toutes villes confondues. Il avait écrit :

=SOMMEPROD(((A2:A6="Paris")+(B2:B6="Stylo"))*D2:D6*E2:E6)

Excel renvoie 148 €, alors que le bon total est de 110 €. Le problème vient des lignes qui correspondent aux deux critères : les stylos vendus à Paris. Les deux tests valent 1, leur addition vaut 2 et les 38 € correspondants sont comptés deux fois.

Pour éviter ce double comptage, vérifiez que la somme des tests est supérieure à zéro :

=SOMMEPROD((((A2:A6="Paris")+(B2:B6="Stylo"))>0)*D2:D6*E2:E6)

Chaque ligne compte désormais une seule fois dès qu’elle satisfait au moins un critère. Le résultat est de 110 €, soit 20 + 32 + 40 + 18.

Il existe une exception utile : lorsque les critères concernent deux valeurs différentes d’une même colonne, comme Paris OU Lyon, une ligne ne peut pas satisfaire les deux à la fois. Vous pouvez donc additionner directement les tests, sans risque de doublon.

Les formules SOMMEPROD prêtes à copier

Retrouvez les calculs courants dans ce tableau. Les résultats correspondent aux données « Ventes », sauf la dernière ligne, qui utilise le tableau « Fournitures » expliqué ensuite.

BesoinFormuleRésultat sur l’exemple
Chiffre d’affaires total=SOMMEPROD(D2:D6;E2:E6)130 €
Une condition : Paris=SOMMEPROD((A2:A6="Paris")*D2:D6*E2:E6)70 €
Deux conditions ET : Paris et Stylo=SOMMEPROD((A2:A6="Paris")*(B2:B6="Stylo")*D2:D6*E2:E6)38 €
Deux conditions OU : Paris ou Stylo=SOMMEPROD((((A2:A6="Paris")+(B2:B6="Stylo"))>0)*D2:D6*E2:E6)110 €
Deux valeurs d’une même colonne : Paris ou Lyon=SOMMEPROD(((A2:A6="Paris")+(A2:A6="Lyon"))*D2:D6*E2:E6)90 €
Différent de Paris=SOMMEPROD((A2:A6<>"Paris")*D2:D6*E2:E6)60 €
Période de dates : mars 2026=SOMMEPROD((C2:C6>=DATE(2026;3;1))*(C2:C6<=DATE(2026;3;31))*D2:D6*E2:E6)72 €
Texte qui contient « cahier »=SOMMEPROD(ESTNUM(CHERCHE("cahier";A2:A6))*B2:B6*C2:C6)31 €

Filtrer les calculs selon une date ou un mot

Additionner sur une période de dates

Une comptable doit isoler les achats d’un fournisseur sur un mois. Dans un tableau contenant les dates, les quantités et les prix, elle peut utiliser deux conditions : une date de début et une date de fin.

Sur notre tableau Ventes, la formule =SOMMEPROD((C2:C6>=DATE(2026;3;1))*(C2:C6<=DATE(2026;3;31))*D2:D6*E2:E6) additionne uniquement les montants de mars 2026. Elle renvoie 72 €.

Les deux comparaisons sont reliées par une multiplication, car la date doit respecter les deux bornes. La fonction DATE construit les dates à partir de l’année, du mois et du jour. Attention : la colonne C doit contenir de vraies dates Excel, pas du texte ressemblant à une date. Pour d’autres besoins calendaires, consultez notre article sur le nombre de mois entre deux dates.

Une condition texte qui contient un mot

Dans un inventaire, les produits sont rarement nommés d’un seul mot. Une recherche exacte sur « cahier » ne retrouvera pas forcément « Cahier A4 » ou « cahier A5 ».

Cahiers, stylos et surligneur sur un bureau, comme les libellés d'un tableau de fournitures

Prenons un tableau « Fournitures » avec A pour le libellé, B pour la quantité et C pour le prix. Les lignes 2 à 6 contiennent : Cahier A4 (3 unités à 5 €), Stylo bleu (10 unités à 2 €), cahier A5 (4 unités à 4 €), Classeur (2 unités à 6 €) et Stylo noir (5 unités à 2 €).

Pour additionner les montants des articles dont le libellé contient « cahier », utilisez :

=SOMMEPROD(ESTNUM(CHERCHE("cahier";A2:A6))*B2:B6*C2:C6)

CHERCHE renvoie la position du mot lorsqu’il est présent. Sinon, elle renvoie une erreur que ESTNUM transforme en FAUX. Le test obtenu permet de conserver uniquement les lignes correspondantes. Résultat : 15 + 16 = 31 €.

CHERCHE ignore les majuscules et les minuscules. Si vous devez respecter la casse, utilisez TROUVE à sa place : dans cet exemple, seule la ligne « cahier A5 » correspond exactement à l’écriture recherchée, pour 16 €.

Corriger une erreur #VALEUR! ou un total inattendu

Une formule SOMMEPROD qui renvoie une erreur ne nécessite pas forcément d’être reconstruite. Commencez par vérifier la taille des plages. Elles doivent avoir les mêmes dimensions. Ainsi, =SOMMEPROD(D2:D13;E2:E10) échoue, alors que =SOMMEPROD(D2:D13;E2:E13) fonctionne.

Loupe et calculatrice posées sur un tableau de chiffres pour vérifier un calcul

Examinez ensuite les données. SOMMEPROD traite les entrées non numériques comme des zéros dans son fonctionnement standard, mais les cellules contenant du texte peuvent aussi provoquer une erreur dans certaines expressions. Vérifiez que les quantités et les prix sont bien enregistrés comme des nombres, ou apprenez à convertir du texte en nombre dans Excel.

Si le résultat semble incorrect, contrôlez les critères avant de modifier toute la formule. Dans la barre de formule, sélectionnez une expression comme A2:A6="Paris", puis appuyez sur F9. Excel affiche la suite des résultats VRAI et FAUX. Appuyez sur Échap pour quitter cet aperçu sans remplacer la formule.

Enfin, évitez les références à des colonnes entières. Pour les performances, l’aide officielle de Microsoft sur SOMMEPROD conseille de limiter les plages aux données utiles : A2:A1000 plutôt que A:A.

SOMMEPROD ou SOMME.SI.ENS : quelle fonction choisir ?

Lorsque vous devez simplement additionner des valeurs selon des critères, SOMME.SI.ENS est généralement plus lisible, et pour un seul critère, SOMME.SI suffit. SOMMEPROD devient intéressante dès que le calcul exige de multiplier des colonnes ou d’appliquer une fonction à une plage.

FonctionBesoinRésultat
SOMME.SI.ENSAdditionner les quantités de stylos vendus à Paris16 pièces
SOMMEPRODCalculer le chiffre d’affaires des stylos vendus à Paris38 €

Avec =SOMME.SI.ENS(D2:D6;A2:A6;"Paris";B2:B6;"Stylo"), Excel additionne les quantités correspondant aux deux critères. Le résultat est de 16 pièces. Mais cette formule ne calcule pas directement les 38 € de chiffre d’affaires, puisqu’il faut multiplier les quantités par les prix.

La distinction est simple : SOMME.SI.ENS additionne une plage selon des conditions (jusqu’à 127 paires plage/critère, selon la page Microsoft de la fonction SOMME.SI.ENS) ; SOMMEPROD réalise aussi des opérations entre plusieurs plages. Les deux fonctions se complètent plutôt qu’elles ne se remplacent.

Questions fréquentes

Faut-il valider avec Ctrl + Maj + Entrée ?

Dans Microsoft 365 et Excel 2021, Entrée suffit : la documentation Microsoft sur les formules de tableau dynamiques indique que les anciennes formules Ctrl + Maj + Entrée restent prises en charge, mais ne doivent plus être utilisées. Les versions plus anciennes demandaient Ctrl + Maj + Entrée pour les formules matricielles qui utilisent SI.

Peut-on utiliser SOMMEPROD dans Google Sheets ?

Oui. Google Sheets propose la fonction SOMMEPROD, appelée SUMPRODUCT en anglais. Elle calcule la somme des produits des entrées correspondantes dans des tableaux ou des plages de même taille.

Comment exclure une valeur du calcul ?

Utilisez l’opérateur <>, qui signifie « différent de ». Par exemple, =SOMMEPROD((A2:A6<>"Paris")*D2:D6*E2:E6) additionne toutes les ventes sauf celles de Paris. Sur notre tableau, le résultat est de 60 €.

Les commentaires sont fermés.

Article Suivant :

0 %