Excel COUNT Coloured Cells

Mynda Treacy

May 29, 2012

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

Find the Last Value in a Column

Secret Excel DATEDIF Function

13 thoughts on “Excel COUNT Coloured Cells”

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

    • 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

      • 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

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

    • 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

    • 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. 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 Comment

Current ye@r *