Hi I have a spreadsheet which has some conditional formatting and
therefore some cells are coloured green, orange or yellow.

Is there a way to count these cells and produce a total number.

For example if row one has 20 columns and 6 of them are coloured
(either green, yellow or orange) can I have a new column at 21 to show
the total number of coloured cells = 6?

Any help appreciated

Thanks

Sam

hi Sam,

this is possible, by using the condition that was used to do the conditional format

--
isabelle


Le 2011-07-30 10:01, Sam Harman a écrit :
> Hi I have a spreadsheet which has some conditional formatting and
> therefore some cells are coloured green, orange or yellow.
>
> Is there a way to count these cells and produce a total number.
>
> For example if row one has 20 columns and 6 of them are coloured
> (either green, yellow or orange) can I have a new column at 21 to show
> the total number of coloured cells = 6?
>
> Any help appreciated
>
> Thanks
>
> Sam

Not easily unless you can count by the condition that caused the color
change.

See Chip Pearson's site for information and code to determine CF
conditions and count them.

http://www.cpearson.com/excel/CFColors.htm


Gord Dibben Microsoft Excel MVP

On Sat, 30 Jul 2011 15:01:40 +0100, Sam Harman <(E-Mail Removed)> wrote:

>Hi I have a spreadsheet which has some conditional formatting and
>therefore some cells are coloured green, orange or yellow.
>
>Is there a way to count these cells and produce a total number.
>
>For example if row one has 20 columns and 6 of them are coloured
>(either green, yellow or orange) can I have a new column at 21 to show
>the total number of coloured cells = 6?
>
>Any help appreciated
>
>Thanks
>
>Sam

"Sam Harman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi I have a spreadsheet which has some conditional formatting and
> therefore some cells are coloured green, orange or yellow.
>
> Is there a way to count these cells and produce a total number.
>
> For example if row one has 20 columns and 6 of them are coloured
> (either green, yellow or orange) can I have a new column at 21 to show
> the total number of coloured cells = 6?
>
> Any help appreciated
>
> Thanks
>
> Sam


Maybe something like

=SUMPRODUCT(--(A2:T2="Pen")+--(A2:T2>=10)+--(A2:T2=5))

in U2?

More generally,

=SUMPRODUCT(--(A2:T2=condition1)+--(A2:T2=condition2)+--(A2:T2=condition3)+...)

More info on Debra Dalgleish's blog -
http://blog.contextures.com/archives...for-underdogs/

I recommend her link to Bob Phillip's write-up.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Sat, 30 Jul 2011 10:14:00 -0500, "Clif McIrvin"
<(E-Mail Removed)> wrote:

>"Sam Harman" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> Hi I have a spreadsheet which has some conditional formatting and
>> therefore some cells are coloured green, orange or yellow.
>>
>> Is there a way to count these cells and produce a total number.
>>
>> For example if row one has 20 columns and 6 of them are coloured
>> (either green, yellow or orange) can I have a new column at 21 to show
>> the total number of coloured cells = 6?
>>
>> Any help appreciated
>>
>> Thanks
>>
>> Sam
>
>
>Maybe something like
>
>=SUMPRODUCT(--(A2:T2="Pen")+--(A2:T2>=10)+--(A2:T2=5))
>
>in U2?
>
>More generally,
>
>=SUMPRODUCT(--(A2:T2=condition1)+--(A2:T2=condition2)+--(A2:T2=condition3)+...)
>
>More info on Debra Dalgleish's blog -
>http://blog.contextures.com/archives...for-underdogs/
>
>I recommend her link to Bob Phillip's write-up.

Hi and thanks for all of your replies and advice.

Clare, if I use your sumprodicy suggestion, what do i use as the
condition as I have tried putting in green, yellow etc but I get an
error and no result....

Any help appreciated

Thanks

Sam

On 7/30/2011 10:01 AM, Sam Harman wrote:
> Hi I have a spreadsheet which has some conditional formatting and
> therefore some cells are coloured green, orange or yellow.
>
> Is there a way to count these cells and produce a total number.
>
> For example if row one has 20 columns and 6 of them are coloured
> (either green, yellow or orange) can I have a new column at 21 to show
> the total number of coloured cells = 6?
>
> Any help appreciated
>
> Thanks
>
> Sam

The free add-in ASAP Utilities has a function to evaluate color index of
a cell's fill

--
Zaidy036

