Counting of cells with a specific color filled

  • Follow


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

8
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 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
> 
5
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:202A912A-AC30-47BA-A45D-B8170E9B35CA@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 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
> > 
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:495451BA-353E-48E6-B42C-532F34E2ECB2@microsoft.com...
> 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
>> 


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:202A912A-AC30-47BA-A45D-B8170E9B35CA@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:D848B713-0FC2-457B-B87C-FA9DE140E5DD@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:202A912A-AC30-47BA-A45D-B8170E9B35CA@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.

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

-1
Reply Gord 3/12/2010 4:48:47 PM

9 Replies
32093 Views

(page loaded in 0.003 seconds)


Reply: