Excel-tips: Cellen tellen/optellen op kleur (achtergrond, lettertype, Voorwaardelijke opmaak gebruiken)

Bij dagelijkse taken is kleurcodering een populaire methode om cruciale gegevens snel te herkennen en te markeren. Maar hoe tel of som je celgegevens op basis van een specifieke kleur — zoals vulkleur, letterkleur of voorwaardelijke opmaak? Excel biedt standaard geen directe functie om op kleur te tellen of op te tellen, maar met een paar slimme trucs en indirecte methodes lukt het toch. In dit artikel leggen we uit hoe je gegevens op kleur kunt tellen of sommeren.

Tel en sommeer cellen op basis van Achtergrondkleur

• Door gebruik te maken van een door de gebruiker gedefinieerde functie
• Door gebruik te maken van een krachtige functie – Kutools voor Excel
• Door gebruik te maken van Filter en SUBTOTAAL

Tel en sommeer cellen op basis van Letterkleur

• Door gebruik te maken van een door de gebruiker gedefinieerde functie
• Door gebruik te maken van een eenvoudige functie – Kutools voor Excel

Tel en sommeer cellen op basis van Voorwaardelijke opmaak gebruiken-kleur

• Door gebruik te maken van VBA-code
• Door gebruik te maken van een slimme functie – Kutools voor Excel

Video: Tel en sommeer cellen op basis van kleur

Tel en sommeer cellen op basis van Achtergrondkleur

Stel dat u bijvoorbeeld een reeks gegevens hebt waarin de cellen zijn ingekleurd met verschillende achtergrondkleuren, zoals in de onderstaande schermafbeelding. Excel biedt standaard geen directe functie om cellen te tellen of op te tellen op basis van hun achtergrondkleur. Met een beetje vindingrijkheid en handige technieken kunt u deze taak echter wél uitvoeren. In deze sectie bespreken we enkele nuttige methoden.

Tel en sommeer cellen op basis van Achtergrondkleur met een door de gebruiker gedefinieerde functie

Hier tonen we u hoe u een door de gebruiker gedefinieerde functie maakt en gebruikt om deze taak in Excel op te lossen. Volg hiervoor de volgende stappen:

Stap 1: Open de VBA-module-editor en kopieer de code

1. Druk op Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.
2. Klik in het geopende venster op Invoegen > Module om een nieuwe lege module te maken.
3. Plak vervolgens de onderstaande code in de lege module.
   VBA-code: Tel en sommeer cellen op basis van Achtergrondkleur

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Stap 2: Formules maken om cellen op basis van Achtergrondkleur te tellen en sommeren

Sluit na het plakken van de bovenstaande code het modulevenster en pas de volgende formules toe:

• Tel cellen op basis van een specifieke achtergrondkleur:
  Kopieer of typ de onderstaande formule in de gewenste cel om het resultaat te krijgen. Sleep vervolgens de vulgreep naar beneden om ook andere resultaten te verkrijgen. Zie de schermafbeelding:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Opmerking: In deze formule is G2 de referentiecel met de specifieke achtergrondkleur die u wilt vergelijken; $B$2:$E$12 het bereik waarin u het aantal cellen met dezelfde kleur als G2 wilt tellen; en FALSE geeft aan dat alleen cellen met een overeenkomende achtergrondkleur worden meegeteld.
  
• Sommeer cellen op basis van een specifieke achtergrondkleur:
  Kopieer of typ de onderstaande formule in de gewenste cel om het resultaat te krijgen. Sleep vervolgens de vulgreep naar beneden om ook andere resultaten te verkrijgen. Zie de schermafbeelding:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Opmerking: In deze formule is G2 de referentiecel met de specifieke achtergrondkleur die u wilt vergelijken; $B$2:$E$12 het bereik waarin u het aantal cellen met dezelfde kleur als G2 wilt tellen; en TRUE geeft aan dat cellen met een overeenkomende kleur moeten worden meegerekend.
  

Tel en sommeer cellen op basis van Achtergrondkleur met een krachtige functie

Voor wie geen ervaring heeft met programmeren, kan VBA behoorlijk complex overkomen. Daarom introduceren we een krachtig hulpmiddel: Kutools for Excel, met de Tellen op kleur-functie waarmee u in slechts enkele klikken eenvoudig berekeningen (aantal, som, gemiddelde, enz.) op basis van achtergrondkleur kunt uitvoeren. Opvallend genoeg gaat de Tellen op kleur-functie nog verder: ze werkt niet alleen met achtergrondkleur, maar onderscheidt ook letterkleur en ondersteunt berekeningen op basis van voorwaardelijke opmaak.

Na het downloaden en installeren van Kutools voor Excel, selecteert u eerst het gegevensbereik waarvoor u cellen wilt tellen of optellen op basis van een specifieke achtergrondkleur. Ga vervolgens naar KUTOOLS PLUS en kies Tellen op kleur.

Geef in het Tellen op kleur-dialoogvenster de gewenste bewerkingen op:

1. Selecteer Standaard opmaakuit de Kleurmoduskeuzelijst;
2. Geef Achtergrondop in de Statistiektypekeuzelijst, en u kunt de statistische resultaten voor elke Achtergrondkleur in het dialoogvenster bekijken;
3. Klik ten slotte op Rapport genereren om de berekende resultaten naar een nieuw werkblad te exporteren.

Resultaat:

U ontvangt nu een nieuw werkblad met de statistieken. Zie de schermafbeelding:

Cellen tellen en optellen op basis van Achtergrondkleur met de functie Filter en SUBTOTAAL

Stel dat we een fruitsalestabel hebben zoals op de onderstaande schermafbeelding, en dat we de gekleurde cellen in de kolom Bedrag willen tellen of optellen.

Stap 1: Pas de SUBTOTAAL-functie toe

Selecteer lege cellen om de SUBTOTAAL-functie in te voeren.

• Voer de volgende formule in om alle cellen met dezelfde Achtergrondkleur te tellen:

  =SUBTOTAL(102, F2:F16)

• Voer de volgende formule in om alle cellen met dezelfde Achtergrondkleur op te tellen;

  =SUBTOTAL(109, F2:F16)

• Opmerking: In de bovenstaande formules betekent 102 dat numerieke waarden in een gefilterde lijst worden geteld, waarbij verborgen cellen worden uitgesloten; 109 dat waarden in een gefilterde lijst worden opgeteld, waarbij verborgen cellen worden uitgesloten; en F2:F16 is het bereik waarover de telling of optelling wordt uitgevoerd.
• 

Stap 2: Filter cellen op basis van een specifieke kleur

1. Selecteer de koptekst van de tabel en klik op Gegevens > Filter. Zie de schermafbeelding:
   
2. Klik op het Filter-pictogram in de koptekstcel van de Bedrag-kolom en klik achtereenvolgens op Filteren op kleur en de specifieke kleur waarmee u wilt tellen. Zie schermafbeelding:
   

Resultaat:

Na het filteren tellen en sommeren de SUBTOTAAL-formules automatisch de zichtbare cellen in de kolom Bedrag. Zie schermafbeelding:

Cellen tellen en optellen op basis van Letterkleur

Wilt u in Excel cellen tellen of optellen op basis van hun letterkleur? Stel dat u gegevens heeft zoals op de bijgaande schermafbeelding, met tekst in rood, blauw, oranje en zwart. Standaard ondersteunt Excel dit niet direct — maar maak u geen zorgen! In dit gedeelte tonen we u enkele eenvoudige trucs om het toch voor elkaar te krijgen.

Cellen tellen en optellen op basis van Letterkleur met een gebruikersgedefinieerde functie

Om cellen met een specifieke letterkleur te tellen en op te tellen, helpt de volgende gebruikersgedefinieerde functie u bij het uitvoeren van deze taak. Volg hiervoor de onderstaande stappen:

Stap 1: Open de VBA-module-editor en kopieer de code

1. Druk op Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.
2. Klik in het geopende venster op Invoegen > Module om een nieuwe lege module te maken.
3. Plak vervolgens de onderstaande code in de lege module.
   VBA-code: Tel en sommeer cellen op basis van Letterkleur

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Stap 2: Formules maken om cellen te tellen en optellen op basis van Letterkleur

Sluit na het plakken van de bovenstaande code het modulevenster en pas de volgende formules toe:

• Tel cellen op basis van de specifieke letterkleur:
  Kopieer of typ de onderstaande formule in de gewenste cel om het resultaat te krijgen. Sleep vervolgens de vulgreep naar beneden voor aanvullende resultaten. Zie de schermafbeelding:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Opmerking: In deze formule is G2 de referentiecel met de specifieke letterkleur waarmee u wilt vergelijken; $B$2:$E$12 is het bereik waarin u het aantal cellen met dezelfde kleur als in G2 wilt tellen.
  
• Sommeer cellen op basis van de specifieke letterkleur:
  Kopieer of typ de onderstaande formule in de gewenste cel om het resultaat te krijgen. Sleep vervolgens de vulgreep naar beneden voor aanvullende resultaten. Zie de schermafbeelding:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Opmerking: In deze formule is G2 de referentiecel met de specifieke letterkleur waarmee u wilt vergelijken; $B$2:$E$12 is het bereik waarin u de som wilt berekenen van alle cellen met dezelfde kleur als in G2.
  

Cellen tellen en optellen op basis van Letterkleur met een eenvoudige functie

