Stel dat we een fruitverkooptabel hebben zoals hieronder afgebeeld, en we zullen de gekleurde cellen in de kolom Bedrag tellen of optellen. In deze situatie kunnen we de kolom Bedrag op kleur filteren en vervolgens eenvoudig gefilterde gekleurde cellen tellen of optellen met de functie SUBTOTAAL in Excel.

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

1. Voer de formule in om alle cellen met dezelfde achtergrondkleur te tellen = SUBTOTAAL (102, E2: E20);
2. Voer de formule in om alle cellen met dezelfde achtergrondkleur bij elkaar op te tellen = SUBTOTAAL (109, E2: E20);

Opmerking:: In beide formules is E2: E20 de kolom Bedrag die de gekleurde cellen bevat, en u kunt deze naar behoefte wijzigen.

2. Selecteer de koptekst van de tabel en klik op Data > Filteren. Zie screenshot:

3. Klik op het filterpictogram  in de koptekstcel van de kolom Bedrag en klik op Filter op kleur en de gespecificeerde kleur die u achtereenvolgens telt. Zie screenshot:

Na het filteren tellen en tellen beide SUBTOTAAL-formules automatisch alle gefilterde kleurcellen in de kolom Bedrag. Zie screenshot:

Opmerking:: Deze methode vereist dat de gekleurde cellen die u gaat tellen of optellen zich in dezelfde kolom bevinden.

Eén klik om gekleurde cellen in Excel te tellen, optellen en gemiddeld te maken

Met het uitstekende Tel op kleur kenmerk van Kutools for Excel, kunt u snel cellen tellen, optellen en gemiddeld op basis van de opgegeven vulkleur of lettertypekleur met slechts één klik in Excel. Bovendien zal deze functie ook de max- en min-waarden van cellen achterhalen aan de hand van de vulkleur of letterkleur.

In deze methode maken we een benoemd bereik met de GET.CELL-functie, halen we de kleurcode van cellen op en tellen of optellen we eenvoudig met de kleurcode in Excel. Ga als volgt te werk:

1. Klikken Formules > Definieer naam. Zie screenshot:

2. Voer in het dialoogvenster Nieuwe naam de onderstaande schermafbeelding uit:
(1) Typ een naam in het vak Naam;
(2) Voer de formule in = GET.CELL (38, Blad4! $ E2) in het vak Verwijst naar (nota: in de formule, 38 betekent de celcode teruggeven, en Blad4! $ E2 is de eerste cel in de kolom Bedrag, behalve de kolomkop die u moet wijzigen op basis van uw tabelgegevens.)
(3) Klik op het OK knop.

3. Voeg nu een nieuwe kleurkolom toe aan de originele tabel. Typ vervolgens de formule = NumColor en sleep de AutoFill-hendel om de formule toe te passen op andere cellen in de kolom Kleur. Zie screenshot:
Opmerking:: In de formule, NumColor is het benoemde bereik dat we in de eerste 2 stappen hebben gespecificeerd. U moet het wijzigen in de opgegeven naam die u instelt.

Nu keert de kleurcode van elke cel in de kolom Bedrag terug in de kolom Kleur. Zie screenshot:

4. Kopieer en vermeld de vulkleur in een leeg bereik in het actieve werkblad en typ formules ernaast zoals onderstaand screenshot:
A. Voer de formule in om cellen op kleur te tellen = AANTAL.ALS ($ F $ 2: $ F $ 20, NumColor);
B. Voer de formule in om cellen op kleur op te tellen = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Opmerking:: In beide formules, $ F $ 2: $ F $ 20 is de kolom Kleur, NumColor is het opgegeven benoemde bereik, $ E $ 2: $ E $ 20 is de kolom Bedrag en u kunt deze naar behoefte wijzigen.

Nu ziet u dat de cellen in de kolom Bedrag worden geteld en opgeteld op basis van hun vulkleuren.

Stel dat de gekleurde cellen zich verspreiden in een bereik zoals hieronder afgebeeld, kunnen beide bovenstaande methoden de gekleurde cellen niet tellen of optellen. Hier introduceert deze methode een VBA om het probleem op te lossen.

1. Houd de ALT + F11 toetsen, en het opent de Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het modulevenster.

