• 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

Count, Sum and Average Colored Cells

You are here: Home / Excel VBA / Count, Sum and Average Colored Cells
count sum average colored cells
November 25, 2016 by Philip Treacy

We often get asked if we can count colored cells on a worksheet, and yes you can.

You could count cells using a combination of conditional formatting, filters and the SUBTOTAL function, but I want to show you how writing a UDF (user defined function) can let you count, sum and average colored cells, in the same function.

Background Color or Conditional Formatting?

Before we get started I need to say that this UDF works only with cells that have their background (fill) color changed. It does not work with conditional formatting.

Before you start rolling your eyes, this limitation is down to Microsoft. You can use VBA to get the color of the cell if it is conditionally formatted:

CellColor = ActiveCell.DisplayFormat.Interior.Color

This works fine in a Sub. But put that same line into a function, and you get a #VALUE error returned.

Why? Ask Microsoft. Their documentation just states that the DisplayFormat property will generate a #VALUE error in a UDF.

So we'll just work with background color for now. I will write another blog post on conditional formatting and provide code that works with CF.

Function Declaration

We want to compare the color in a reference cell against the colors of a range of cells. So we must pass into the function both of these things. The function is called ColorMath.

Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")

InputRange is the first argument and is the range of cells we are checking.

ReferenceCell is the cell with the color we are looking for.

The 3rd argument is the Action we want to carry out, that is, are we going to SUM, COUNT or AVERAGE the values in the cells. This is an optional argument. If it is omitted then Action is given the default value "S" which indicates we are going to SUM values.

I've made Action a String, rather than a number as reading the formula in the sheet seems clearer to me if I see "S" for SUM, "C" for COUNT or "A" for AVERAGE. You can of course use numbers to indicate what you want your functions to do.

The string that specifies the action is not case sensitive. The function converts it to upper case before checking it.

Checking Fill Colors

The piece of code that checks the background or fill color is pretty straightforward.

The color of our ReferenceCell is assigned to the variable ReferenceColor by :

  ReferenceColor = ReferenceCell.Interior.Color

Looking For The Colored Cells

Use a For ... Next loop to check each cell in the InputRange

If Action = "C" Then
            
  For Each Cell In InputRange
        
    If Cell.Interior.Color = ReferenceColor Then Result = Result + 1
                
  Next Cell
            
End If

This loop is counting the number of cells that match our ReferenceColour. The process is similar if we want to SUM or AVERAGE the matching cell values.

Some Examples

With a small list of numbers, colored as shown, I've set up my function to show me the SUM, COUNT and AVERAGE of the numbers in the three different colors.

These examples are contained in the sample workbook you can download below.

SUM

sum colored cells

COUNT

count colored cells

AVERAGE

average colored cells

You can of course (and you should) use Named Ranges rather than specify the range as I have done in these examples.

My sample workbook uses a named range called ColoredCells.

Recalculation

If you change the fill color of a cell using the Ribbon, by right clicking and choosing Fill Color, or by right clicking and choosing Format Cells->Fill, this won’t trigger a recalculation, so a function's output may not be correct. Be wary of this.

This is down to the way Excel works. It just doesn't see a background color change as a change worthy of recalculating. Only certain things will cause Excel to recalculate.

However, using the Format Painter to change fill colors will cause a recalculation to occur.

You could also make use of the Workbook_SheetSelectionChange event to make Excel calculate as new cells are selected. Changing the fill color still won't cause a recalculation, but as soon as you click a different cell, or use the cursor keys, Excel will recalculate.

In the sample workbook below I've included code for the Workbook_SheetSelectionChange event if you want to use it.

Look in the ThisWorkbook module.

ThisWorkbook Module

You can also make the function volatile, that is, its output is recalculated each time the sheet or workbook undergoes a recalculation.

By using the statement Application.Volatile we can make our function recalculate by pressing SHIFT+F9 to recalculate the active sheet.

Error Checking

I haven't written any specific error checking or handling routines. So if your InputRange contains a letter, you'll get a #VALUE error.

You can let Excel handle error processing, or write your own error handling routines.

Download Sample Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook here.

To use this function, save it into your PERSONAL.XLSB or into the add-in you created after reading my last blog post.

