Count unique distinct values by cell color
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.
User Defined Syntax
CountC(rng As Range, Cell As Range)
Arguments
| 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?
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.
- Copy above VBA code.
- 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.
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to create a code module in your workbook.
- Paste to code window, see image above.
- Exit VB Editor and return to Excel.
Recommended articles
User defined function category
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
Excel categories
10 Responses to “Count unique distinct values by cell color”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Oscar,
A little off topic question, but I am thinking outside the box a little.
I have two charts that display different data sets for the same projects. I want the formatting of the series (projects) to be the same on each chart so they are recognisable. Could similar VBA code be used to format the fill colour on the charts based on how you colour the series names in the data tables?
Mark Graveson,
read this post:
Format fill color on a column chart based on cell color
Oscar,
Thanks again for the answer in the other blog post. I'll also be looking for the response to Dave's question!
Mark.
[…] Mark Graveson asks: […]
Oscar, great function! How difficult would it be to make it count colour alone (so not unique values) and / or use cell font colour or other features also (bold for example) ? Excel has some quick green (with green text) and red (with red text) preset formats that people often use in either conditional formats or by manual application and althought the unique values is very useful in a lot cases this is even more detailed than required for just a count of the formatted conditions. Awesome site by the way, it's been invaluable to me lately! Cheers..
Dave,
great question! I am working on a blog post, check it out next week.
Dave and Mark Graveson,
Read this post:
Count cells by cell and font color
I want to add a pivot table and have a count based on color cells.
Example, I have highlighted in red some cells vs others and want to only have the pivot table count the red cells rather than everything.
Elvia,
I recommend pivot table filters:
https://support.office.com/en-us/article/Filter-data-in-a-PivotTable-cc1ed287-3a97-4e95-b377-ddfafe79fa8f
I have created a table containing conditionally formatted cells to represent the following;
Red scores zero for non compliance
Amber scores 5 for observation
Green scores 10 for compliance
So my table represents the audit results based upon 10 questions whereby 10/10 would show 10 greens
9/10 would be 9 green and one red
I want to show these results in a stacked bar chart whereby the Stack represents the 10 questions but if for example q8 is red then I want my chart to show this !!!
I cannot fathom out how to do this as whatever I try affects the whole series whereas I want it to only apply to each individual part of the stack for each audit
Please help
Many thanks
Mark