My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Excel COUNT Coloured Cells

You are here: Home / Excel Formulas / Excel COUNT Coloured Cells
Excel COUNT Coloured Cells
by

From time to time I get asked; “how can I count cells formatted in a particular colour”. Aka 'color' if you’re from the U.S.

Well, there are some VBA solutions around, but today I’d like to look at a solution that’s fairly straight forward, and uses the tools already available in Excel.

Those tools are:

Excel Count Coloured Cells

Below is my data. I’ve set a Conditional Format to highlight cells in column C that are >$1m, and now I’d like to COUNT them.

I’ve also inserted Filters in row 8.

To insert a filter click on the row you want to use as your column labels > Data tab of ribbon > Filter.

Excel COUNT colored cells

Now I need to filter by colour.

Click on the filter button for Salary > Filter by Color > Filter by Cell Color, or since my font is coloured too I could 'Filter by Font Color'.

Excel COUNT colored cells

Now my data looks like this:

Note how the row numbers are blue and the numbering skips to show that some rows are hidden.

Excel COUNT colored cells

Now I can use the SUBTOTAL function to count only those cells that are visible. Like this:

=SUBTOTAL(102,C9:C35)

=9

Or if I wanted to SUM the salaries >$1m I could use this SUBTOTAL formula:

=SUBTOTAL(109,C9:C35)

=$54,491,666

The syntax for the SUBTOTAL function is:

=SUBTOTAL(function_num, ref1,ref2,...)

In English:

=SUBTOTAL(what type of total do you want, what range/s do you want to subtotal)

The function_num specifies the type of function you want to use. Here are what’s available in SUBTOTAL function candy store:

Excel COUNT colored cells

The trick is that when used with a filtered list the SUBTOTAL function will only COUNT, SUM, AVERAGE etc. those cells that aren’t hidden/filtered.

Notes:

  1. The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
  2. If I clear my filters the SUBTOTAL function will COUNT/SUM all of the data in the range C9:C35.

For more on how the SUBTOTAL function works.

Count Coloured Cells UDF

Another way to count coloured cells is with the UDF: https://www.myonlinetraininghub.com/count-sum-and-average-colored-cells

Excel COUNT Coloured Cells

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Formulas Posts

Excel SUMPRODUCT Function
The Excel SUMPRODUCT function is one of Excel's most versatile and underutilized functions allowing for flexible aggregations of data.
Summarize Months to Quarters
3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions
Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively
How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!
excel dynamic named ranges
Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
functions for financial modelling
Top 23 must know Excel functions for Financial Modeling. Includes example Excel file and step by step instructions.
excel formula by example
Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor
Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts
Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment
Excel Labs is a long awaited, new improved way to write, name and store Excel formulas, including LAMBDAS with the help of AI.


Category:

Reader Interactions

Comments

  1. King_Excel

    You can count cells by their backcolor or forecolor and calculate their average, sum, max, min and total per each color and generate a report without using any formula using Dose for Excel Add-In.

    Reply

  3. Peter Buyze

    Your way to count coloured cells requires filtering the cells 1st. That is not really convenient if you keep adding rows to the table, or if you need to use the hidden rows for something else too. I find it cumbersome to have to filter & unfilter.
    Microsoft suggests a combination of VBA & a formula. The macro code is as follows:

    Function CountCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
    xcolor = criteria.Interior.ColorIndex
    For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
    CountCcolor = CountCcolor + 1
    End If
    Next datax
    End Function

    After that it is possible to use the =CountCCOLOR(range) formula.

    Reply

    • Philip Treacy

      Hi Peter,

      The idea behind this post was to provide a way to count colored cells without using VBA. This post provides a VBA solution.

      The ColorIndex indicates where on the palette the color resides. This isn’t the same as the Color, so using this code may give you the same ColorIndex for colors that are actually different.

      If you give a cell a color then run this code while selecting that cell

      Sub getcolor()

ActiveCell.Offset(, 1) = ActiveCell.Interior.Color
ActiveCell.Offset(, 2) = ActiveCell.Interior.ColorIndex

End Sub

      You’ll get the values for the Color and the ColorIndex.

      If you now adjust the Red, Green or Blue components by 1, and run it again, the ColorIndex will be the same but the Color will be different. So using Interior.Color is correct.

      Regards

      Phil

      Reply

      • Peter Buyze

        Hi Philip, thanks for your reply. I was under the impression that each colour has a unique index, but your 2nd paragraph indicates that is not the case, which does not sound logical to me. Nevertheless, in my use case I have 1 reference colour which I use to colour particular cells with, and the macro works fine like that. And I do not have to apply any filtering prior to counting my coloured cells, which was my issue.

        Reply

        • Philip Treacy

          Hi Peter,

          The ColorIndex refers to where on the palette the color resides. You use the numbers 1 to 56 to access these colors.

          The Interior.Color is the actual color (RGC/HEX value). The ColorIndex and Color aren’t the same thing, and not every color has a different ColorIndex. If you run the code I provided you’ll see you can actually change the color but get the same ColorIndex.

          If you are using colors off the palette to fill your cells then everything will still work fine for you as you are using that palette color as a reference and checking the other cells for it.

          Cheers

          Phil

          Reply

  4. Balaji

    This is really good … but what in case if I need to put formula to automate the task based on the output of this count.

    Reply

    • Mynda Treacy

      Hi Balaji,

      I’m sorry I don’t understand your question. Perhaps you’d like to send an example of what you’re trying to do via the help desk.

      Kind regards,

      Mynda.

      Reply

    • Mynda Treacy

      Hi Ashutosh,

      Filter your column on the colour you want to count and then use the SUBTOTAL function number 103 to count cells containing data:

      =SUBTOTAL(103,your range)

      I hope that helps.

      Kind regards,

      Mynda.

      Reply

  6. Carolyn Rigg

    REALLY impressed by the explanation given above on how to count colour-filtered cells using the ‘visible cells’ option. I have been seeking this solution for ages! And it’s so simple! 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *