Count number of cells in a cell range that has a particular color

[myletterboxnp]

Hi all,

A2:A20 cells have different colors such as red, green and yellow.

In A22, what should be the formula to count the number of the red cells in the range A2:A20

 

[Joe4]

Are the cells being colored manually, or by Conditional Formatting Rules?

If they are being colored by Conditional Formatting Rules, you may be able to use the same rules you use to set up the Conditional Formatting Rules in a COUNTIF or COUNTIFS function to count them.

If they are being colored manually, there are of ways to count them.
See this: 6 Ways to Count Colored Cells in Microsoft Excel [Illustrated Guide] | How To Excel

 

[myletterboxnp]

This are manual formatting. I looked at that link earlier and did not understand properly. I just need a simple formula if there is.

 

[Joe4]

This are manual formatting. I looked at that link earlier and did not understand properly. I just need a simple formula if there is.

There is no "simple" native Excel formula, which is why you have to use one of those options.
That is because native Excel formulas only run against formula values, not cell formatting.

Personally, I like the VBA option, because all you have to do is copy/paste the code into the VB Editor to create your own User Defined Function to do this.
Then you can use the function on your spreadsheet, like any other Excel function.
So it doesn't involve much VBA knowledge (the code has already been written for you!), and you can easily use it like any other Excel function.

 

[myletterboxnp]

hi there,

I tried using VBA option but got stuck. For example, I pressed Alt F11 and it showed me a VBA project screen. Now where do I paste the copied codes? Sorry for asking all these small questions.

Kind Regards

 

[Joe4]

See here: Insert and run VBA macros in Excel - step-by-step guide.)

 

[myletterboxnp]

Thanks, so I added to codes (module1) and saved the file also as macro enabled workbook. When I opened the workbook and pressed Alt F8 to run the macro, there's no macro name to choose. What am I doing wrong?

Kind Regards,

 

[Joe4]

Note that if you copy/pasted a FUNCTION, it will not show up in the list of Macros! Functions and Macros (Procedures) are different thing.
If you are using a Function, you just use it like you would any other Excel function (like "SUM", "LEFT", etc).

 

[myletterboxnp]

Sorry its to complicated for me. I give up. Thanks though!

 

[Joe4]

Sorry its to complicated for me. I give up. Thanks though!

Its actually probably not quite as complicated as you think. Let me walk you through it.

If you copied the Function code from the link to a new Module (like it sounds you already did):

Function COLORCOUNT(CountRange As Range, FillCell As Range)
Dim FillColor As Integer
Dim Count As Integer
FillColor = FillCell.Interior.ColorIndex
For Each c In CountRange
    If c.Interior.ColorIndex = FillColor Then
        Count = Count + 1
    End If
Next c
COLORCOUNT = Count
End Function

and then you wanted to count the number of red cells in the range A1:A10 (see image below):

you could do that with the following formula in cell D1 and copy down to D3:

=colorcount(A$1:A$10,C1)

Note that the formula has two arguments.
The first is the range you want to count (A$1:A$10)
The second is a cell with the color that you to count (C1)