Counting Coloured Cells - Conditional Formatting
 

Hi

I'm using Excel 2003 and have a range of cells whose colour is
determined by conditional formatting.

Does anyone please know if there is a way to count the cells in this
range which have a particular colour - say yellow?

I have a feeling I tried this before but could only find a solution
which worked if the cell colour was from ordinary (manual) formatting
rather than conditional formatting.

Many thanks.

Counting Coloured Cells - Conditional Formatting
 

Hi Victor,

Am Fri, 15 Jan 2016 20:53:52 -0000 schrieb Victor Delta:

Does anyone please know if there is a way to count the cells in this
range which have a particular colour - say yellow?

try it with SUMPRODUCT and use the condition for the yellow color in
that formula.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Counting Coloured Cells - Conditional Formatting
 

In article , says...

Hi Victor,

Am Fri, 15 Jan 2016 20:53:52 -0000 schrieb Victor Delta:

Does anyone please know if there is a way to count the cells in this
range which have a particular colour - say yellow?

try it with SUMPRODUCT and use the condition for the yellow color in
that formula.


Regards
Claus B.

Hi Claus

Thanks for your reply although not sure how I would use sumproduct to do
this.

The range of cells is AX2:AX930 and the relevant conditional formatting
formula in, say, cell AX3 is
=AND(BN3="10",AY3<"N",OR(ISBLANK(AX3),TODAY()AX3 +60)).

Victor

Counting Coloured Cells - Conditional Formatting
 

Hi V.D.,

Am Sat, 16 Jan 2016 00:13:19 -0000 schrieb Victor Delta:

The range of cells is AX2:AX930 and the relevant conditional formatting
formula in, say, cell AX3 is
=AND(BN3="10",AY3<"N",OR(ISBLANK(AX3),TODAY()AX3 +60)).

if AX3 is blank the value is 0 and TODAY() is always greater than
AX3+60.
Therefore you only need for CF:
=AND(BN2=10,AY2<"N",TODAY()AX2+60)

And to count the yellow cells you can use:
=SUMPRODUCT(((BN2:BN930=10)*(AY2:AY930<"N"))*(TOD AY()AX2:AX930+60))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Counting Coloured Cells - Conditional Formatting
 

In article , says...

Hi V.D.,

Am Sat, 16 Jan 2016 00:13:19 -0000 schrieb Victor Delta:

The range of cells is AX2:AX930 and the relevant conditional formatting
formula in, say, cell AX3 is
=AND(BN3="10",AY3<"N",OR(ISBLANK(AX3),TODAY()AX3 +60)).

if AX3 is blank the value is 0 and TODAY() is always greater than
AX3+60.
Therefore you only need for CF:
=AND(BN2=10,AY2<"N",TODAY()AX2+60)

And to count the yellow cells you can use:
=SUMPRODUCT(((BN2:BN930=10)*(AY2:AY930<"N"))*(TOD AY()AX2:AX930+60))


Regards
Claus B.

Claus

Very many thanks. That works brilliantly.

Victor