Author: Oscar Cronquist Article last updated on April 16, 2020

Count unique distinct values by cell color 1

This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. A UDF is an Excel Function that you can build yourself which is very handy if there isn't a built-in function that can do it for you.

A UDF is built of VBA code, VBA stands for Visual Basic for Applications. Microsoft Excel has a built-in Editor named Visual Basic Editor that allows you to create macros and UDFs. You will be surprised how easy it is to start building your own macros.

The image above shows the following formula in cell E4:


The first argument is the cell range. The second argument is a single cell with a color you want to use as a condition. Cell D4 has an interior color red.

The formula in cell E4 uses the color of cell D4 to find cells in cell range B3:B22 with the same color. When a matching color is found the cell value is analyzed and possibly counted in order to count all unique distinct values.

For example, the formula returns 6 in cell E4 because there are 6 numbers in red cells and they all are unique. Cell E6 returns 4, numbers in green cells are 87, 84, 78, 75, 78 and  84. Number 84 and 78 have duplicates. Unique distinct values are all values except duplicates are merged into one single value. That leaves us four numbers: 87, 84, 75 and 78.

The formula above won't work until you have copied the VBA code below and pasted it to a code module in your workbook.

Note, this UDF does not work with Conditional Formatting. As far as I know, it is not possible to count conditionally formatted cells using VBA, however, I recommend using the same Conditional Formatting formula that highlights cells to count cells.

User Defined Syntax

CountC(rng As Range, Cell As Range)


rng Required. A cell reference to a cell range that you want to count cells based on cell color.
Cell Required. A cell reference to one cell containing the color you want to count.

VBA code

VBA code is easy to learn, I highly recommend the macro recorder if you are new to VBA. It lets you record a macro based on your actions. Start the recording, perform your actions and then stop the recording. Now, look at the code Excel created.

You can find the "Macro recorder" on the "Developer" tab, if that tab is missing on the ribbon you need to enable it. Then go to the VB Editor to check out the code Excel created for you. See instructions below on how to show the Visual Basic Editor.

I have commented each line in the UDF displayed below, you can copy those lines as well. The apostrophe is a character that allows you to comment your code, it will be ignored when the UDF or macro is started.

'Name the User Defined Function and it's parameters 
Function CountC(rng As Range, Cell As Range)

'Dimension variables and declare data types
Dim CellC As Range, ucoll As New Collection

'Iterate through each cell in range object rng using variable CellC
For Each CellC In rng

    'Check if cell color is equal to second parameter
    If CellC.Interior.Color = Cell.Interior.Color Then

        'Enable error handling
        On Error Resume Next

            'Check if the number of characters are larger than zero and if so add the cell value to collection ucoll
            If Len(CellC) > 0 Then ucoll.Add CellC, CStr(CellC)

        'Disable error handling
        On Error GoTo 0
    End If

'Continue with next cell
Next CellC

'Return values in collection variable ucoll to UDF
CountC = ucoll.Count
End Function

I do recommend commenting your VBA code, it will be a time saver next time you need the macro/UDF and need to do modifications.

Where to put the code?

Count unique distinct values by cell color VB Editor

The image above shows the Visual Basic Editor. The Project Explorer is to the left and an empty window is to the right. There is a top menu and some buttons below the top menu.

The image shows what to press with left mouse button on in order to create a new module in your workbook for the User Defined Function.

  1. Copy above VBA code.
  2. Press shortcut keys Alt + F11 to open the Visual Basic Editor. The Project Explorer window is to the left, it allows you to select which workbook and module to use.
  3. Press with left mouse button on "Insert" on the top menu.
  4. Press with left mouse button on "Module" to create a code module in your workbook.
  5. Paste to code window, see image above.
  6. Exit VB Editor and return to Excel.
Note, make sure you save the workbook with file extension *.xlsm (macro-enabled workbook).

Recommended articles