Hi All,

can we count cells color?
for example in colom A.
in Row 1 = Blue
in Row 2 = Yellow
in Row 3 = Red
in Row 10 = Blue
in Row 53 = Blue

can you give a formula to count Blue....

thanks

reza

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 ColorSum(varRange As Range, varColor As Variant)
Dim arrTemp As Variant
Dim varTemp As Variant
For Each cell In varRange
varTemp = cell.Interior.ColorIndex
If varTemp = -4142 Then varTemp = 0
If varTemp = varColor Then ColorSum = ColorSum + 1
Next
End Function

To 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 colorindex... Below
denotes the other colors...

=colorsum(A1:A10,5)

0 - No Color
1 - Black
2 - White
3 - Red
4 - Green
5 - Blue
6 - Yellow
7 - Magenta
8 - Cyan

If this post helps click Yes
---------------
Jacob Skaria


"reza" wrote:

Hi All,

can we count cells color?
for example in colom A.
in Row 1 = Blue
in Row 2 = Yellow
in Row 3 = Red
in Row 10 = Blue
in Row 53 = Blue

can you give a formula to count Blue....

thanks

reza

greaatt.......

thanks boss

"Jacob Skaria" wrote:

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 ColorSum(varRange As Range, varColor As Variant)
Dim arrTemp As Variant
Dim varTemp As Variant
For Each cell In varRange
varTemp = cell.Interior.ColorIndex
If varTemp = -4142 Then varTemp = 0
If varTemp = varColor Then ColorSum = ColorSum + 1
Next
End Function

To 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 colorindex... Below
denotes the other colors...

=colorsum(A1:A10,5)

0 - No Color
1 - Black
2 - White
3 - Red
4 - Green
5 - Blue
6 - Yellow
7 - Magenta
8 - Cyan

If this post helps click Yes
---------------
Jacob Skaria


"reza" wrote:

Hi All,

can we count cells color?
for example in colom A.
in Row 1 = Blue
in Row 2 = Yellow
in Row 3 = Red
in Row 10 = Blue
in Row 53 = Blue

can you give a formula to count Blue....

thanks

reza