Op zoek naar een moeiteloze manier om celwaarden in Excel te tellen of op te tellen op basis van letterkleur? Ontdek dan de Kutools voor Excel's Tellen op kleur-functie! Met deze slimme tool wordt het tellen en optellen van cellen op basis van een specifieke letterkleur kinderspel. Ontdek hoe Kutools uw Excel-ervaring kan transformeren.

Na het downloaden en installeren van Kutools voor Excel, selecteert u eerst het gegevensbereik waarvoor u cellen wilt tellen of optellen op basis van een specifieke letterkleur. Klik vervolgens op KUTOOLS PLUS > Tellen op kleur om het Tellen op kleur-dialoogvenster te openen.

Geef in het Tellen op kleur-dialoogvenster de gewenste bewerkingen op:

1. Selecteer Standaard opmaakuit de Kleurmoduskeuzelijst;
2. Geef Lettertypeop in de Statistiektypekeuzelijst, en u kunt de statistische resultaten voor elk Letterkleur in het dialoogvenster bekijken;
3. Klik ten slotte op Rapport genereren om de berekende resultaten naar een nieuw werkblad te exporteren.

Resultaat:

U hebt nu een nieuw werkblad met gedetailleerde statistieken op basis van letterkleur. Zie de schermafbeelding:

Cellen tellen en optellen op basis van Voorwaardelijke opmaak gebruiken-kleur

In Excel gebruikt u vaak Voorwaardelijke opmaak om specifieke kleuren toe te passen aan cellen die aan bepaalde criteria voldoen, waardoor datavisualisatie intuïtief wordt. Maar wat als u die speciaal opgemaakte cellen moet tellen of optellen? Hoewel Excel hier standaard geen directe oplossing voor biedt, zijn er slimme manieren om deze beperking te omzeilen.

Voorwaardelijk opgemaakte cellen tellen en optellen met VBA-code

Het tellen en optellen van voorwaardelijk opgemaakte cellen in Excel is met de ingebouwde functies niet eenvoudig – maar met VBA-code lukt het wél! Hieronder leggen we uit hoe u VBA hiervoor gebruikt:

Stap 1: Open de VBA-module-editor en kopieer de code

1. Druk op Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.
2. Klik in het geopende venster op Invoegen > Module om een nieuwe lege module te maken.
3. Plak vervolgens de onderstaande code in de lege module.
   VBA-code: Tel en sommeer cellen op basis van Voorwaardelijke opmaak gebruiken-kleur

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Stap 2: Voer deze VBA-code uit

1. Druk na het plakken van de code op de F5-toets om deze uit te voeren. Er verschijnt een invoervenster. Selecteer het gegevensbereik waarin u cellen wilt tellen en sommeren op basis van voorwaardelijke opmaak, en klik vervolgens op OK. Zie schermafbeelding:
   
2. Selecteer in een ander invoervenster een specifieke Voorwaardelijke opmaak gebruiken-kleur die u wilt tellen en sommeren, en klik op de OKknop, zie schermafbeelding:
   

Resultaat:

Het resultaat – inclusief zowel het aantal als de som van cellen met de opgegeven kleur voor Voorwaardelijke opmaak gebruiken – wordt nu weergegeven in het pop-upvenster. Zie schermafbeelding:

Voorwaardelijk opgemaakte cellen tellen en optellen met een slimme functie

Bent u op zoek naar andere snelle en eenvoudige manieren om voorwaardelijk opgemaakte cellen te tellen en op te tellen? Dan is Kutools voor Excel uw ideale oplossing. Met de Tellen op kleur-functie lost u deze taak op in slechts enkele klikken. Ontdek hoe efficiënt en nauwkeurig Kutools uw werkproces kan maken!

Na het downloaden en installeren van Kutools voor Excel, selecteert u eerst het gegevensbereik waarvoor u cellen wilt tellen of optellen op basis van een specifieke kleur uit de Voorwaardelijke opmaak. Klik vervolgens op KUTOOLS PLUS > Tellen op kleur om het Tellen op kleur-dialoogvenster te openen.

Geef in het Tellen op kleur-dialoogvenster de gewenste bewerkingen op:

1. Selecteer Voorwaardelijke opmaak gebruikenuit de Kleurmoduskeuzelijst;
2. Geef Achtergrondop in de Statistiektypekeuzelijst, en u kunt de statistische resultaten voor elke voorwaardelijke opmaakkleur in het dialoogvenster bekijken;
3. Klik ten slotte op Rapport genereren om de berekende resultaten naar een nieuw werkblad te exporteren.

Resultaat:

U hebt nu een Nieuw werkblad met gedetailleerde statistieken op basis van de Voorwaardelijke opmaak gebruiken-kleur. Zie schermafbeelding:

Gerelateerde artikelen: