Besoin de totaliser des montants répartis sur plusieurs mois (colonnes) tout en filtrant les lignes par statut (« Confirmed » ou « Pipeline ») ? Voici des formules simples, robustes et compatibles avec toutes les versions d’Excel, plus des variantes Microsoft 365 optimisées.
Contexte et objectif
Vous avez un tableau avec :
- Colonne A : le statut par ligne (« Confirmed » ou « Pipeline »),
- Colonnes B à F : les montants mensuels (janvier → mai).
Le but est de calculer, en bas du tableau (ou sur le côté), deux totaux : la somme de tous les montants des lignes marquées Confirmed et la somme de ceux marquées Pipeline, même si les montants sont étalés horizontalement sur plusieurs colonnes.
Exemple de données (schéma minimal)
| A (Statut) | B (Jan) | C (Fév) | D (Mar) | E (Avr) | F (Mai) |
|---|---|---|---|---|---|
| Confirmed | 1200 | 800 | 0 | 450 | 300 |
| Pipeline | 0 | 600 | 900 | 0 | 0 |
| Confirmed | 500 | 0 | 700 | 0 | 0 |
| Pipeline | 200 | 300 | 0 | 0 | 400 |
| Confirmed | 0 | 0 | 250 | 100 | 0 |
Dans cet exemple, la plage des montants est B2:F6 et la plage des statuts est A2:A6.
Solutions proposées (à copier-coller)
Ces deux formules répondent exactement au besoin :
| Formule | Compatibilité | Particularités |
|---|---|---|
=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6="Confirmed")) | Toutes les versions d’Excel | Fonction matricielle classique ; pas besoin de valider par Ctrl + Shift + Enter. Remplacez « Confirmed » par « Pipeline » pour l’autre total. |
=SUM((B2:F6)*(A2:A6="Confirmed")) | Microsoft 365 / Excel 2021 | Exploite les matrices dynamiques ; entrée standard (touche Entrée). Même variante pour « Pipeline ». |
Astuce locale : si votre Excel utilise le point‑virgule comme séparateur d’arguments, remplacez les virgules , par des ;. Les noms de fonctions peuvent varier selon la langue (ex. SOMMEPROD pour SUMPRODUCT, FILTRE pour FILTER, etc.).
Pourquoi ces formules fonctionnent
SUMPRODUCT : la solution universelle
SUMPRODUCT additionne les produits « élément par élément » de matrices de même dimension. Ici :
B2:F6est une matrice 5 × 5 de montants,(A2:A6="Confirmed")renvoie un vecteur 5 × 1 de valeurs booléennesTRUE/FALSEpar ligne.
Excel réplique implicitement ce vecteur sur toutes les colonnes (broadcasting) pour l’aligner sur 5 × 5 : chaque ligne Confirmed devient une ligne de TRUE et chaque Pipeline devient une ligne de FALSE. Lors de la multiplication :
- les montants des lignes Confirmed sont conservés (
nombre * TRUE = nombre), - ceux des lignes Pipeline sont annulés (
nombre * FALSE = 0), - puis
SUMPRODUCTadditionne tous les résultats.
Pour totaliser Pipeline, on ne change que le critère :
=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6="Pipeline"))Version Microsoft 365 : calcul matriciel direct
Dans Microsoft 365 / Excel 2021, le moteur de matrices dynamiques sait gérer l’expression (B2:F6)*(A2:A6="Confirmed") sans SUMPRODUCT. Il suffit d’envelopper le résultat dans SUM :
=SUM((B2:F6)*(A2:A6="Confirmed"))Alternative moderne avec FILTER
La fonction FILTER sélectionne les lignes Confirmed de la matrice des montants, puis SUM en fait la somme :
=SUM(FILTER(B2:F6, A2:A6="Confirmed"))Remplacez « Confirmed » par « Pipeline » pour l’autre total. Cette approche est très lisible et se combine bien avec d’autres filtres.
Comparaison rapide des approches
| Approche | Quand l’utiliser | Avantages | Limites |
|---|---|---|---|
SUMPRODUCT | Toutes versions d’Excel | Robuste, accepte nativement les plages 2D, pas de CSE | Moins « parlant » pour les novices, peut être un peu plus lent sur d’énormes plages |
SUM((B:F)*(critère)) | Microsoft 365 / 2021 | Court et efficace, excellent pour l’apprentissage des matrices | Non compatible avec les versions plus anciennes |
SUM(FILTER(...)) | Microsoft 365 / 2021 | Très lisible, facilement extensible à plusieurs critères | Nécessite FILTER |
Éviter les pièges courants
- Somme horizontale + critère vertical :
SUMIF/SUMIFSexigent classiquement que la plage à sommer soit 1D (une seule colonne).SUMPRODUCTcontourne cette limite car il supporte les matrices 2D. - Données non numériques : si certaines cellules contiennent du texte (ex. « — »), coercisez avec
N():=SUMPRODUCT(N($B$2:$F$6)*($A$2:$A$6="Confirmed")). - Espaces ou casse dans les statuts : normalisez le critère :
=SUMPRODUCT(($B$2:$F$6)*(UPPER(TRIM($A$2:$A$6))="CONFIRMED")). - Performance : éviter les références de colonnes entières dans
SUMPRODUCT(ex.B:F) sur des centaines de milliers de lignes. Préférez une plage bornée ou un Tableau Excel (Ctrl+T). - Lisibilité : placez le texte du statut dans une cellule dédiée (ex.
H1) et référez‑vous à elle :=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6=$H$1)).
Étendre naturellement aux nouveaux mois
Option 1 — Étendre la plage : si vous ajoutez des colonnes après mai, élargissez la plage : $B$2:$Z$6. Les formules ci‑dessus continueront de fonctionner.
Option 2 — Tableau Excel (recommandé) : convertissez vos données en Tableau (Ctrl+T). Supposons que la première colonne s’appelle Status et que les colonnes mensuelles suivent. Avec SUMPRODUCT :
=SUMPRODUCT( Table1[[Jan]:[Mai]] * (Table1[Status]=$H$1) )Avantage : lorsque vous insérez une nouvelle colonne « Jun / Juin », étendez simplement la partie [[Jan]:[Mai]] à [[Jan]:[Juin]]. Dans Microsoft 365, vous pouvez viser « toutes les colonnes sauf la première » grâce aux fonctions modernes :
=LET(d, DROP(Table1,0,1), s, Table1[Status], SUM(d*(s=$H$1)))Ici, DROP(...,0,1) enlève la première colonne (Status) et garde dynamiquement toutes les colonnes de montants, présentes et futures.
Plusieurs critères : régions, équipes, etc.
Ajoutez des conditions avec des multiplications (ET) ou des additions (OU). Exemple : total des montants de janvier à mai pour les lignes Confirmed et EMEA (colonne G = Région) :
=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6="Confirmed")*($G$2:$G$6="EMEA"))Variante OU (Confirmed ou Pipeline) :
=SUMPRODUCT(($B$2:$F$6)*( ($A$2:$A$6="Confirmed")+($A$2:$A$6="Pipeline") ))En Microsoft 365, la version FILTER se prête aussi très bien aux critères multiples :
=SUM(FILTER(B2:F6, (A2:A6="Confirmed")*(G2:G6="EMEA")))Colonnes non contiguës (ex. Jan, Mar, Mai uniquement)
Vous ne souhaitez sommer que certaines colonnes ? En Microsoft 365 :
=SUM(FILTER(CHOOSECOLS(B2:F6,1,3,5), A2:A6="Confirmed"))Sans CHOOSECOLS, vous pouvez utiliser SUMPRODUCT avec la somme des colonnes ciblées :
=SUMPRODUCT( ($B$2:$B$6+$D$2:$D$6+$F$2:$F$6) * ($A$2:$A$6="Confirmed") )Colonne d’aide (méthode simple et performante)
Si vous préférez éviter les matrices, créez une colonne Total ligne en G :
- En
G2:=SUM(B2:F2), recopiez vers le bas. - Total Confirmed :
=SUMIF(A2:A6,"Confirmed",G2:G6) - Total Pipeline :
=SUMIF(A2:A6,"Pipeline",G2:G6)
C’est souvent la méthode la plus lisible pour les équipes non techniques et elle scale bien.
Cas avancés et bonnes pratiques
Pondérer par une probabilité (% d’affaire)
Supposons une colonne H contenant une probabilité (ex. 0,3 pour 30 %). Pour totaliser les montants pondérés des lignes Pipeline :
=SUMPRODUCT(($B$2:$F$6)*($H$2:$H$6)*($A$2:$A$6="Pipeline"))Le vecteur des probabilités est répliqué horizontalement, ce qui pondère chaque mois de la ligne par sa probabilité.
Ignorer les erreurs
Si certaines cellules de B2:F6 peuvent contenir des erreurs, remplacez-les à la volée :
=SUMPRODUCT( IFERROR($B$2:$F$6,0) * ($A$2:$A$6="Confirmed") )Dans les versions antérieures à Microsoft 365, validez avec Ctrl + Shift + Enter si Excel vous le demande.
Références nommées pour clarifier
Créez deux noms : Montants = $B$2:$F$6 et Statut = $A$2:$A$6. Vos formules deviennent :
=SUMPRODUCT(Montants*(Statut=$H$1))Structurer avec LET
LET (Microsoft 365) améliore la lisibilité et la performance en évitant les répétitions :
=LET(d,B2:F6; s,A2:A6; crit,$H$1; SUMPRODUCT(d*(s=crit)))Alternative moderne : SUM sur SUMIF multi‑colonnes
En Microsoft 365, SUMIF accepte un sum_range 2D et renvoie un vecteur des totaux par colonne. En l’agrégeant avec SUM, vous obtenez le même résultat :
=SUM( SUMIF(A2:A6, "Confirmed", B2:F6) )C’est une alternative concise, mais veillez à la compatibilité de version.
Pas à pas (copier, coller, adapter)
- Saisissez
ConfirmedenH1etPipelineenH2(ou utilisez une liste déroulante). - En
I1:=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6=$H$1))→ total Confirmed. - En
I2:=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6=$H$2))→ total Pipeline. - Convertissez les données en Tableau (Ctrl+T) pour simplifier l’extension à de nouveaux mois.
FAQ
Q : Puis‑je combiner des critères texte avec des dates ?
R : Oui. Exemple : uniquement les lignes Confirmed dont la date de clôture (colonne G) est en 2025 : =SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6="Confirmed")*(YEAR($G$2:$G$6)=2025)).
Q : Et si les mois ne sont pas strictement adjacents ?
R : Utilisez CHOOSECOLS (Microsoft 365) ou additionnez explicitement les colonnes dans SUMPRODUCT (voir plus haut).
Q : Les nombres apparaissent comme du texte (alignés à gauche) ; la somme est fausse.
R : Convertissez en nombres (Coller spécial → Multipliez par 1) ou encapsulez N() : =SUMPRODUCT(N($B$2:$F$6)*($A$2:$A$6="Confirmed")).
Q : Quelle est la méthode la plus rapide ?
R : Sur de grands modèles, la colonne d’aide (SUM par ligne) + SUMIF est souvent la plus performante. Sur Microsoft 365, SUM(FILTER(...)) est très compétitif et plus lisible.
Bonnes pratiques SEO et lisibilité (appliquées ici)
- Des mots‑clés clairs : Excel, SUMPRODUCT, montants horizontaux, critère vertical, FILTER.
- Des exemples concrets et formules copiables.
- Des tableaux pour comparer les approches.
- Des sections ciblées : compatibilité, alternatives, cas avancés.
Récapitulatif express
| Besoin | Formule conseillée | Commentaires |
|---|---|---|
| Toutes versions, simple et fiable | =SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6="Confirmed")) | Changez seulement le texte pour « Pipeline » |
| Microsoft 365, formule courte | =SUM((B2:F6)*(A2:A6="Confirmed")) | Utilise les matrices dynamiques |
| Microsoft 365, très lisible | =SUM(FILTER(B2:F6, A2:A6="Confirmed")) | Parfait pour enchaîner d’autres critères |
| Colonnes non contiguës (365) | =SUM(FILTER(CHOOSECOLS(B2:F6,1,3,5), A2:A6="Confirmed")) | Adaptez l’index des colonnes |
| Très gros volumes | Colonne d’aide =SUM(B2:F2) + SUMIF | Lisible et performant |
Notes complémentaires utiles
- Pourquoi SUMPRODUCT ? : il accepte les plages 2D (comme
B2:F6) et évalue le produit élément par élément, contournant la limite des fonctionsSUMIF/SUMIFSqui, historiquement, exigent une plage de somme sur une seule colonne. - Alternative 365 :
=SUM(FILTER(B2:F6, A2:A6="Confirmed"))donne le même résultat avec une formule très lisible. - Lisibilité : stockez « Confirmed » et « Pipeline » dans des cellules (ex.
$H$1,$H$2) pour ne pas répéter des chaînes de texte dans vos formules. - Évolution du modèle : ajoutez des mois ? Étendez la plage (
$B$2:$Z$6) ou utilisez un Tableau Excel +LET/DROPpour capturer « toutes les colonnes de montants ».
Exemples supplémentaires « copier‑coller »
Total Confirmed dans une cellule de critère (H1 contient Confirmed) :
=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6=$H$1))Total Pipeline (365) avec FILTER :
=SUM(FILTER(B2:F6, A2:A6="Pipeline"))Total Confirmed + EMEA (G = Région) :
=SUMPRODUCT(($B$2:$F$6)*($A$2:$A$6="Confirmed")*($G$2:$G$6="EMEA"))Total avec normalisation du statut (espaces/casse) :
=SUMPRODUCT(($B$2:$F$6)*(UPPER(TRIM($A$2:$A$6))="CONFIRMED"))Total Confirmed (SUMIF multi‑colonnes, 365) :
=SUM(SUMIF(A2:A6,"Confirmed",B2:F6))Conclusion
Pour totaliser des montants disposés horizontalement tout en filtrant verticalement par statut, SUMPRODUCT est la solution la plus universelle. Sur Microsoft 365, les formules à matrices dynamiques (SUM((...)*(...))) et FILTER offrent des alternatives très lisibles et tout aussi fiables. En adoptant un Tableau Excel et, si possible, LET/DROP, vous obtenez des formules plus robustes, prêtes à évoluer au fil des mois ajoutés.