VBA: tel en som cellen op basis van achtergrondkleur:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Sla vervolgens de code op en pas de volgende formule toe:
A. Tel de gekleurde cellen: = kleurfunctie (A, B: C, FALSE)
B. Tel de gekleurde cellen op: = kleurfunctie (A, B: C, TRUE)

Opmerking: in bovenstaande formules, A is de cel met de specifieke achtergrondkleur waarvan u het aantal en de som wilt berekenen, en B: C is het celbereik waar u het aantal en de som wilt berekenen.

4. Neem bijvoorbeeld de volgende schermafbeelding, voer de formule in= kleurfunctie (A1, A1: D11, FALSE) om de gele cellen te tellen. En gebruik de formule = kleurfunctie (A1, A1: D11, TRUE) om de gele cellen op te tellen. Zie screenshot:

5. Als je andere gekleurde cellen wilt tellen en optellen, herhaal dan stap 4. Dan krijg je de volgende resultaten:

Kutools voor Excel ondersteunt ook enkele handige functies om Excel-gebruikers te helpen bij het maken van speciale berekeningen, zegt tellen op celachtergrondkleur, optellen op lettertypekleur, enz.

1. Selecteer de lege cel waarin u de telresultaten plaatst en klik op Kutools > Kutools-functies > Statistisch en wiskundig > COUNTBYCELLKLEUR. Zie screenshot:

2. Geef in het dialoogvenster Functieargumenten het bereik op waarin u gekleurde cellen in de Referentienummer kiest u de cel die wordt gevuld met de opgegeven achtergrondkleur in het Kleur_index_nr in en klik op de OK knop. Zie screenshot:

Opmerkingen:
(1) U kunt ook de opgegeven Kutools-functie typen = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20; G2)  in de lege cel of formulebalk om de telresultaten te krijgen;
(2) Klik op Kutools > Kutools-functies > Statistisch en wiskundig > SUMBYCELLKLEUR of typ = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20; G2) in de lege cel om cellen direct op te tellen op basis van de opgegeven achtergrondkleur.
Breng de COUNTBYCELLKLEUR en SUMBYCELLKLEUR functies voor elke achtergrondkleur afzonderlijk, en u krijgt de resultaten zoals hieronder afgebeeld:

Kutools-functies bevatten een aantal ingebouwde functies om Excel-gebruikers te helpen gemakkelijk te berekenen, waaronder Aantal / som / gemiddeld zichtbare cellen, Tel / som op celkleur, Tel / tel op letterkleur, Tekens tellen, Tel op lettertype vetgedrukt, Etc. Probeer gratis!

Met de bovenstaande door de gebruiker gedefinieerde functie moet u de formule een voor een invoeren, als er veel verschillende kleuren zijn, zal deze methode vervelend en tijdrovend zijn. Maar als je dat hebt gedaan Kutools for Excel's Tel op kleur hulpprogramma, kunt u snel een rapport van de gekleurde cellen genereren. U kunt niet alleen de gekleurde cellen tellen en optellen, maar u kunt ook de gemiddelde, max en min waarden van het gekleurde bereik krijgen.

1. Selecteer het bereik dat u wilt gebruiken en klik op Koetools Plus > Tel op kleur, zie screenshot:

2. En in de Tel op kleur dialoogvenster, doe dan zoals hieronder getoond screenshot:
(1) Selecteer Standaardopmaak van de Kleur methode keuzelijst;
(2) Selecteer Achtergrond van de Type telling keuzelijst.
(3) Klik op de knop Rapport genereren.

Opmerking:: Om gekleurde cellen te tellen en op te tellen op basis van specifieke voorwaardelijke opmaakkleur, selecteert u Conditionele opmaak van de Kleur methode vervolgkeuzelijst in het bovenstaande dialoogvenster, of selecteer Standaard en voorwaardelijke opmaak uit de vervolgkeuzelijst om alle cellen te tellen die zijn gevuld met de opgegeven kleur.

Nu krijg je een nieuw werkboek met de statistieken. Zie screenshot:

De Tel op kleur functie berekent (aantal, som, gemiddelde, max, etc.) cellen op achtergrondkleur of lettertypekleur. Probeer gratis!

Hoe cellen tellen / optellen op basis van de lettertypekleuren in Excel?