Counting of cells with a specific color filled

[Atiq]

Hello experts,

I have an excel workhseet where different cells are filled with different
colors. I need to count no of cells in a specific column with a specific
colour filled. For example i need to count how many cells are filled with
green color and how many with red etc. What formula i should use?

Thanks in advance,
Atiq

 

[dlw]

use the COUNTIF() function, the criteria is whatever was used in conditional
formatting to change the color fill of the cell. If conditional formatting
was not used to color the cell, you can't do it. (unless you use Visual
Basic programming)

 

[Atiq]

Cells are filled manually with a specific color, not with COUNTIF() function.
Do i need to learn VB to perform this calculation?

 

[Jacob Skaria]

There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not
recalculate if you change color. Every time you change the color you will
need to recalculate or wait excel to recalculate...

To install the UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

Function COLORCOUNT(varRange As Range, varColor As Range)
Dim cell As Range
For Each cell In varRange
If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then
ColorCount = ColorCount + 1
End If
Next
End Function

ITo use the as a formula in range A1:A10 try the below; which will count the
number of blue cells....The second argument denotes the colored cell.

=COLORCOUNT(A1:A10,B1)

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
example

 

[Atiq]

Thanks Jacob.
I tried it but is giving error in formula when i apply it. Can you re-check
or may be i am making some mistake in its application...

 

[T. Valko]

But that function will not recalculate if you change color.

Every time you change the color you will need to
recalculate or wait excel to recalculate...

Which is why you should not base calculations on cell formats!

 

[Atiq]

Thanks Bob.
I went through this link and followed instructions. Now forumal is giving no
errors but result of formula is 0 though there are 20 cells in that specified
range (A1:A100) filled with Red color.

Can you please advise whether i am making any mistake?

Thanks in advance, Atiq

 

[Bob Phillips]

No, that is impossible without seeing the workbook, and what you have done
to it, unless you can give a much more detailed explanation.

 

[Gord Dibben]

If you just need a count of those colored cells you can use the COUNT
function on the Status Bar.

Select your range of data then Edit>Find>Format>Format.

Choose a color and Find All.

In the "Found" dialog hit CTRL + a to select all the found cells.

Right-click on Status Bar and Count.


Gord Dibben MS Excel MVP