On Sun, 31 Jul 2011 09:45:25 -0400, Zaidy036 <(E-Mail Removed)>
wrote:

>On 7/30/2011 10:01 AM, Sam Harman wrote:
>> Hi I have a spreadsheet which has some conditional formatting and
>> therefore some cells are coloured green, orange or yellow.
>>
>> Is there a way to count these cells and produce a total number.
>>
>> For example if row one has 20 columns and 6 of them are coloured
>> (either green, yellow or orange) can I have a new column at 21 to show
>> the total number of coloured cells = 6?
>>
>> Any help appreciated
>>
>> Thanks
>>
>> Sam
>
>The free add-in ASAP Utilities has a function to evaluate color index of
>a cell's fill


Thanks Zaidy but could you expand please....i have ASAP installed but
have no idea how to use it to count the number of cells that have a
cell background colour

Thanks

Sam

On 7/31/2011 12:25 PM, Sam Harman wrote:
> On Sun, 31 Jul 2011 09:45:25 -0400, Zaidy036<(E-Mail Removed)>
> wrote:
>
>> On 7/30/2011 10:01 AM, Sam Harman wrote:
>>> Hi I have a spreadsheet which has some conditional formatting and
>>> therefore some cells are coloured green, orange or yellow.
>>>
>>> Is there a way to count these cells and produce a total number.
>>>
>>> For example if row one has 20 columns and 6 of them are coloured
>>> (either green, yellow or orange) can I have a new column at 21 to show
>>> the total number of coloured cells = 6?
>>>
>>> Any help appreciated
>>>
>>> Thanks
>>>
>>> Sam
>>
>> The free add-in ASAP Utilities has a function to evaluate color index of
>> a cell's fill
>
>
> Thanks Zaidy but could you expand please....i have ASAP installed but
> have no idea how to use it to count the number of cells that have a
> cell background colour
>
> Thanks
>
> Sam
Here is a function I have used , once for each color:
- the cell color index was taken from a "sample" cell I had set up

=ASAPSUMBYCELLCOLOR(AM$4:AM$171,ASAPCELLCOLORINDEX($AO220))

--
Zaidy036

"Sam Harman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sat, 30 Jul 2011 10:14:00 -0500, "Clif McIrvin"
> <(E-Mail Removed)> wrote:
>
>>"Sam Harman" <(E-Mail Removed)> wrote in message
>>news:(E-Mail Removed)...
>>> Hi I have a spreadsheet which has some conditional formatting and
>>> therefore some cells are coloured green, orange or yellow.
>>>
>>> Is there a way to count these cells and produce a total number.
>>>
>>> For example if row one has 20 columns and 6 of them are coloured
>>> (either green, yellow or orange) can I have a new column at 21 to
>>> show
>>> the total number of coloured cells = 6?
>>>
>>> Any help appreciated
>>>
>>> Thanks
>>>
>>> Sam
>>
>>
>>Maybe something like
>>
>>=SUMPRODUCT(--(A2:T2="Pen")+--(A2:T2>=10)+--(A2:T2=5))
>>
>>in U2?
>>
>>More generally,
>>
>>=SUMPRODUCT(--(A2:T2=condition1)+--(A2:T2=condition2)+--(A2:T2=condition3)+...)
>>
>>More info on Debra Dalgleish's blog -
>>http://blog.contextures.com/archives...for-underdogs/
>>
>>I recommend her link to Bob Phillip's write-up.
>
> Hi and thanks for all of your replies and advice.
>
> Clare, if I use your sumprodicy suggestion, what do i use as the
> condition as I have tried putting in green, yellow etc but I get an
> error and no result....
>
> Any help appreciated
>
> Thanks
>
> Sam


To answer your question, each SUMPRODUCT condition needs to be identical
to the conditional format condition that determines each distinct
background color.

Since you have the ASAP utilities, I think it would be far easier to use
Zaidy's suggestion.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Cliff

I haven't had ASAP installed for a long time, but the feature Zaidy is
speaking of only returned color indexes of manually backgrounded
cells.

i.e. no good for CF colored cells.

Perhaps Zaidy could confirm yea or nay about newer version or ASAP


Gord Dibben Microsoft Excel MVP

On Mon, 1 Aug 2011 16:53:07 -0500, "Clif McIrvin"
<(E-Mail Removed)> wrote:

>Since you have the ASAP utilities, I think it would be far easier to use
>Zaidy's suggestion.