Fonction Excel · Contrôler un classeur

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.

Module1 (Code)

Function SOMME_SI_COULEUR(plage As Range, cellule_modele As Range, Optional plage_somme As Range) As Variant
' Additionne les cellules de « plage » qui ont la même couleur de fond que « cellule_modele ».
' Avec « plage_somme », la couleur se lit dans « plage » et la valeur dans la cellule de même
' position de « plage_somme » : les deux doivent alors être un seul bloc, de même taille.
' Exemple : =SOMME_SI_COULEUR(B2:B100;D1)
' Excel ne recalcule pas seul après un changement de couleur : Ctrl+Alt+F9 force le calcul.
    Dim cellule As Range
    Dim i As Long
    Dim valeur As Variant
    Dim total As Double
    If plage_somme Is Nothing Then
        ' For Each parcourt aussi une plage en plusieurs blocs, contrairement à Cells(i).
        For Each cellule In plage.Cells
            If MemeFond(cellule, cellule_modele) Then
                valeur = cellule.Value2
                ' Value2 rend les dates et les heures en nombres, comme SOMME les voit.
                ' VRAI et FAUX sont écartés, sinon VRAI compterait pour -1.
                If VarType(valeur) <> vbBoolean And Not IsError(valeur) Then
                    If IsNumeric(valeur) Then total = total + CDbl(valeur)
                End If
            End If
        Next cellule
        SOMME_SI_COULEUR = total
        Exit Function
    End If
    If plage.Areas.Count > 1 Or plage_somme.Areas.Count > 1 _
        Or plage_somme.Rows.Count <> plage.Rows.Count Or plage_somme.Columns.Count <> plage.Columns.Count Then
        SOMME_SI_COULEUR = CVErr(xlErrValue)
        Exit Function
    End If
…

Excel pour Windows, prévue aussi pour Mac
Ctrl+Z annule son résultat

Par défaut, il n’est pas possible de faire une somme basée sur la couleur des cellules dans Excel. Pour un total ponctuel, tu peux filtrer ta colonne par couleur, puis lire le résultat avec SOUS.TOTAL. En revanche, si ce besoin revient souvent, nous te conseillons de copier-coller notre macro VBA, qui ajoute la fonction gratuite SOMME_SI_COULEUR à ton classeur.

Pourquoi SOMME.SI ne peut pas tester une couleur

SOMME.SI et SOMME.SI.ENS ne peuvent pas tester une couleur, parce qu’elles comparent la valeur de chaque cellule à un critère, un nombre, un texte ou une date. La couleur de fond, elle, est une mise en forme et non une valeur, et aucun critère ne sait la lire. Sous leur nom anglais, SUMIF et SUMIFS, les deux fonctions ont exactement la même limite.

Excel n’a pas non plus de fonction SOMME.SI.COULEUR, et la formule =SOMME.SI.COULEUR(B2:B6;B2), tapée dans Excel pour Microsoft 365, renvoie l’erreur #NOM?, la marque d’une fonction inconnue. Pour additionner les cellules d’une couleur, il reste le filtre par couleur suivi de SOUS.TOTAL, la recherche par format ou une fonction VBA comme SOMME_SI_COULEUR.

La fonction SOMME_SI_COULEUR, une somme si couleur en formule

SOMME_SI_COULEUR est une fonction personnalisée écrite en VBA, qui 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.

Dans l’exemple, les montants du loyer, de l’assurance et des loisirs sont surlignés en jaune. La formule =SOMME_SI_COULEUR(B2:B6;B2), écrite en B8, renvoie 1 085, le total de ces trois montants.

	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

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) ».

Voir les étapes détaillées pour installer une fonction

Écrire la formule SOMME_SI_COULEUR

fx Formule

=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. 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 plus de six secondes à se calculer, contre quelques millisecondes sur B2:B500.

Additionner une colonne selon la couleur d’une autre

Le troisième argument de SOMME_SI_COULEUR sert au cas fréquent où c’est le nom du client qui est surligné, alors que le montant est dans la colonne voisine. La fonction lit alors la couleur dans la première plage, et la valeur dans la cellule de même position de la troisième.

fx Formule

=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, la formule renvoie 1 650. Les deux plages doivent être d’un seul tenant et avoir le même nombre de lignes et de colonnes, comme ici deux colonnes de quatre cellules, sans quoi #VALEUR! remplace le total.

Additionner des heures de la même couleur

Puisque SOMME_SI_COULEUR additionne les heures comme SOMME, il te suffit, dans une feuille de temps, de surligner les journées à facturer, puis d’écrire =SOMME_SI_COULEUR(B2:B6;B2). Le résultat est un nombre de jours, qu’il faut mettre en forme pour le lire en heures.

Au format h:mm, Excel repart 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+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.

Faire la somme par couleur sans macro

Sans macro, deux méthodes additionnent les cellules d’une couleur, le filtre par couleur suivi de SOUS.TOTAL et la recherche par format. Elles t’évitent de coller du code et d’activer les macros, mais le total qu’elles donnent est à refaire dès que les couleurs changent. Pour un chiffre à lire une fois, ou dans un classeur qui doit rester sans macro, ce sont donc elles qu’il te faut.

Une troisième méthode, LIRE.CELLULE, revient dans beaucoup de tutoriels, alors qu’elle repose sur les anciennes macros Excel 4.0, que Microsoft 365 désactive par défaut.

Filtrer par couleur, puis SOUS.TOTAL(109)

Le filtre par couleur masque les lignes qui n’ont pas la couleur choisie, et 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.

fx Formule

=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.

Tableau filtré sur les montants jaunes, dont la ligne Total visible n’additionne que les trois montants affichés — Sur un autre tableau filtré sur le jaune, SOUS.TOTAL n’additionne que les trois montants restés visibles, soit 950.

Le code 9, =SOUS.TOTAL(9;B2:B6), donne le même total après un filtre, et 109 écarte en plus les lignes masquées à la main. Le détail des codes est sur la fiche de SOUS.TOTAL.

Le filtre, lui, est aussi précis que 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 les couleurs, et si tu en modifies une, les lignes restent affichées ou masquées comme avant. Clique alors sur Réappliquer, dans l’onglet Données, pour remettre le filtre et le total à jour.

Rechercher par format et lire la barre d’état

La recherche par format sélectionne d’un coup toutes les cellules qui portent la couleur cherchée, et la barre d’état affiche la somme de cette sélection. Tu lis ainsi le total sans filtrer le tableau ni écrire de 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 rend donc un total trop petit, sans qu’aucun message ne le signale.

La recherche distingue deux jaunes voisins, comme le filtre, mais elle confond une cellule remplie de blanc et une cellule sans remplissage. Comme elle ne laisse rien dans la feuille, son total est à relire après chaque changement.

LIRE.CELLULE dans un nom défini, puis SOMME.SI

LIRE.CELLULE 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 sa couleur, et la colonne d’aide ainsi remplie donne à SOMME.SI le critère qui lui manquait.

Microsoft 365 désactive ces macros par défaut, et 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. Cette option se trouve, selon la même aide, dans les paramètres des macros du Centre de gestion de la confidentialité.

Avec cette option décochée, Excel pour Microsoft 365 peut 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.

fx Formule

=LIRE.CELLULE(38;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. Le code 38 renvoie le numéro de la couleur de fond dans la palette de 56 couleurs d’Excel, 6 pour le jaune standard et 0 pour une cellule sans remplissage.

Écrite sans dollar, Budget!B2 est une référence relative, et d’après l’aide de Microsoft sur le gestionnaire de noms, Excel la lit depuis la cellule active au moment où tu crées le nom. Créé depuis C2, le nom vise donc la cellule de gauche, et =CouleurFond écrit en C3 lit B3. C’est pourquoi C2 doit être la cellule active à ce moment-là, faute de quoi le nom viserait une autre cellule que celle de gauche. Un nom qui vise ainsi la cellule de gauche renvoie B2 depuis C2, B3 depuis C3 et D7 depuis E7.

fx Formule

=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.

fx Formule

=SOMME.SI.ENS(B2:B6;C2:C6;6;B2:B6;">100")

Ne garde que les montants jaunes supérieurs à 100, soit 990.

Comme le numéro vient de la palette de 56 couleurs, le jaune standard et un jaune à peine différent reçoivent tous deux le numéro 6, et leurs montants se mélangent dans le total. Si ce mélange te fait renoncer à la méthode, le gestionnaire de noms (Ctrl+F3) permet de retrouver le nom et de le supprimer.

Quelle méthode choisir

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

Pour un total à 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 reste dans le classeur, SOMME_SI_COULEUR est la seule qui tienne en une formule, à condition de la recalculer par Ctrl+Alt+F9 quand une couleur change.

Additionner selon la couleur de police

Quand c’est le texte qui est coloré, 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 même les cellules d’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).

VBA

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 Range
    For Each cellule In plage.Cells
        If VarType(cellule.Value2) = vbDouble Then
            If cellule.Font.Color = cellule_modele.Font.Color Then
                SOMME_SI_COULEUR_POLICE = SOMME_SI_COULEUR_POLICE + cellule.Value2
            End If
        End If
    Next cellule
End Function

Avec 850 et 95 écrits en rouge, =SOMME_SI_COULEUR_POLICE(B2:B6;B2) renvoie 945.

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. Pour la variante, un texte en couleur automatique et un texte noir ont la même couleur.

Somme si couleur et mise en forme conditionnelle

