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

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" wrote:

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

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

"dlw" wrote:

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" wrote:

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

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)

--
Jacob


"Atiq" wrote:

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

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

--

HTH

Bob

"Atiq" wrote in message
...
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

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...

"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 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)

--
Jacob


"Atiq" wrote:

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

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!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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)

--
Jacob


"Atiq" wrote:

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

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" wrote:

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

--

HTH

Bob

"Atiq" wrote in message
...
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



.

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

--

HTH

Bob

"Atiq" wrote in message
...
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" wrote:

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

--

HTH

Bob

"Atiq" wrote in message
...
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



.

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 EditFindFormatFormat.

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

On Thu, 11 Mar 2010 07:02:01 -0800, Atiq
wrote:

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

"dlw" wrote:

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" wrote:

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