MENU
  1. Accueil
  2. MS Office
  3. Excel
  4. Excel : additionner des colonnes par en‑tête (ordre variable) — XLOOKUP, VSTACK, SUMPRODUCT

Excel : additionner des colonnes par en‑tête (ordre variable) — XLOOKUP, VSTACK, SUMPRODUCT

Excel

Besoin d’additionner toutes les valeurs sous “A1” et “A2” alors que vos colonnes bougent ? Voici des formules modernes et rétro‑compatibles, prêtes à copier‑coller, avec variantes robustes pour les en‑têtes manquants, doublons et erreurs.

Sommaire

Additionner des cellules en fonction des en‑têtes, même si l’ordre des colonnes change

Problématique

Vous disposez d’un tableau dont la ligne 2 contient les en‑têtes (« A1 », « A2 », etc.) et dont les données s’étendent de B3 : C5 (ou plus large dans votre fichier réel). Vous souhaitez additionner toutes les valeurs placées sous les en‑têtes “A1” et “A2”, quel que soit leur emplacement après un réarrangement de colonnes.

Mini‑jeu de données d’exemple

LigneBCDE
2A2B3A1C7
310853
47290
56514

Peu importe si, demain, la colonne “A1” se retrouve en B, C, D ou E : les formules ci‑dessous trouveront les bonnes colonnes et en feront la somme.

Solutions proposées

ApprocheFormule (à saisir dans une seule cellule)CompatibilitéPoints clés
1. XLOOKUP + VSTACK / HSTACK=SUM( VSTACK( XLOOKUP("A1",$B$2:$Z$2,$B$3:$Z$1048576), XLOOKUP("A2",$B$2:$Z$2,$B$3:$Z$1048576) ) )Excel 365 / 2021XLOOKUP repère la colonne d’en‑tête visée et renvoie tout le bloc de données dessous. VSTACK (ou HSTACK) concatène les colonnes ciblées pour que SUM les additionne d’un coup. Adaptez $B$2:$Z$2 et $B$3:$Z$1048576 à la largeur/hauteur réelles du tableau.
2. Somme conditionnelle avec IF (ou FILTER)=SUM( IF( ($B$2:$Z$2="A1") + ($B$2:$Z$2="A2"), $B$3:$Z$1048576, 0 ) )Excel 365 / 2021 (calcul matriciel)La condition fabrique un masque (1 = colonne à garder). IF renvoie uniquement les colonnes marquées, puis SUM agrège. En versions antérieures, valider avec Ctrl + Shift + Enter ou préférez SUMPRODUCT.
3. Alternative pré‑365 (INDEX+MATCH+SUMPRODUCT)=SUMPRODUCT( ($B$2:$Z$2="A1")*($B$3:$Z$1048576) + ($B$2:$Z$2="A2")*($B$3:$Z$1048576) )Excel 2010 à 2019SUMPRODUCT gère nativement les calculs matriciels (pas de touches spéciales). Même logique : multiplier chaque colonne par un masque binaire issu des en‑têtes, puis sommer.

Remarque “séparateur” : si votre Excel utilise le point‑virgule comme séparateur d’arguments (paramètre régional FR), remplacez les virgules par des ; dans les formules.

Approches « plus propres » et plus rapides en 365

Si vous disposez d’Excel 365, vous pouvez obtenir des formules encore plus lisibles et performantes grâce à XMATCH et CHOOSECOLS.

CHOOSECOLS + XMATCH (recommandé)

=LET(
  h, $B$2:$Z$2,
  d, $B$3:$Z$1048576,
  SUM( CHOOSECOLS( d, XMATCH({"A1","A2"}, h, 0) ) )
)
  • XMATCH renvoie la position des colonnes “A1” et “A2”.
  • CHOOSECOLS extrait ces colonnes en un seul appel (moins de calculs que deux XLOOKUP).
  • SUM additionne l’ensemble.

Version robuste si un en‑tête est manquant

=LET(
  h, $B$2:$Z$2,
  d, $B$3:$Z$1048576,
  pos, XMATCH({"A1","A2"}, h, 0),
  SUM( CHOOSECOLS( d, FILTER(pos, ISNUMBER(pos)) ) )
)

Ici, FILTER élimine les positions #N/A si l’un des en‑têtes n’existe pas : la somme continue sans erreur.

Sommer une liste de plusieurs en‑têtes (A1, A2, A7, …)

Placez vos étiquettes cibles sur une feuille « Param », par exemple Param!A1:A99, puis utilisez :

=LET(
  h, $B$2:$Z$2,
  d, $B$3:$Z$1048576,
  cibles, Param!$A$1:INDEX(Param!$A:$A,COUNTA(Param!$A:$A)),
  masque, ISNUMBER( MATCH(h, cibles, 0) ),
  SUM( IF(masque, d, 0) )
)

Avantages : aucune formule à modifier lorsque vous ajoutez/retirez des libellés dans la liste ; la plage cibles « grandit » toute seule.

Utiliser un Tableau Excel (Ctrl+T) pour des plages dynamiques parfaites

Convertissez vos données en Tableau (par ex. TableA) : le tableau s’agrandit automatiquement, et les références deviennent lisibles.

=LET(
  h, TableA[#Headers],
  d, TableA,
  SUM( IF( ISNUMBER(MATCH(h, {"A1","A2"}, 0)), d, 0 ) )
)
  • TableA[#Headers] : ligne d’en‑têtes.
  • TableA : données (sans en‑têtes).
  • Pas besoin de $B$3:$Z$1048576, et la formule reste identique même si vous ajoutez des lignes.

Par ligne : totaliser seulement “A1” et “A2” pour chaque enregistrement

Vous voulez un total par ligne (B3:Z3, B4:Z4, …) en ne prenant que les colonnes dont l’en‑tête est “A1” ou “A2” ? Utilisez BYROW et une LAMBDA compacte.

=BYROW(
  $B$3:$Z$1000,
  LAMBDA(r, SUM( IF( ISNUMBER(MATCH($B$2:$Z$2, {"A1","A2"}, 0)), r ) ) )
)

Fonction personnalisée réutilisable (LAMBDA)

Créez une fonction SOMME_PAR_ENTETES dans le Gestionnaire de noms (Formules > Gestionnaire de noms > Nouveau) :

=LAMBDA(entetes, donnees, cibles,
  LET(
    masque, ISNUMBER( MATCH(entetes, cibles, 0) ),
    SUM( IF(masque, donnees) )
  )
)

Utilisation :

=SOMME_PAR_ENTETES($B$2:$Z$2, $B$3:$Z$1048576, {"A1","A2"})

Conseils complémentaires

  1. Plages dynamiques : préférez les Tableaux Excel (Ctrl+T). Les formules s’ajustent automatiquement.
  2. Validation : stockez “A1” et “A2” dans une zone dédiée (ex. Param!A1:A2) pour éviter les fautes de frappe.
  3. Performance : limitez les plages à la zone utile (évitez $B$3:$Z$1048576 si 5 000 lignes suffisent). CHOOSECOLS+XMATCH est plus rapide que deux XLOOKUP + VSTACK.
  4. Lisibilité : documentez via « Évaluer la formule » et créez des noms définis (Entetes, Donnees) pour alléger la syntaxe.

Dépannage (pièges fréquents et remèdes)

SymptômeCause probableSolution
#N/A avec XLOOKUPEn‑tête introuvableUtilisez la version « robuste » avec FILTER, ou IFNA si vous acceptez 0 quand absent :
=SUM( VSTACK( IFNA(XLOOKUP("A1",h,d,0),0), IFNA(XLOOKUP("A2",h,d,0),0) ) )
Total incorrect (décalage)Plages entêtes et données de tailles différentesAssurez‑vous que h (ligne d’en‑têtes) a le même nombre de colonnes que d (données).
Doublons d’en‑têtes (“A1” présent 2×)Colonnes dupliquéesToutes les colonnes “A1” seront additionnées (comportement voulu la plupart du temps). Pour ne prendre que la 1re :
=LET(h,$B$2:$Z$2; d,$B$3:$Z$1048576; SUM( CHOOSECOLS(d, XMATCH("A1",h,0), XMATCH("A2",h,0) ) ) )
Cellules texte (« 12 » au lieu de 12)Nombres stockés en texteForcer en numérique dans la somme :
=LET(h,$B$2:$Z$2; d,$B$3:$Z$1048576; m,ISNUMBER(MATCH(h,{"A1","A2"},0)); SUM( N(IF(m,d)) ) )
Erreurs dispersées (#DIV/0!, #VALEUR!)Calculs amontIgnorer les erreurs :
=LET(h,$B$2:$Z$2; d,IFERROR($B$3:$Z$1048576,0); m,ISNUMBER(MATCH(h,{"A1","A2"},0)); SUM(IF(m,d)) )
Sensibilité à la casse (“a1” ≠ “A1”)Comparaison binaireUtilisez EXACT :
=SUM( IF( EXACT($B$2:$Z$2,"A1") + EXACT($B$2:$Z$2,"A2"), $B$3:$Z$1048576 ) )
Correspondance partielle voulueEn‑têtes “A1 – Ventes”, “A1 – Coûts”Recherche partielle (attention à “A10”) :
=SUM( IF( ISNUMBER(SEARCH("A1",$B$2:$Z$2)) + ISNUMBER(SEARCH("A2",$B$2:$Z$2)), $B$3:$Z$1048576 ) )

Pas‑à‑pas rapide (copier‑coller)

  1. Identifiez votre ligne d’en‑têtes (ex. $B$2:$Z$2) et vos données ($B$3:$Z$1048576 ou la plage utile).
  2. Collez l’une des formules proposées (365 : CHOOSECOLS+XMATCH ; 2010‑2019 : SUMPRODUCT).
  3. Adaptez les bornes B:Z et le séparateur d’arguments (virgule ou point‑virgule selon votre Excel).
  4. Testez en permutant physiquement les colonnes : le total doit rester identique.

Pourquoi ces méthodes fonctionnent

Toutes les approches appliquent la même idée : créer un masque d’inclusion à partir de la ligne d’en‑têtes, puis sommer uniquement les colonnes marquées. Les fonctions modernes (XMATCH, CHOOSECOLS, BYROW, LAMBDA) réduisent le nombre de calculs et améliorent la lisibilité, tandis que SUMPRODUCT garantit une solution fiable pour les versions plus anciennes d’Excel.

Variantes utiles

  • Somme conditionnelle + critère de ligne : si vous devez filtrer des lignes avant la somme (ex. Année = 2025) : =LET( h,$B$2:$Z$2, d,$B$3:$Z$1048576, cibles,{"A1","A2"}, lignes, $A$3:$A$1048576=2025, SUM( IF(lignes, IF(ISNUMBER(MATCH(h,cibles,0)), d ) ) ) )
  • Somme dans un sous‑ensemble de lignes (plage filtrée ou visible seulement) : pour ne prendre que les lignes visibles après un filtre, utilisez SUBTOTAL et BYROW ou la colonne d’état des filtres avancés.
  • Somme pondérée : remplacez d par d*poids si une colonne de poids s’applique ligne par ligne.

Bonnes pratiques de performance

  • Réduisez les plages aux lignes effectivement utilisées (ex. jusqu’à la dernière ligne non vide via un INDEX dynamique) ou, mieux, basculez en Tableau.
  • Privilégiez CHOOSECOLS+XMATCH à plusieurs XLOOKUP imbriqués : moins d’appels, donc moins de recalculs.
  • Évitez les fonctions volatiles (OFFSET, INDIRECT) si un modèle volumineux doit rester fluide.

Résumé opérationnel

  • Microsoft 365/2021 : utilisez CHOOSECOLS+XMATCH (ou XLOOKUP+VSTACK) pour une solution claire, rapide et robuste.
  • Partage avec versions anciennes : SUMPRODUCT reste la valeur sûre, sans validation matricielle spéciale.
  • Dans tous les cas, la logique demeure : identifier dynamiquement les colonnes par leur en‑tête, puis additionner verticalement les cellules correspondantes – indépendamment de l’ordre des colonnes.

Recettes prêtes à l’emploi (copier‑coller)

BesoinFormuleNotes
Sommer “A1” et “A2” (365)=LET(h,$B$2:$Z$2; d,$B$3:$Z$1048576; SUM(CHOOSECOLS(d, XMATCH({"A1","A2"}, h, 0))) )Ultra‑léger et lisible
Sommer une liste variable=LET(h,$B$2:$Z$2; d,$B$3:$Z$1048576; c,Param!$A$1:INDEX(Param!$A:$A,COUNTA(Param!$A:$A)); SUM(IF(ISNUMBER(MATCH(h,c,0)), d)) )Ajoutez/supprimez des libellés librement
Version compatible 2010‑2019=SUMPRODUCT( ISNUMBER(MATCH($B$2:$Z$2,{"A1","A2"},0)) * $B$3:$Z$1048576 )Pas de touches spéciales
Ignorer les erreurs dans les données=LET(h,$B$2:$Z$2; d,IFERROR($B$3:$Z$1048576,0); SUM(IF(ISNUMBER(MATCH(h,{"A1","A2"},0)), d)) )IFERROR transforme les erreurs en 0
Somme par ligne (toutes lignes)=BYROW($B$3:$Z$1000, LAMBDA(r, SUM(IF(ISNUMBER(MATCH($B$2:$Z$2,{"A1","A2"},0)), r))))Génère une colonne de totaux

FAQ express

Que se passe‑t‑il si “A1” est absent ? Les formules « robustes » continuent (la colonne manquante compte pour 0). Avec la formule simple, utilisez IFNA ou FILTER pour éviter une erreur.

Et si j’ai “A10” et que je cherche “A1” ? Préférez l’égalité stricte (= ou EXACT) plutôt que SEARCH, qui ferait une correspondance partielle.

Comment éviter les références jusqu’à 1 048 576 lignes ? Basculez en Tableau (Ctrl+T) ou remplacez la borne basse par un INDEX dynamique sur la dernière ligne utile.

Avec ces modèles, votre total « A1 + A2 » devient insensible à l’ordre des colonnes : un vrai gain de robustesse pour vos classeurs d’analyse et de reporting.

Excel
Excel Formules Microsoft 365 SUMPRODUCT XLOOKUP
Sommaire