Une couleur posée par une mise en forme conditionnelle n’est pas le fond de la cellule, et SOMME_SI_COULEUR, qui ne lit que ce fond, ne la voit pas. Dans un tableau où une règle colore en rouge les montants supérieurs à 300, =SOMME_SI_COULEUR(B2:B6;B2) renvoie 1 465, le total de tous les montants, puisqu’aucun n’a de remplissage à lui.

Le filtre par couleur, lui, voit ces couleurs, d’où le total de 1 170 que =SOUS.TOTAL(109;B2:B6) affiche 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 la formule la condition de la règle, puisque c’est elle qui décide de la couleur.

fx Formule

=SOMME.SI(B2:B6;">300")

Pour une règle qui colore les montants supérieurs à 300, soit 1 170.

fx Formule

=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 son sens. Additionne plutôt selon un seuil, avec SOMME.SI.

Quand la somme par couleur ne fonctionne pas

#NOM? s’affiche. 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. Excel pour le web, lui, n’exécute pas les macros.
#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 dans un module neuf, par Insertion puis Module.
Le total ne bouge pas après un changement de couleur. Changer une couleur ne modifie aucune valeur, et Excel ne relance aucun calcul, pas même avec F9. Ctrl+Alt+F9 recalcule toutes les formules des classeurs ouverts. Le total se met aussi à jour dès qu’une valeur de la plage change.
Le total vaut 0 ou oublie des cellules. La cellule modèle doit avoir exactement la même couleur, et 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. Si la couleur est posée sur des noms, la plage ne contient que du texte et le total vaut 0. Ajoute alors la colonne des montants en troisième argument.
#VALEUR! apparaît avec le troisième argument. Les deux plages n’ont pas le même nombre de lignes et de colonnes, ou l’une des deux est faite de plusieurs blocs.
Un total d’heures paraît trop petit. La cellule est au format h:mm, qui repart de zéro toutes les 24 heures. Le format [h]:mm affiche le vrai total.
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. SOMME_SI_COULEUR additionne aussi les lignes masquées, alors que SOUS.TOTAL(109) les écarte.

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.

SOMME.SI ne sait pas tester une couleur, parce que ses critères portent sur des valeurs, mais une colonne d’aide qui porte le numéro de la couleur de chaque ligne lui donne un critère, soit remplie à la main, soit calculée par LIRE.CELLULE dans un nom défini, une ancienne fonction de macro que Microsoft 365 désactive par défaut. Sans macro, le filtre par couleur suivi de SOUS.TOTAL reste le plus simple. Pour une formule, SOMME_SI_COULEUR fait le travail de SOMME.SI sur la couleur.

Une somme si couleur ne se met pas à jour seule, parce que changer une couleur ne modifie aucune valeur et ne relance donc aucun calcul, pas même avec F9, si bien qu’il faut appuyer sur Ctrl+Alt+F9 pour recalculer les formules. Une erreur #NOM? signale, elle, des macros désactivées ou un classeur enregistré en « Classeur Excel », qui a perdu le code. Les autres causes sont détaillées dans la section « Quand la somme par couleur 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.

Pour additionner selon la couleur du texte, filtre la colonne avec Filtrer par couleur en choisissant la couleur de police, puis lis le total de =SOUS.TOTAL(109;B2:B6), ou colle la variante SOMME_SI_COULEUR_POLICE donnée sur cette page, qui compare la couleur du texte de chaque cellule à celle d’une cellule modèle. SOMME_SI_COULEUR, elle, ne lit que la couleur de fond.

SOMME_SI_COULEUR est écrite en VBA, le langage des macros d’Excel, alors que les macros de Google Sheets s’écrivent en Apps Script, si bien que cette page ne couvre ni Sheets ni LibreOffice. Google propose un outil qui convertit le code VBA d’un fichier Excel en Apps Script, 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

Faire une somme en fonction de la couleur dans Excel

Pourquoi SOMME.SI ne peut pas tester une couleur

La fonction SOMME_SI_COULEUR, une somme si couleur en formule

Installer la fonction

Écrire la formule SOMME_SI_COULEUR

Additionner une colonne selon la couleur d’une autre

Additionner des heures de la même couleur

Faire la somme par couleur sans macro

Filtrer par couleur, puis SOUS.TOTAL(109)

Rechercher par format et lire la barre d’état

LIRE.CELLULE dans un nom défini, puis SOMME.SI

Quelle méthode choisir

Additionner selon la couleur de police

Somme si couleur et mise en forme conditionnelle

Quand la somme par couleur ne fonctionne pas

Tu as peut-être encore une question

Pour aller plus loin

Télécharge notre cours Excel complet en PDF

Ton cours Excel gratuit

Entraîne-toi sur nos 63 exercices corrigés

Les macros Excel et le VBA expliqués simplement

Activer les macros quand Excel les bloque

Décris ta tâche, on écrit la macro

Besoin d’aide sur Excel ?