How to Count Colored Cells in Excel (3 Ways)

Excel doesn’t have a formula to count colored cells, so here are three fantastic, easy ways to count colored cells in Excel.

Way 1: Using a Filter

  1. Select the range of colors with the header cell included
  2. Go to Home > Sort & Filter > Add Filter
  3. Click on the down-arrow in the header cell, Sort by Color, and select the color you want.

Now you can count the sorted cells and see how many of that color there are.

Way 2: Using Name Manager

This way is more powerful, faster, but a bit more complicated.

  1. Select the cell NEXT TO the range you want to count color in (in this picture it’s C3)
  2. Go to Formulas > Name Manager > New Name
  3. Input the new name (something like ColorCount, but it’s your choice)
  4. Input the formula: =GET.CELL(38, B3)
    • (“38” checks the cell color attribute)
  5. Now in the cell next to your colored column, input your new formula: “=ColorCount” and drag it down the column. It will give you the “Numbers” that associate with the colors in the cell next to it.
  6. Now in a separate cell with the color you want to count next to it (E and F columns in the picture), input “=COUNTIF(E3, ColorCount)”
    • Where E3 is the color you want to count, and ColorCount is our Named formula from before.

This will count ALL the colors that match that color!

Way 3: Creating a Formula to Count Color Using VBA

The final, coolest, fastest and most automatic way is to create our very own Formula in Excel that does exactly what we need. We’ll do this using VBA, and you can see how easy it is to create your own formulas!

  1. Select Developer > Visual Basic
  2. In the VBA Window, Select Insert > Module
  3. In the Module window that appears, input the below VBA code, then close the window (it saves automatically).
  4. Finally, use your new formula in any cell: =COUNTINGCOLORS( Range of Colors, Cell with the color you want to count)

The code is:

Function COUNTINGCOLORS(MYRANGE As RANGE, MYCOLOR As RANGE)

Dim COLORCELL As Integer
Dim CURRENTCOUNT As Integer

COLORCELL = MYCOLOR.Interior.ColorIndex

Set CELL = MYRANGE

For Each CELL In MYRANGE
If CELL.Interior.ColorIndex = COLORCELL Then
CURRENTCOUNT = CURRENTCOUNT + 1
End If

Next CELL
COUNTINGCOLORS = CURRENTCOUNT

End Function

See more Excel Tips and Tricks: 

You can see what people are saying about David McLachlan here: REVIEWS

Navigate to Free Project Management and Leadership Articles through the links on the right (or at the bottom if on Mobile) 

PMI PMP 35 PDUs CourseThe Ultimate PMP Project Management Prep Course (35 PDUs)
Agile Certified Practitioner (PMI-ACP 21 PDUs)The Complete Agile Course: PMI-ACP (21 PDUs), Coaching, Jira and MORE! 
50 Project Management Templates Gantt Chart Risk Matrix and more Excel50+ Project Management Templates in Excel and PowerPoint (Gantt Chart, Risk Matrix and more!)
Project Management Plan TemplatesPre-made Project Management Plan Template: Save 100 HOURS!

 

Leave a Reply