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


9




Reply

Utf

3/11/2010 2:41:01 PM 

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
>


0




Reply

Utf

3/11/2010 2:57:02 PM


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


0




Reply

Utf

3/11/2010 3:02:01 PM


There is no builtin 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
>


6




Reply

Utf

3/11/2010 3:11:01 PM


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

HTH
Bob
"Atiq" <Atiq@discussions.microsoft.com> wrote in message
news:202A912AAC3047BAA45DB8170E9B35CA@microsoft.com...
> 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
>


2




Reply

Bob

3/11/2010 3:15:55 PM


Thanks Jacob.
I tried it but is giving error in formula when i apply it. Can you recheck
or may be i am making some mistake in its application...
"Jacob Skaria" wrote:
> There is no builtin 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
> >


1




Reply

Utf

3/11/2010 3:47:01 PM


>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" <JacobSkaria@discussions.microsoft.com> wrote in message
news:495451BA353E48E6B42C532F34E2ECB2@microsoft.com...
> There is no builtin 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
>>


0




Reply

T

3/11/2010 5:31:11 PM


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" <Atiq@discussions.microsoft.com> wrote in message
> news:202A912AAC3047BAA45DB8170E9B35CA@microsoft.com...
> > 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
> >
>
>
> .
>


0




Reply

Utf

3/12/2010 10:14:01 AM


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" <Atiq@discussions.microsoft.com> wrote in message
news:D848B7130FC2457BB87CFA9DE140E5DD@microsoft.com...
> 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" <Atiq@discussions.microsoft.com> wrote in message
>> news:202A912AAC3047BAA45DB8170E9B35CA@microsoft.com...
>> > 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
>> >
>>
>>
>> .
>>


0




Reply

Bob

3/12/2010 11:35:11 AM


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.
Rightclick on Status Bar and Count.
Gord Dibben MS Excel MVP
On Thu, 11 Mar 2010 07:02:01 0800, Atiq <Atiq@discussions.microsoft.com>
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
>> >


0




Reply

Gord

3/12/2010 4:48:47 PM



9 Replies
47786 Views
(page loaded in 0.004 seconds)
