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

More Excel Formulas Posts

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 Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips
Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do
Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function
The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files
New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions
TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


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 *