Faire une somme en fonction de la couleur dans Excel
Additionne les cellules surlignées d’une même couleur, avec ou sans macro, par un filtre ou par une fonction gratuite à coller une fois dans ton classeur.
Excel ne sait pas faire une somme selon la couleur des cellules, puisque SOMME.SI teste leurs valeurs et jamais leur couleur. Pour un total ponctuel, tu peux filtrer ta colonne par couleur, puis lire le résultat avec SOUS.TOTAL. Quand ce total est à refaire chaque mois, copie-colle plutôt notre macro VBA, qui ajoute la fonction gratuite SOMME_SI_COULEUR.
Pourquoi SOMME.SI ne peut pas tester la couleur d’une cellule
SOMME.SI ne peut pas tester la couleur d’une cellule, parce qu’elle compare la valeur de chaque cellule à un critère, qu’il s’agisse d’un nombre, d’un texte ou d’une date. La couleur de fond que tu as posée sur tes montants est une mise en forme, pas une valeur, si bien qu’aucun critère ne sait la lire. SOMME.SI.ENS bute sur la même limite, tout comme les deux fonctions sous leur nom anglais, SUMIF et SUMIFS.
Et la fonction SOMME.SI.COULEUR que tu cherches peut-être n’existe pas non plus dans Excel. Tapée dans Excel pour Microsoft 365, la formule =SOMME.SI.COULEUR(B2:B6;B2) renvoie l’erreur #NOM?, la marque d’une fonction qu’Excel ne connaît pas. Pour additionner les cellules d’une couleur, il te reste donc le filtre par couleur suivi de SOUS.TOTAL, la recherche par format, ou une fonction VBA comme SOMME_SI_COULEUR.
Calculer une somme si couleur avec la fonction SOMME_SI_COULEUR
Pour calculer une somme si couleur dans une formule, il te faut une fonction personnalisée écrite en VBA, comme SOMME_SI_COULEUR. Elle additionne les cellules d’une plage dont le fond a exactement la couleur d’une cellule modèle. Une fois collée dans ton classeur, elle s’écrit dans une cellule comme n’importe quelle formule, et son total reste dans la feuille.
Dans l’onglet Budget de l’exemple, trois montants sont surlignés en jaune, les 850 du loyer, les 95 de l’assurance et les 140 des loisirs. Écrite en B8, la formule =SOMME_SI_COULEUR(B2:B6;B2) renvoie 1 085, le total de ces trois montants, sans que tu aies à les désigner un par un. Pour compter ces cellules au lieu de les additionner, NB_SI_COULEUR s’écrit de la même façon, sans troisième argument, et =NB_SI_COULEUR(B2:B6;B2) renvoie 3 sur le même budget.
| A | B | |
|---|---|---|
| 1 | Poste | Montant |
| 2 | Loyer | 850 |
| 3 | Courses | 320 |
| 4 | Assurance | 95 |
| 5 | Transport | 60 |
| 6 | Loisirs | 140 |
| 7 | ||
| 8 | Total en jaune | 1085 |
Installer la fonction qui additionne par couleur
Ouvre l’éditeur VBA (Alt+F11 sous Windows), insère un module neuf et colles-y tout le code de la fonction. Écris ensuite la formule dans une cellule, puis enregistre le classeur au format « Classeur Excel (prenant en charge les macros) ».
Écrire la formule SOMME_SI_COULEUR et ses arguments
=SOMME_SI_COULEUR(B2:B6;B2)Additionne les montants de B2:B6 qui ont le même fond que B2.
| Argument | Ce qu’il contient |
|---|---|
| plage | Les cellules dont la fonction lit la couleur, et qu’elle additionne quand le troisième argument est absent. |
| cellule_modele | Une cellule qui porte la couleur cherchée. Elle peut être dans la plage ou à côté, dans une case de légende par exemple. |
| plage_somme | Facultatif. Les cellules à additionner à la place de la plage, décrites dans la section suivante. |
La couleur est comparée à l’identique, si bien que le jaune standard et un jaune à peine différent, choisi dans Autres couleurs, ne se confondent pas.
Une cellule sans remplissage ne compte pas comme une cellule remplie de blanc, même si les deux se ressemblent à l’écran.
Les heures, les durées et les dates s’additionnent comme avec SOMME.
Les textes qui ne sont pas des nombres, VRAI et FAUX sont laissés de côté. Une cellule en erreur l’est aussi, si bien qu’elle ne bloque pas le total, là où SOMME renverrait l’erreur.
Un nombre saisi comme texte est additionné, alors que SOMME l’ignore dans une plage. Sur un Windows français, « 5,5 » saisi comme texte compte pour 5,5.
Les lignes masquées restent dans le total, même quand un filtre les cache.
Donne-lui une plage à la taille de tes données, comme B2:B500, plutôt qu’une colonne entière. Avec =SOMME_SI_COULEUR(B:B;D1), la fonction lit le fond de chacune des 1 048 576 cellules de la colonne, et recommence à chaque Ctrl+Alt+F9. Cette seule formule peut mettre plusieurs secondes à se calculer, contre quelques millisecondes sur B2:B500.
Faire la somme d’une colonne selon la couleur d’une autre
Pour faire la somme d’une colonne selon la couleur d’une autre, donne à SOMME_SI_COULEUR un troisième argument, la plage des montants. C’est le cas fréquent où tu as surligné le nom du client, alors que son montant se trouve dans la colonne voisine. La fonction lit alors la couleur dans la première plage, et additionne la valeur de même position dans la troisième.
=SOMME_SI_COULEUR(A2:A5;A2;B2:B5)Additionne les montants de B2:B5 dont le client, en colonne A, a le même fond que A2.
Avec Dupont et Leroy surlignés en vert, et 1 200 et 450 en face de leurs noms, la formule renvoie 1 650. Veille simplement à ce que les deux plages soient d’un seul tenant et aient le même nombre de lignes et de colonnes, comme ici deux colonnes de quatre cellules. Sans cela, c’est l’erreur #VALEUR! qui s’affiche à la place du total.
Additionner des heures de la même couleur
Dans une feuille de temps, surligne les journées à facturer, puis écris =SOMME_SI_COULEUR(B2:B6;B2), puisque la fonction additionne les heures comme le ferait SOMME. Le résultat est un nombre de jours, qu’il te faut encore mettre en forme pour le lire en heures.
Au format h:mm, Excel repart en effet de zéro toutes les 24 heures, si bien qu’un total de 25 heures et 45 minutes s’affiche 1:45. Pour lire 25:45, sélectionne la cellule du total, appuie sur Ctrl+Maj+1 et, dans la catégorie Personnalisée de l’onglet Nombre, tape le format personnalisé [h]:mm, dont les crochets empêchent ce retour à zéro.
Comment faire la somme par couleur sans VBA
Pour faire la somme par couleur sans VBA, filtre ta colonne par couleur et additionne les lignes restées visibles avec SOUS.TOTAL. Tu peux aussi sélectionner les cellules colorées par une recherche par format, puis lire leur somme dans la barre d’état. Ces deux méthodes t’évitent de coller du code et d’activer les macros, mais leur total est à refaire dès que les couleurs changent. Ce sont donc elles qu’il te faut pour un chiffre à lire une fois, ou dans un classeur qui doit rester sans macro.
Beaucoup de tutoriels proposent une troisième méthode, LIRE.CELLULE, qui se passe elle aussi de VBA mais repose sur les anciennes macros Excel 4.0, que Microsoft 365 désactive par défaut. Elle a tout de même sa section plus bas, pour qui tient à garder SOMME.SI.
Filtrer par couleur, puis additionner avec SOUS.TOTAL
Filtre ta colonne par couleur, et les lignes qui n’ont pas la couleur choisie se masquent, pendant que SOUS.TOTAL(109), écrit sous le tableau, n’additionne que celles qui restent visibles. Le total reste ainsi dans la feuille tant que le filtre est posé, ce que la recherche par format ne permet pas.
- 01
Sous le tableau, écris la formule du total ci-dessous.
- 02
Clique dans le tableau, puis sur le bouton Filtrer de l’onglet Données (Ctrl+Maj+L).
- 03
Ouvre la flèche de la colonne des montants, choisis Filtrer par couleur, puis clique sur la couleur voulue sous Filtrer par couleur de cellule.
Le menu Filtrer par couleur liste les couleurs de fond présentes dans la colonne. - 04
Pour revoir tout le tableau une fois le total lu, rouvre la flèche et efface le filtre.
=SOUS.TOTAL(109;B2:B6)Additionne les seules lignes visibles de B2:B6, soit 1 085 dans l’exemple une fois filtré sur le jaune.
Le code 9, =SOUS.TOTAL(9;B2:B6), donne le même total après un filtre, alors que 109 écarte en plus les lignes masquées à la main. Tu trouveras le détail de ces codes sur la fiche de SOUS.TOTAL.
Côté précision, le filtre vaut SOMME_SI_COULEUR, puisqu’il compare la couleur exacte et distingue une cellule remplie de blanc d’une cellule sans remplissage. Il ne suit pas pour autant tes couleurs, et si tu en modifies une, les lignes restent affichées ou masquées comme avant. Un clic sur Réappliquer, dans l’onglet Données, remet alors le filtre et le total à jour.
Lire la somme des cellules colorées dans la barre d’état
Pour lire la somme des cellules colorées dans la barre d’état, il suffit de les sélectionner toutes, et la recherche par format le fait d’un coup, où qu’elles se trouvent dans la colonne. Tu lis ainsi le total sans filtrer le tableau ni écrire la moindre formule.
- 01
Sélectionne la colonne des montants, appuie sur Ctrl+F, puis clique sur le bouton « Options >> » pour déplier la fenêtre Rechercher et remplacer.
Les options dépliées de la recherche. Le bouton Format et sa flèche sont en haut à droite, et la case qui les précède affiche Sans mise en forme tant qu’aucun format n’est choisi. - 02
Ouvre la flèche placée à droite du bouton « Format… », choisis « Choisir le format à partir de la cellule… », puis clique sur une cellule qui porte la couleur cherchée. La case à gauche du bouton, qui annonçait Sans mise en forme, montre alors un aperçu du format retenu.
- 03
Clique sur le bouton Rechercher tout. La liste qui se déplie nomme chaque cellule trouvée, et la ligne sous elle en donne le nombre, « 3 cellule(s) trouvée(s) » dans l’exemple.
- 04
Clique sur une ligne de cette liste, appuie sur Ctrl+A pour sélectionner d’un coup toutes les cellules trouvées, puis clique sur le bouton Fermer.
- 05
Lis la somme des cellules sélectionnées dans la barre d’état, en bas de la fenêtre d’Excel, soit 1 085 dans l’exemple.
Prends le format sur une cellule qui ne porte que la couleur cherchée, parce qu’Excel relève la mise en forme de la cellule modèle dans son ensemble, et pas sa seule couleur. Un modèle jaune et gras ne trouve ainsi que les cellules à la fois jaunes et grasses, alors qu’un modèle jaune sans gras trouve toutes les cellules jaunes, grasses comprises. Un modèle mal choisi te rend donc un total trop petit, sans qu’aucun message ne te prévienne.
Comme le filtre, la recherche distingue deux jaunes voisins, mais elle confond une cellule remplie de blanc et une cellule sans remplissage. Et comme elle ne laisse rien dans la feuille, son total est à relire après chaque changement.
Donner à SOMME.SI un critère de couleur avec LIRE.CELLULE
LIRE.CELLULE donne à SOMME.SI le critère de couleur qui lui manque, en remplissant une colonne d’aide avec le numéro de la couleur de chaque montant. Seulement, c’est une fonction des anciennes macros d’Excel 4.0, qu’on ne peut pas écrire dans une cellule. La méthode des tutoriels la place donc dans un nom défini, CouleurFond par exemple, créé pendant que la cellule C2 est sélectionnée. Écrit ensuite en face de chaque montant, de C2 à C6, =CouleurFond renvoie le numéro de palette de sa couleur.
Reste un obstacle de taille, puisque Microsoft 365 désactive ces macros par défaut. D’après l’aide de Microsoft, elles ne s’exécutent que si l’option qui les active avec les macros VBA est cochée, dans les paramètres des macros du Centre de gestion de la confidentialité. Avec cette option décochée, Excel pour Microsoft 365 peut même refuser le nom dès que LIRE.CELLULE désigne une cellule. Si ton Excel accepte le nom, les formules ci-dessous prennent le relais.
=LIRE.CELLULE(63;Budget!B2)À écrire dans la zone Fait référence à d’un nom défini, jamais dans une cellule, pendant que C2 est sélectionnée. D’après la référence des fonctions de macro Excel 4.0 de Microsoft, le code 63 renvoie la couleur de remplissage de la cellule, sous la forme de son numéro de palette, de 1 à 56, soit 6 pour le jaune standard et 0 pour une cellule sans remplissage.
Écrite sans dollar, Budget!B2 est une référence relative, qu’Excel lit depuis la cellule active au moment où tu crées le nom, d’après l’aide de Microsoft sur le gestionnaire de noms. Créé depuis C2, le nom vise donc toujours la cellule de gauche, si bien qu’il renvoie B2 depuis C2, B3 depuis C3 et D7 depuis E7. C’est pourquoi C2 doit être la cellule active quand tu crées le nom, faute de quoi il viserait une autre cellule que celle de gauche.
=SOMME.SI(C2:C6;6;B2:B6)Avec une colonne d’aide qui vaut 6 en face des montants jaunes, additionne ces montants, soit 1 085.
=SOMME.SI.ENS(B2:B6;C2:C6;6;B2:B6;">100")Ne garde que les montants jaunes supérieurs à 100, soit 990.
Ce numéro a toutefois un défaut, puisqu’il vient de la palette de 56 couleurs, où le jaune standard et un jaune à peine différent portent tous deux le numéro 6. Leurs montants se retrouvent donc additionnés dans le même total. Si ce mélange te fait renoncer à la méthode, le gestionnaire de noms (Ctrl+F3) te permet de retrouver le nom et de le supprimer. Et pour lire le code exact d’une couleur plutôt que ce numéro de palette, la fonction CODE_COULEUR le renvoie dans une cellule, 65535 pour le jaune standard, sans confondre deux jaunes voisins.
Quelle méthode choisir pour additionner par couleur
| Méthode | Total dans la feuille | Sans code VBA | Couleur du texte | Couleur d’une mise en forme conditionnelle |
|---|---|---|---|---|
| Filtre et SOUS.TOTAL | Oui, tant que le filtre est posé | Oui | Oui | Oui |
| Rechercher par format | Non, lu dans la barre d’état | Oui | Oui | Non |
| SOMME_SI_COULEUR | Oui, à jour après Ctrl+Alt+F9 | Non | Non, voir la variante plus bas | Non |
Tout se joue sur la durée de vie du total que tu cherches. Pour un chiffre à lire une fois, le filtre suffit, et c’est aussi la seule des trois méthodes à voir la couleur posée par une mise en forme conditionnelle. Pour un total qui doit rester dans le classeur, SOMME_SI_COULEUR est la seule à tenir en une formule, à condition de la recalculer par Ctrl+Alt+F9 quand une couleur change.
Additionner selon la couleur du texte ou de la police
Pour additionner selon la couleur du texte, quand tes chiffres sont écrits en couleur au lieu d’être surlignés, les deux méthodes sans macro marchent aussi. Dans Filtrer par couleur, choisis la couleur de police voulue, et =SOUS.TOTAL(109;B2:B6) additionne les lignes restantes, tandis que la recherche par format trouve de la même façon les cellules écrites dans une couleur de police.
SOMME_SI_COULEUR, elle, ne lit que le fond, si bien qu’une formule sur la couleur du texte demande une variante. Colle cette variante courte dans un module neuf, puis écris =SOMME_SI_COULEUR_POLICE(B2:B6;B2) dans la cellule du total.
Function SOMME_SI_COULEUR_POLICE(plage As Range, cellule_modele As Range) As Double' Additionne les nombres de « plage » écrits dans la même couleur que « cellule_modele ».Dim cellule As RangeFor Each cellule In plage.CellsIf VarType(cellule.Value2) = vbDouble ThenIf cellule.Font.Color = cellule_modele.Font.Color ThenSOMME_SI_COULEUR_POLICE = SOMME_SI_COULEUR_POLICE + cellule.Value2End IfEnd IfNext celluleEnd Function
La variante additionne, comme SOMME_SI_COULEUR, les nombres, les heures et les dates, et laisse de côté les textes, VRAI, FAUX et les erreurs. Un nombre saisi comme texte, en revanche, n’entre pas dans son total, là où SOMME_SI_COULEUR l’additionne. Elle tient enfin un texte en couleur automatique et un texte noir pour une seule et même couleur.
Faire une somme si couleur avec une mise en forme conditionnelle
Si tes couleurs viennent d’une mise en forme conditionnelle, SOMME_SI_COULEUR ne les voit pas. Une couleur posée par une règle n’est en effet pas le fond de la cellule, et la fonction ne lit que ce fond. Dans un tableau où une règle colore en rouge les montants supérieurs à 300, =SOMME_SI_COULEUR(B2:B6;B2) renvoie ainsi 1 465, le total de tous les montants, puisqu’aucun n’a de remplissage à lui.
Le filtre par couleur, lui, voit ces couleurs, et =SOUS.TOTAL(109;B2:B6) affiche 1 170 une fois le même tableau filtré sur le rouge de la règle. La recherche par format, en revanche, ne trouve aucune cellule. Le plus sûr reste de reprendre dans ta formule la condition de la règle, puisque c’est elle qui décide de la couleur.
=SOMME.SI(B2:B6;">300")Pour une règle qui colore les montants supérieurs à 300, soit 1 170.
=SOMME.SI.ENS(B2:B6;C2:C6;"En retard")Pour une règle par formule qui colore le montant quand la colonne C vaut « En retard », soit 380.
Avec des nuances de couleurs, la teinte change avec chaque valeur, et un total par couleur perd tout son sens. Mieux vaut alors additionner selon un seuil, avec une formule SOMME.SI comme la première ci-dessus.
Pourquoi la somme si couleur ne fonctionne pas
Une somme si couleur qui ne fonctionne pas se trahit par une erreur, par un total qui ne bouge plus ou par un total faux. Cherche ton symptôme dans la liste ci-dessous, qui donne pour chacun sa cause et, le plus souvent, le geste qui la corrige.
#NOM? s’affiche à la place du total quand Excel ne trouve pas la fonction. Les macros sont peut-être désactivées (activer les macros sur Excel explique comment les autoriser), ou le classeur a été enregistré en « Classeur Excel », le format sans macros, qui supprime le code. Quant à Excel pour le web, il n’exécute tout simplement pas les macros.
Si #NOM? s’affiche alors que les macros sont actives, le code a sans doute été collé dans le module d’une feuille ou dans ThisWorkbook, où Excel ne trouve pas la fonction. Colle-le plutôt dans un module neuf, par le menu Insertion puis Module.
Le total ne bouge pas après un changement de couleur, parce que changer une couleur ne modifie aucune valeur, si bien qu’Excel ne relance aucun calcul, pas même avec F9. Ctrl+Alt+F9, en revanche, recalcule toutes les formules des classeurs ouverts, et le total se met aussi à jour dès qu’une valeur de la plage change.
Quand le total vaut 0 ou oublie des cellules, vérifie d’abord que la cellule modèle a exactement la même couleur, car un jaune choisi dans Autres couleurs n’est pas le jaune standard. Une cellule sans remplissage ne vaut pas non plus une cellule remplie de blanc. Et si la couleur est posée sur des noms, la plage ne contient que du texte, d’où un total de 0, qu’il suffit de corriger en ajoutant la colonne des montants en troisième argument.
#VALEUR! apparaît avec le troisième argument quand les deux plages n’ont pas le même nombre de lignes et de colonnes, ou quand l’une des deux est faite de plusieurs blocs.
Un total d’heures qui paraît trop petit vient du format h:mm, qui repart de zéro toutes les 24 heures, alors que le format [h]:mm affiche le vrai total.
Si des cellules colorées sont ignorées, leur couleur vient peut-être d’une mise en forme conditionnelle, que la fonction ne lit pas.
Le total compte des lignes filtrées, parce que SOMME_SI_COULEUR additionne aussi les lignes masquées, alors que SOUS.TOTAL(109) les écarte.
D’autres fonctions et macros utiles
Compter les cellules d’une couleur de fond
Compte les cellules surlignées d’une même couleur, avec ou sans macro, par un filtre, par la recherche d’Excel ou par une fonction gratuite à coller une fois dans ton classeur.
Lire le code couleur du fond d’une cellule
Retrouve la couleur exacte d’une cellule pour la reprendre ailleurs, ou pour comprendre pourquoi deux jaunes presque identiques ne se comptent pas ensemble.
Tu as peut-être encore une question
Pour faire la somme des cellules colorées, filtre la colonne par couleur (Données, Filtrer, puis Filtrer par couleur) et lis le total d’une formule =SOUS.TOTAL(109;B2:B6), ou colle la fonction gratuite SOMME_SI_COULEUR pour écrire =SOMME_SI_COULEUR(B2:B6;B2), qui additionne les cellules ayant le même fond que B2. La fonction reste dans le classeur et se recalcule avec Ctrl+Alt+F9.
Non, Excel n’a pas de fonction SOMME.SI.COULEUR, et tapée dans Excel pour Microsoft 365, la formule =SOMME.SI.COULEUR(B2:B6;B2) renvoie l’erreur #NOM?, la marque d’une fonction inconnue, si bien qu’il faut passer par le filtre par couleur suivi de SOUS.TOTAL, par la recherche par format ou par une fonction VBA comme SOMME_SI_COULEUR. Une fois collée dans le classeur, cette fonction gratuite s’écrit comme n’importe quelle formule.
Une somme par couleur ne se met pas à jour seule, parce que changer une couleur ne modifie aucune valeur, si bien qu’Excel ne relance aucun calcul, pas même avec F9, et qu’il faut appuyer sur Ctrl+Alt+F9 pour recalculer toutes les formules des classeurs ouverts. Avec le filtre par couleur, c’est un clic sur Réappliquer, dans l’onglet Données, qui remet le filtre et le total à jour. Les autres pannes, comme #NOM? ou un total à 0, sont détaillées dans la section sur la somme si couleur qui ne fonctionne pas.
Pour compter les cellules d’une couleur sans macro, filtre la colonne par couleur, puis écris =SOUS.TOTAL(103;A2:A6), qui compte les cellules non vides restées visibles, ou =SOUS.TOTAL(102;B2:B6) pour ne compter que les nombres, et les deux renvoient 3 dans l’exemple des trois montants jaunes. Après un changement de couleur, un clic sur Réappliquer, dans l’onglet Données, remet le filtre et le compte à jour.
Cette page ne couvre ni Google Sheets ni LibreOffice, parce que SOMME_SI_COULEUR est écrite en VBA, le langage des macros d’Excel, alors que les macros de Google Sheets s’écrivent dans un autre langage, Apps Script, où la fonction serait à réécrire. Google propose bien un outil qui convertit le code VBA d’un fichier Excel en Apps Script, mais il est réservé aux offres Workspace Enterprise Plus et Education Plus.
Pour aller plus loin
Tout ce dont tu as besoin pour faire tourner tes macros et progresser sur Excel se trouve ici.
Télécharge notre cours Excel complet en PDF
Télécharger
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Entraîne-toi sur nos 63 exercices corrigés
S’entraîner
Les macros Excel et le VBA expliqués simplement
Lire le guide
Activer les macros quand Excel les bloque
Voir comment