• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel COUNT Coloured Cells

You are here: Home / Excel Formulas / Excel COUNT Coloured Cells
Excel COUNT Coloured Cells
May 29, 2012 by Mynda Treacy

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:

  • Conditional Formatting โ€“ this is used to colour the cell/font. Although you could manually colour the data if you want.
  • Filters
  • The SUBTOTAL Function

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

top excel functions for data analysts

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

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

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

New Array Shaping Excel Functions

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 and 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

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

Excel VSTACK and HSTACK Functions

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

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

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. Theyโ€™re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:Find the Last Value in a ColumnFind the Last Value in a Column
Next Post:Secret Excel DATEDIF FunctionSecret Excel Function DATEDIF

Reader Interactions

Comments

  1. King_Excel

    April 16, 2018 at 7:49 pm

    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
    • Mynda Treacy

      April 17, 2018 at 8:47 am

      Or you can use this free UDF: https://www.myonlinetraininghub.com/count-sum-and-average-colored-cells

      Reply
  2. Rajesh Sinha

    March 5, 2017 at 2:52 pm

    Wonderful idea

    Reply
  3. Peter Buyze

    November 26, 2016 at 8:01 pm

    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

      November 26, 2016 at 9:12 pm

      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

        November 30, 2016 at 6:47 pm

        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

          December 2, 2016 at 5:55 pm

          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

    July 1, 2013 at 7:24 pm

    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

      July 1, 2013 at 10:33 pm

      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
  5. Ashutosh Bhatnagar

    June 28, 2013 at 8:34 pm

    How can We count the Cell based on color.

    Reply
    • Mynda Treacy

      June 29, 2013 at 1:37 pm

      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

    December 20, 2012 at 12:57 am

    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
    • Mynda Treacy

      December 20, 2012 at 6:50 am

      ๐Ÿ™‚ Thanks, Carolyn. Glad to have helped.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.