count sum average colored cells

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBA
Previous Post:Excel COUNT MATCH Array Formula
Next Post:Excel Slopegraphs

Reader Interactions

Comments

  1. Laura S

    January 24, 2018 at 10:44 pm

    Hello
    My knowledge in programing is close to null, yet this function is extremely usefull for my work.
    The (probably) dumb problem I face is that if I copy my worksheet with all my data to the sample workbook you have here for download, everything works wonderfully. However, if I copy the code from your sample workbook to my personal VBAProject, in a new excel file, and I try to use the function it always gives me the error #NAME?
    What’s wrong?
    I have to point out that I’m working in a Mac and that my Office is in french (could that be the problem?)

    Thank you

    Reply
    • Catalin Bombea

      January 25, 2018 at 3:02 am

      Hi Laura,
      When using macro’s from personal.xlsb, you have to type “personal.xlsb!” before the UDF’s name. It’s better if you save the personal.xlsb as an add-in (.xlam file type), then activate the add-in, you will be able to use the UDF’s as regular excel functions, no need to reference the file name in function.
      Catalin

      Reply
  2. Michael Fate

    November 30, 2016 at 2:52 am

    Great post Philip. Is there anyway to add some descriptions or help to this function? At a bare minimum, it would be nice if the user could see what the “Action” options were.

    Thanks

    Reply
    • Catalin Bombea

      December 6, 2016 at 2:51 pm

      Hi Michael,
      You can run a simple code that will register the help for that function, and will add it to a specific category from Functions list:

      Sub AddFunctionDescription()
      Application.MacroOptions _
      Macro:="ColorMath", _
      Description:="Perform calculations on colored cells", _
      Category:=3, _
      ArgumentDescriptions:=Array("Input Range", "The Reference range", "Optional: Action as string. Action can be S to SUM, A to AVERAGE, or C to COUNT. If not specified the default Action is SUM")
      End Sub

      The categories are:
      0 No category appears only in All
      1 Financial
      2 Date & Time
      3 Math & Trig
      4 Statistical
      5 Lookup & Reference
      6 Database
      7 Text
      8 Logical
      9 Information
      10 Commands normally hidden
      11 Customizing normally hidden
      12 Macro Control normally hidden
      13 DDE/External normally hidden
      14 User Defined default
      15 Engineering only available if the Analysis Toolpak add-in is installed

      This option is not available to all excel versions, just 2010 up i think.
      Another option is to put all actions in the argument name:
      Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional OptionalAction_S_C_A As String = “S”)
      After you type =ColorMath( in a cell, press Ctrl+Shift+A, this will fill all arguments with argument name:
      =ColorMath(InputRange, ReferenceCell, OptionalAction_S_C_A)
      There are even more complex solutions, to register the descriptions to get the Intellisense functionality, you will find one here: Excel-DNA/IntelliSense
      Cheers,
      Catalin

      Reply
  3. Julian Chen

    November 28, 2016 at 3:47 pm

    Hi Philip,

    How about the other calculations like Max, or Min?

    Regards,

    Julian

    Reply
    • Catalin Bombea

      November 29, 2016 at 10:29 pm

      Hi Julian,
      As you already know, almost anything is possible.
      But I would do things differently: instead of performing a loop for each Action, I would collect all matching cells into a range, using Union VB function, then apply any calculations to the resulting range:

      Option Explicit

      '
      ' Written by Philip Treacy
      ' https://www.myonlinetraininghub.com/count-sum-and-average-colored-cells
      '

      Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")

      Application.Volatile

      ' Action can be S to SUM, A to AVERAGE, or C to COUNT
      ' If not specified the default Action is SUM

      Dim ReferenceColor As Long
      Dim CellCount As Long
      Dim Result As Variant
      Dim Cell As Range, UnionRng As Range

      Action = UCase(Action)
      Result = 0
      CellCount = 0
      ReferenceColor = ReferenceCell.Interior.Color

      For Each Cell In InputRange
      If Cell.Interior.Color = ReferenceColor Then
      If UnionRng Is Nothing Then
      Set UnionRng = Cell
      Else
      Set UnionRng = Union(UnionRng, Cell)
      End If
      End If
      Next Cell

      If Action = "S" Then Result = Application.WorksheetFunction.Sum(UnionRng)
      If Action = "C" Then Result = UnionRng.Cells.Count
      If Action = "A" Then Result = Application.WorksheetFunction.Average(UnionRng)
      If Action = "MIN" Then Result = Application.WorksheetFunction.Min(UnionRng)
      If Action = "MAX" Then Result = Application.WorksheetFunction.Max(UnionRng)
      ColorMath = Result

      End Function

      The average was wrong in the original code, if one of the cells has color but is empty. Using the default Average function is safer.

      Reply
      • Philip Treacy

        November 29, 2016 at 10:50 pm

        Thank Catalin. I was just working on modifying it to use UNION and return a range that could be used by other functions.

        Good spot on the Avg error too.

        Phil

        Reply
      • SunnyKow

        November 30, 2016 at 9:39 am

        Hi Catalin

        Why is the Application.WorksheetFunction.Average(UnionRng) returning a #VALUE! error instead of a #DIV/0! (as in a normal Excel function would) if the cells referred to are all blanks?

        Sunny

        Reply
        • Catalin Bombea

          November 30, 2016 at 8:11 pm

          Hi Sunny,
          when a UDF function hits an error, no matter what caused that error (may be a runtime error, or you have passed a string to a parameter declared as Integer for example – a mismatch), Excel will exit that function without any warning, and will return the VALUE error. In our case, we have a runtime error 1004: “Unable to get the Average property of the WorksheetFunction class”
          If you disable error checking, by placing On Error Resume Next statement before the worksheet functions evaluations, the result will be 0, not VALUE or DIV/0.
          A way to get the same result as the original Average sheet function is to use the Evaluate VB function:
          Result= Evaluate(“=Average(” & UnionRng.Address & “)”)
          This will return DIV/0 error (Error 2007 in VB)
          Of course, there may be the case where UnionRng is Nothing (no cell has the selected color), the code needs to handle this situation too.
          Cheers,
          Catalin

          Reply
          • SunnyKow

            December 1, 2016 at 12:43 am

            Hi Catalin
            Thanks for your detailed explanation.
            I already saw the run time error when I tested that particular line of code (AVERAGE) via the immediate window but was just curious to know why it is like that. Normally I will try to avoid the On Error Resume Next statement unless I have to as it can cause lots of problem if misused.

            Once again thanks again for your clarification.
            Cheers
            Sunny

          • Catalin Bombea

            December 1, 2016 at 3:03 am

            Indeed, it’s better to handle errors instead of disabling them. There are cases though when you intentionally want to force an error, to see if a workbook is open for example, we even know what error number we will have:
            On Error Resume Next
            Set Wb=Workbooks("Test.xlsx")
            If Err.Number=9 then Set Wb=Workbooks.Open("C:\Test.xlsx")
            on error goto 0

            The Evaluate vb function will work without disabling errors, but I had a weird experience with Evaluate function, when the same function in excel sheet works and the VB Evaluate returned an error for exactly the same data. Therefore, I consider the Evaluate function unreliable.
            Cheers,
            Catalin

  4. Sunny Kow

    November 25, 2016 at 6:49 pm

    Hi Philip
    Adding Application.Volatile to the code will allow recalculation when pressing the F9 key. Only drawback is it makes the function volatile.

    Reply
    • Philip Treacy

      November 25, 2016 at 8:19 pm

      Hi Sunny,

      Yes I did think about including this but left it out. But now you have mentioned it, I have put it back in 🙂

      Cheers

      Phil

      Reply
      • Sunny Kow

        November 28, 2016 at 1:02 pm

        Hi Philip

        I noticed that if you change the following line of code
        Result = Result + Cell.Value
        to Result = Result + WorksheetFunction.Sum(Cell)
        your function will no longer trigger an error when you enter text to the cell. Any idea why this is so?

        Sunny

        Reply
        • Philip Treacy

          November 28, 2016 at 8:33 pm

          Hi Sunny,

          SUM ignores the text and only adds the numbers. You could use this, but unless you write your own code to check the input range for errors, you may not get any indication that there is an invalid cell.

          I guess it’s up to the individual how they want to program.

          Cheers

          Phil

          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.