If you have ever tried to count cells by colour in Excel, you may have noticed that Excel does not contain a function to accomplish this.
We can find out how many cells by filtering the list by colour, and maybe use the AGGREGATE function to return the count. This is an option. But having to filter the list each time may not be good enough.
Because functions such as COUNTIF cannot count by cell colour, we will need to create our own custom function (also known as User Defined Functions or UDF’s) to get the job done.
Custom Function to Count Cells by Colour
- Open the Visual Basic Editor by pressing Alt +F11 or by clicking the Visual Basic button on the Developer tab.
- Insert a new module if necessary by clicking the Insert menu and then Module.
- Copy and paste the code below into the code window (you can put this into the current workbook or the Personal Macro Workbook for global use).
Function COUNTIFCOLOUR(Colour As Range, rng As Range) As Long Dim NoCells As Long Dim CellColour As Long Dim rngCell As Range CellColour = Colour.Interior.Color For Each rngCell In rng If rngCell.Interior.Color = CellColour Then NoCells = NoCells + 1 End If Next COUNTIFCOLOUR = NoCells End Function
You can then use this function like any other function in Excel. The arguments do not appear like normal functions, but everything else is the same.
How does it work?
Colour and rng are set up as arguments for the function. So when a user selects a cell containing the colour they want to count, this is assigned to the CellColour variable.
The user will select the range of cells to use and this is assigned to rng.
A For Next loop is then used to loop through each cell of that range checking if it is the colour we are looking for. If so 1 is added to the NoCells variable. This is assigned to the function for returning when the loop finishes.
Want to Learn More?
Want to learn more Excel VBA? Check out the complete Excel VBA online course. You will see more examples of User Defined Functions and a whole lot more.
many thanks for your effort Alan
Hi Alan,
This is ideal, however how do I count the VALUE of the coloured cell, not just how many cells there are.
This is doing my head in and I welcome any help.
Thanks in advance,
Ah I think you want to sum the cell values instead. Just change the line that currently says NoCells = NoCells + 1.
Instead maybe create the variable as SumCells and do SumCells = SumCells + rngCell.value
Hi Alan,
thanks a lot for the help, i tried your inbuild function works well! thumps up!
Now tricky part, it does not read cell having colors under conditional formatting. what do you advise?
Thank again!
Thanks Arzi. Hopefully I can get that problem solved in a future update on this blog post.
my countifcolour function does not show up, help please. Does it matter if I’m using mac?
I don’t use a Mac so may need advice from elsewhere, but there are issues with VBA and Macs.
Alan,
You are a life saver. I appreciate you putting this knowledge out here for the rest of us to find and use.
Thanks
Your welcome Scott, thanks very much for the comments.
Hello Alan,
Were you able to solve the problem of the code not recognizing the conditional formatting that colored a cell? I have red (Conditional Formatting) cells I’d like to count in a row, across multiple columns.
also, How can i automatically update the count when a Red cell has been removed or added? Presently I have to manually update the cell to obtain the correct count.
Thanks much
Bruce
Unfortunately not Bruce. It is very complex and dependent upon what Conditional Formatting is in play sometimes impossible. For more information I hve a couple of useful links.
This one is for ExtendExcel who have a great app call Kutools that can accomplish this – https://www.extendoffice.com/documents/excel/2651-excel-count-cells-by-color-conditional-formatting.html
And for possibly the best description on the intricacies of counting and summing Conditionally Formatted cells you can check this link – http://www.cpearson.com/excel/CFColors.htmI conclusion there is no Conditional Formatting rule that cannot be replicated in a cell formula. So instead of counting coloured cells. I would repeat the condition in a cell formula such as COUNTIFS or SUMPRODUCT.
Hi I don’t know what i am doing wrong but I have the function you wrote in everything i am doing it on excel but on mac. so everything is a little different I don’t know why is not working can you help?
Hi Karina,
Unfortunately I do not own a mac so cannot comment. I am under the impressions though of huge differences in the VBA area.
Alan
thank you, it works great, but I might be doing something wrong, every time I close it , and save it, when I open the file the function disappears and I have to do it all over again, I saved it under excel enable workbook and excel enable template and it is the same, do i need to develop a module every time I open this file ?
No, as long as the file is saved as a macro enabled file, the module should be there when the file is re-opened.
same problem, with me also brother.
cannot find solution.
cant understand,
what want to do for this problem.
Hi Computergaga,
I’ve tried and it won’t work on the colour that’s done with Conditional Formatting. Is there any solutions?
Thank you so much for your help.
Cheers,
Tina
Hi Tina, did you manage to find a solution to this?
can this work if the cells have been coloured with conditional formatting? i cant seem to get it working with it.
Very difficult Steve. This technique does not work with Conditionally formatted cells.
Here is a in depth article from Chip Pearson on it with a solution – http://www.cpearson.com/excel/CFColors.htm
Thank you, I didn’t know about developer in Excel, your code counted what I wanted. I may opt to take online VBA course. I did find when I added or removed a colored cell the formula does not run to update count. I had to manually update: remove and add the colored cell location, then the ‘countifcolour’ code would run, recounting all colored cells. Your response to Bruce states this isn’t fixable with out using other other options outside of excel, is that correct?
Hi Alisha,
It has been a long time since I wrote that post, but I believe the function doesn’t run unless a cell is edited to trigger it.
Although it is a cool UDF example, it is better to write a formula to to sum or count based the criteria behind the reason to colour, rather than counting the colours.
VBA is an awesome skill to learn and I have an online course.
Alan
Hi
I understand this is an old post but would you be able to help.
I like the sum of color cells to be picked up when I use conditional formatting. It picks up the number of colored cells when I fill manually but not with conditional formatting.
Thanks
Hi Adam, yes unfortunately this does not work with Conditional Formatting.
It works, but if I change the colors of the cells, formula/function doesn’t calculate it automatically until I hit enter on the function cell again. why?
Under formulas menu, Calculation Option is set to Automatic though.
Please suggest?
Thank You
UDF’s are only calculated when one of their input values is changed. Unfortunately, a change in colour is not a change in value. This link provides some workarounds but none are perfect.