Comment additionner des valeurs de deux colonnes (ou plusieurs colonnes) pour une même référence dans Excel

Si vous avez déjà perdu 20 minutes à essayer d'additionner deux colonnes pour obtenir des erreurs #VALUE, vous n'êtes pas seul. Mais voici la bonne nouvelle : additionner des colonnes ne doit pas être douloureux.

Pourquoi additionner des colonnes compte (au‑delà des maths de base)

Imaginez que vous suivez les ventes mensuelles vs les dépenses. Les additionner manuellement, c'est comme compter de l'argent avec un boulier-ça marche, mais il y a mieux. En affaires, la rapidité n'est pas qu'un confort-c'est un avantage compétitif.

Scénarios courants et problématiques

• Vous avez une colonne avec des références (produits, gammes, codes) et plusieurs colonnes avec des montants (ventes par site ou par mois) et vous souhaitez obtenir la somme pour chaque référence.
• Les gammes peuvent être répétées à plusieurs reprises en raison de la consolidation des données au coup par coup.
• Les calculs conventionnels ne sont pas capables de résoudre facilement la somme conditionnelle sur plusieurs colonnes si le critère doit être appliqué à toute une ligne ou à plusieurs colonnes simultanément.

Méthode classique : SOMME.SI et SOMME.SI.ENS (limites)

Supposons que vous devez additionner des valeurs avec plusieurs conditions, telles que la somme des ventes de produits dans une région spécifique. =SOMME.SI.ENS est une formule arithmétique. Il calcule les nombres, qui dans ce cas se trouvent dans la colonne D. En d’autres termes, vous voulez que la formule additionne les nombres de cette colonne s’ils répondent aux conditions.

Ensuite, vous souhaitez rechercher les données qui répondent à deux conditions. Vous devez donc entrer votre première condition en spécifiant pour la fonction l’emplacement des données (A2 :A11) et également la condition, qui est « Sud ». Enfin, vous entrez les arguments pour votre deuxième condition, la plage de cellules (C2:C11) qui contient le mot "viande", ainsi que le mot lui‑même (entre guillemets) pour permettre à Excel de le trouver.

Lorsque vous tapez la fonction SUMIFS dans Excel, si vous ne vous souvenez pas des arguments, l’aide est disponible. En examinant l’image de la saisie semi‑automatique de formule et la liste des arguments, dans notre exemple sum_range est D2 :D11, la colonne de nombres que vous souhaitez additionner ; criteria_range1 est A2.

Lire aussi: TVA et Excel

Additionner plusieurs colonnes selon un seul critère : SOMMEPROD (méthode polyvalente)

Ce tutoriel vous explique comment additionner des valeurs provenant de plusieurs colonnes selon un même critère dans Excel. Comment additionner plusieurs colonnes en fonction d’un seul critère ? Considérez l’exemple ci‑dessous : imaginez que vous disposiez d’une liste de produits accompagnée de leurs ventes sur trois mois, et que vous souhaitiez additionner toutes les ventes du produit « KTE ».

Formules génériques :

1. =SUMPRODUCT((criteria_rng=criteria)*(sum_range))

Arguments :

• Plage_critères: la plage de cellules que vous souhaitez évaluer à l’aide du critère ;
• Critère: le critère qui détermine quelles cellules de la plage_critères seront additionnées ;
• Plage_somme: la plage de cellules à additionner.

Comment utiliser cette formule ? Sélectionnez une cellule vide, copiez ou saisissez la formule ci‑dessous, puis appuyez sur la touche Entrée pour obtenir le résultat souhaité.

=SUMPRODUCT((B5:B10=G5)*(C5:E10))

Lire aussi: Excel et la TVA : Mode d'emploi

Remarques : 1) Vous pouvez saisir directement le critère dans la formule en l’encadrant de guillemets doubles ; =SUMPRODUCT((B5:B10="KTE")*(C5:E10)) 2) Vous pouvez également combiner les fonctions SOMME et SI pour effectuer le même calcul. N’oubliez pas d’activer cette formule en appuyant simultanément sur les touches Ctrl+Maj+Entrée. =SUM(IF(B5:B10="KTE",C5:E10))

Explication de la formule =SUMPRODUCT((B5:B10=G5)*(C5:E10)) :

• (B5:B10=G5): cette partie vérifie si les cellules de la plage B5:B10 sont égales à « KTE » et renvoie un tableau de ce type {VRAI;FAUX;FAUX;VRAI;FAUX;VRAI} ;
• {VRAI;FAUX;FAUX;VRAI;FAUX;VRAI}*(C5:E10): chaque valeur VRAI ou FAUX du tableau est multipliée par les valeurs de la plage C5:E10 (tous les VRAI et FAUX sont convertis respectivement en 1 et 0), ce qui génère un nouveau tableau : {100,150,300;0,0,0;0,0,0;400,450,450;0,0,0;600,650,550} ;
• SOMMEPROD({100,150,300;0,0,0;0,0,0;400,450,450;0,0,0;600,650,550}): la fonction SOMMEPROD additionne tous les nombres du tableau et renvoie le résultat final 3650.

Additionner sur lignes et colonnes selon une sélection (cas avancé)

Sur l'exemple illustré par la capture, un tableau relate les ventes réalisées par gammes de produits sur quatre différents sites de l'entreprise. Ces gammes peuvent être répétées à plusieurs reprises en raison de la consolidation des données au coup par coup. Sur la droite de ce tableau, l'utilisateur désigne l'une de ces gammes à l'aide d'une liste déroulante. Aussitôt, la somme des ventes réalisées pour cette gamme sur les quatre sites, donc en ligne comme en colonne, est livrée juste en-dessous avec deux formules.

Pour que la somme puisse être réalisée sur les lignes et les colonnes concernées par le choix de l'utilisateur, l'astuce consiste à imbriquer la fonction Somme.Si dans la fonction SommeProd, mais ce n'est pas tout. Vous le savez, la fonction SommeProd est une fonction matricielle. Elle multiplie les lignes respectives des matrices confrontées pour additionner leurs résultats à l'issue. Donc en guise de plage pour la somme de la fonction Somme.Si, nous devons être en mesure de parcourir l'ensemble des colonnes de ce tableau dans ce raisonnement matriciel.

C'est précisément là que l'astuce réside. Il ne s'agit pas de considérer une seule colonne mais toutes celles qui honorent le critère. Et les calculs conventionnels ne sont pas capables de résoudre le cas. En revanche, un raisonnement matriciel est capable de confronter ces colonnes au critère tour à tour, comme le ferait un traitement récursif en VBA Excel. Un montant consolidé apparaît. Il concerne la gamme sélectionnée. Et vous pouvez facilement vérifier la cohérence de cette somme conditionnelle sur plusieurs colonnes en sélectionnant toutes les cellules de couleur et en consultant l'information de synthèse fournie par Excel dans la barre d'état, en bas à droite.

Lire aussi: Comment Calculer TVA 20%

Dans la fonction ligne, nous exploitons la fonction Indirect pour interpréter une matrice virtuelle reconstruite par calcul. Nous partons de l'indice 1 jusqu'à l'indice correspondant au nombre de colonnes du tableau. Celui-ci peut évoluer en effet. Nous nous basons sur la ligne 11 qui ne propose aucune cellule de synthèse sur la droite.

Autres astuces pratiques

• Notez l'emploi judicieux des références absolues dans ce raisonnement chronologique de la règle, en partant de la première gamme figée dans sa colonne pour progresser en ligne et être inévitablement comparée à la référence choisie en H4, complètement figée ($H$4), pour ne pas évoluer avec l'analyse.
• Si vous comptez additionner toute la colonne, c'est le meilleur emplacement. Vous avez une colonne A de données de la ligne A1 à A10 et vous en voulez la somme.
• Comme vous allez faire la somme de toutes les cellules d'un certain nombre de colonnes, vous devez veiller à n'en oublier aucune.
• Doublecliquez sur l'icône de votre document Excel pour l'ouvrir et le modifier.

Outils complémentaires et automation

ChatGPT peut expliquer comment additionner des colonnes, mais voici le hic-il ne peut pas toucher votre fichier. Notre verdict : Utile pour apprendre, mais reste du travail manuel. C'est là que les tableurs gagnent des super‑pouvoirs. Quand passer aux méthodes automatisées ? Vous cliquez encore manuellement ? À vous de jouer : Libérez‑vous des tableurs.

Kutools pour Excel propose plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin est toujours à portée de clic : Combinez les colonnes ou cellules sans perdre de données, Fusion avancée des lignes, Scinder des cellules Excel... Kutools IA Aide: Révolutionnez l'analyse des données grâce à : Exécution intelligente | Générez du code | Créez formules personnalisées | Analysez des données et générez des graphiques.

Exemples pratiques à copier/coller

Vous pouvez utiliser des exemples de données et de formules ici, dans ce classeur Excel pour le web. Copiez toutes les cellules dans le tableau ci‑dessous, puis collez‑les dans la cellule A1 d’une nouvelle feuille de calcul dans Excel.

Points à surveiller

• Ne mélangez pas types de données dans les cellules que vous additionnez (texte + nombres) sans conversion - cela peut provoquer des erreurs ou des résultats inattendus.
• Vérifiez l'utilisation de références absolues ($) quand vous copiez des formules vers d'autres cellules pour garder des critères figés.
• Pour des jeux de données dynamiques, préférez des plages structurées (tableaux Excel) ou des formules matricielles capables de s'adapter au nombre de colonnes.

Ressources pour aller plus loin

• Apprenez Excel avec DataCamp - Fondamentaux d'Excel : Acquérir les compétences essentielles dont vous avez besoin pour utiliser Excel, de la préparation des données à l'écriture de formules et à la création de visualisations.
• Utilisez Kutools pour automatiser et simplifier les opérations répétitives : Rechercher et marquer les doublons, Combiner colonnes, Liste déroulante avancée.

balises:

Articles populaires:

• Plan de financement simplifié
• Démarches pour ouvrir une boutique de fleurs
• Impact des Gangsters de la Finance
• installation électrique pour professionnels en Vendée
• Risques de requalification : guide pratique pour les prestataires en EURL