VBA | Project Success

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

Select the range of colors with the header cell included
Go to Home > Sort & Filter > Add Filter
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.

Select the cell NEXT TO the range you want to count color in (in this picture it’s C3)
Go to Formulas > Name Manager > New Name
Input the new name (something like ColorCount, but it’s your choice)
Input the formula: =GET.CELL(38, B3)
- (“38” checks the cell color attribute)
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.
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!

Select Developer > Visual Basic
In the VBA Window, Select Insert > Module
In the Module window that appears, input the below VBA code, then close the window (it saves automatically).
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)