Totalling numbers that are Conditionally Formatted

Is there any way to total up a column by the colours of a cell?

I have a column that has Green, Yellow, Amber and Red cells and I need to
add up how many of each are there and then calculate the percentage.

Any help would be gratefully received!

Ann

In article <[email protected]>,
Ann Knoff <Ann [email protected]> wrote:

> Is there any way to total up a column by the colours of a cell?
>
> I have a column that has Green, Yellow, Amber and Red cells and I need to
> add up how many of each are there and then calculate the percentage.
>
> Any help would be gratefully received!

Since the numbers are conditionally formatted, use the same condition in
your sum. For instance, if values less than 0 are formatted as red, use

=SUMIF(A:A,"<0")/COUNT(A:A)

(formatted as a percentage).

Are they conditionally formatted. If so, see
http://www.xldynamic.com/source/xld.CFConditions.html. If it just normal
colouring, see http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ann Knoff" <Ann [email protected]> wrote in message
news:[email protected]...
> Is there any way to total up a column by the colours of a cell?
>
> I have a column that has Green, Yellow, Amber and Red cells and I need to
> add up how many of each are there and then calculate the percentage.
>
> Any help would be gratefully received!
>
> Ann

Thank you for responding so quickly. Unfortunately I still can't get it to
work (probably because I am a complete novice)!

The column the conditional formatting in is the O column and the conditions
I am using are as follows:

If the number is between 43 and 56 - Yellow
If the number is between 57 and 84 - Amber
If the number is greater than or equal to 85 - Red

(Oh and the default is Green which is less than or equal to 42 but I
couldn't get 4 conditional formatting items to work)

Any more help you can give would again be greatly appreciated

Ann

"JE McGimpsey" wrote:

> In article <[email protected]>,
> Ann Knoff <Ann [email protected]> wrote:
>
> > Is there any way to total up a column by the colours of a cell?
> >
> > I have a column that has Green, Yellow, Amber and Red cells and I need to
> > add up how many of each are there and then calculate the percentage.
> >
> > Any help would be gratefully received!
>
> Since the numbers are conditionally formatted, use the same condition in
> your sum. For instance, if values less than 0 are formatted as red, use
>
> =SUMIF(A:A,"<0")/COUNT(A:A)
>
> (formatted as a percentage).
>

To sum yellow use

=SUMIF(A1:A100,">=43")-SUMIF(A1:A100,">56")

apply the same technique to the other conditions

--
Regards,

Peo Sjoblom

(No private emails please)


"Ann Knoff" <[email protected]> wrote in message
news:[email protected]...
> Thank you for responding so quickly. Unfortunately I still can't get it
> to
> work (probably because I am a complete novice)!
>
> The column the conditional formatting in is the O column and the
> conditions
> I am using are as follows:
>
> If the number is between 43 and 56 - Yellow
> If the number is between 57 and 84 - Amber
> If the number is greater than or equal to 85 - Red
>
> (Oh and the default is Green which is less than or equal to 42 but I
> couldn't get 4 conditional formatting items to work)
>
> Any more help you can give would again be greatly appreciated
>
> Ann
>
> "JE McGimpsey" wrote:
>
>> In article <[email protected]>,
>> Ann Knoff <Ann [email protected]> wrote:
>>
>> > Is there any way to total up a column by the colours of a cell?
>> >
>> > I have a column that has Green, Yellow, Amber and Red cells and I need
>> > to
>> > add up how many of each are there and then calculate the percentage.
>> >
>> > Any help would be gratefully received!
>>
>> Since the numbers are conditionally formatted, use the same condition in
>> your sum. For instance, if values less than 0 are formatted as red, use
>>
>> =SUMIF(A:A,"<0")/COUNT(A:A)
>>
>> (formatted as a percentage).
>>

Thank you - that works wonderfully, except (and I obviously wasn't clear on
this - sorry) - I only need it to count the number of cells, not their
contents.

ie there is one red cell in the range, whose contents are 118, and the
formula is returning 118 - I need it to return 1

Does that make any sense?

Ann

"Peo Sjoblom" wrote:

> To sum yellow use
>
> =SUMIF(A1:A100,">=43")-SUMIF(A1:A100,">56")
>
> apply the same technique to the other conditions
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Ann Knoff" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for responding so quickly. Unfortunately I still can't get it
> > to
> > work (probably because I am a complete novice)!
> >
> > The column the conditional formatting in is the O column and the
> > conditions
> > I am using are as follows:
> >
> > If the number is between 43 and 56 - Yellow
> > If the number is between 57 and 84 - Amber
> > If the number is greater than or equal to 85 - Red
> >
> > (Oh and the default is Green which is less than or equal to 42 but I
> > couldn't get 4 conditional formatting items to work)
> >
> > Any more help you can give would again be greatly appreciated
> >
> > Ann
> >
> > "JE McGimpsey" wrote:
> >
> >> In article <[email protected]>,
> >> Ann Knoff <Ann [email protected]> wrote:
> >>
> >> > Is there any way to total up a column by the colours of a cell?
> >> >
> >> > I have a column that has Green, Yellow, Amber and Red cells and I need
> >> > to
> >> > add up how many of each are there and then calculate the percentage.
> >> >
> >> > Any help would be gratefully received!
> >>
> >> Since the numbers are conditionally formatted, use the same condition in
> >> your sum. For instance, if values less than 0 are formatted as red, use
> >>
> >> =SUMIF(A:A,"<0")/COUNT(A:A)
> >>
> >> (formatted as a percentage).
> >>
>
>

Try this approach:

ColA ColB ColC
0 42 Green
43 56 Yellow
57 84 Amber
85 10000 Red

Format D1 to D4 as a Percent,
And enter this formula in D1:

=SUMPRODUCT(($O$1:$O$100<>0)*($O$1:$O$100>=A1)*($O$1:$O$100<=B1))/COUNT($O$1
:$O$100)

Copy this formula down to D4.

You should now have your percents alongside your colors.

You *should* enter your largest possible value in B4!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Ann Knoff" <[email protected]> wrote in message
news:[email protected]...
> Thank you - that works wonderfully, except (and I obviously wasn't clear
on
> this - sorry) - I only need it to count the number of cells, not their
> contents.
>
> ie there is one red cell in the range, whose contents are 118, and the
> formula is returning 118 - I need it to return 1
>
> Does that make any sense?
>
> Ann
>
> "Peo Sjoblom" wrote:
>
> > To sum yellow use
> >
> > =SUMIF(A1:A100,">=43")-SUMIF(A1:A100,">56")
> >
> > apply the same technique to the other conditions
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Ann Knoff" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thank you for responding so quickly. Unfortunately I still can't get
it
> > > to
> > > work (probably because I am a complete novice)!
> > >
> > > The column the conditional formatting in is the O column and the
> > > conditions
> > > I am using are as follows:
> > >
> > > If the number is between 43 and 56 - Yellow
> > > If the number is between 57 and 84 - Amber
> > > If the number is greater than or equal to 85 - Red
> > >
> > > (Oh and the default is Green which is less than or equal to 42 but I
> > > couldn't get 4 conditional formatting items to work)
> > >
> > > Any more help you can give would again be greatly appreciated
> > >
> > > Ann
> > >
> > > "JE McGimpsey" wrote:
> > >
> > >> In article <[email protected]>,
> > >> Ann Knoff <Ann [email protected]> wrote:
> > >>
> > >> > Is there any way to total up a column by the colours of a cell?
> > >> >
> > >> > I have a column that has Green, Yellow, Amber and Red cells and I
need
> > >> > to
> > >> > add up how many of each are there and then calculate the
percentage.
> > >> >
> > >> > Any help would be gratefully received!
> > >>
> > >> Since the numbers are conditionally formatted, use the same condition
in
> > >> your sum. For instance, if values less than 0 are formatted as red,
use
> > >>
> > >> =SUMIF(A:A,"<0")/COUNT(A:A)
> > >>
> > >> (formatted as a percentage).
> > >>
> >
> >

Just replace SUMIF with COUNTIF

--
Regards,

Peo Sjoblom

(No private emails please)


"Ann Knoff" <[email protected]> wrote in message
news:[email protected]...
> Thank you - that works wonderfully, except (and I obviously wasn't clear
> on
> this - sorry) - I only need it to count the number of cells, not their
> contents.
>
> ie there is one red cell in the range, whose contents are 118, and the
> formula is returning 118 - I need it to return 1
>
> Does that make any sense?
>
> Ann
>
> "Peo Sjoblom" wrote:
>
>> To sum yellow use
>>
>> =SUMIF(A1:A100,">=43")-SUMIF(A1:A100,">56")
>>
>> apply the same technique to the other conditions
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> (No private emails please)
>>
>>
>> "Ann Knoff" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you for responding so quickly. Unfortunately I still can't get
>> > it
>> > to
>> > work (probably because I am a complete novice)!
>> >
>> > The column the conditional formatting in is the O column and the
>> > conditions
>> > I am using are as follows:
>> >
>> > If the number is between 43 and 56 - Yellow
>> > If the number is between 57 and 84 - Amber
>> > If the number is greater than or equal to 85 - Red
>> >
>> > (Oh and the default is Green which is less than or equal to 42 but I
>> > couldn't get 4 conditional formatting items to work)
>> >
>> > Any more help you can give would again be greatly appreciated
>> >
>> > Ann
>> >
>> > "JE McGimpsey" wrote:
>> >
>> >> In article <[email protected]>,
>> >> Ann Knoff <Ann [email protected]> wrote:
>> >>
>> >> > Is there any way to total up a column by the colours of a cell?
>> >> >
>> >> > I have a column that has Green, Yellow, Amber and Red cells and I
>> >> > need
>> >> > to
>> >> > add up how many of each are there and then calculate the percentage.
>> >> >
>> >> > Any help would be gratefully received!
>> >>
>> >> Since the numbers are conditionally formatted, use the same condition
>> >> in
>> >> your sum. For instance, if values less than 0 are formatted as red,
>> >> use
>> >>
>> >> =SUMIF(A:A,"<0")/COUNT(A:A)
>> >>
>> >> (formatted as a percentage).
>> >>
>>
>>

Just use COUNTIF instead of SUMIF.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ann Knoff" <[email protected]> wrote in message
news:[email protected]...
> Thank you - that works wonderfully, except (and I obviously wasn't clear
on
> this - sorry) - I only need it to count the number of cells, not their
> contents.
>
> ie there is one red cell in the range, whose contents are 118, and the
> formula is returning 118 - I need it to return 1
>
> Does that make any sense?
>
> Ann
>
> "Peo Sjoblom" wrote:
>
> > To sum yellow use
> >
> > =SUMIF(A1:A100,">=43")-SUMIF(A1:A100,">56")
> >
> > apply the same technique to the other conditions
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Ann Knoff" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thank you for responding so quickly. Unfortunately I still can't get
it
> > > to
> > > work (probably because I am a complete novice)!
> > >
> > > The column the conditional formatting in is the O column and the
> > > conditions
> > > I am using are as follows:
> > >
> > > If the number is between 43 and 56 - Yellow
> > > If the number is between 57 and 84 - Amber
> > > If the number is greater than or equal to 85 - Red
> > >
> > > (Oh and the default is Green which is less than or equal to 42 but I
> > > couldn't get 4 conditional formatting items to work)
> > >
> > > Any more help you can give would again be greatly appreciated
> > >
> > > Ann
> > >
> > > "JE McGimpsey" wrote:
> > >
> > >> In article <[email protected]>,
> > >> Ann Knoff <Ann [email protected]> wrote:
> > >>
> > >> > Is there any way to total up a column by the colours of a cell?
> > >> >
> > >> > I have a column that has Green, Yellow, Amber and Red cells and I
need
> > >> > to
> > >> > add up how many of each are there and then calculate the
percentage.
> > >> >
> > >> > Any help would be gratefully received!
> > >>
> > >> Since the numbers are conditionally formatted, use the same condition
in
> > >> your sum. For instance, if values less than 0 are formatted as red,
use
> > >>
> > >> =SUMIF(A:A,"<0")/COUNT(A:A)
> > >>
> > >> (formatted as a percentage).
> > >>
> >
> >

That works wonderfully!

Thank you thank you thank you!

Ann

"Ragdyer" wrote:

> Try this approach:
>
> ColA ColB ColC
> 0 42 Green
> 43 56 Yellow
> 57 84 Amber
> 85 10000 Red
>
> Format D1 to D4 as a Percent,
> And enter this formula in D1:
>
> =SUMPRODUCT(($O$1:$O$100<>0)*($O$1:$O$100>=A1)*($O$1:$O$100<=B1))/COUNT($O$1
> :$O$100)
>
> Copy this formula down to D4.
>
> You should now have your percents alongside your colors.
>
> You *should* enter your largest possible value in B4!
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
>
>
> "Ann Knoff" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you - that works wonderfully, except (and I obviously wasn't clear
> on
> > this - sorry) - I only need it to count the number of cells, not their
> > contents.
> >
> > ie there is one red cell in the range, whose contents are 118, and the
> > formula is returning 118 - I need it to return 1
> >
> > Does that make any sense?
> >
> > Ann
> >
> > "Peo Sjoblom" wrote:
> >
> > > To sum yellow use
> > >
> > > =SUMIF(A1:A100,">=43")-SUMIF(A1:A100,">56")
> > >
> > > apply the same technique to the other conditions
> > >
> > > --
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > (No private emails please)
> > >
> > >
> > > "Ann Knoff" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Thank you for responding so quickly. Unfortunately I still can't get
> it
> > > > to
> > > > work (probably because I am a complete novice)!
> > > >
> > > > The column the conditional formatting in is the O column and the
> > > > conditions
> > > > I am using are as follows:
> > > >
> > > > If the number is between 43 and 56 - Yellow
> > > > If the number is between 57 and 84 - Amber
> > > > If the number is greater than or equal to 85 - Red
> > > >
> > > > (Oh and the default is Green which is less than or equal to 42 but I
> > > > couldn't get 4 conditional formatting items to work)
> > > >
> > > > Any more help you can give would again be greatly appreciated
> > > >
> > > > Ann
> > > >
> > > > "JE McGimpsey" wrote:
> > > >
> > > >> In article <[email protected]>,
> > > >> Ann Knoff <Ann [email protected]> wrote:
> > > >>
> > > >> > Is there any way to total up a column by the colours of a cell?
> > > >> >
> > > >> > I have a column that has Green, Yellow, Amber and Red cells and I
> need
> > > >> > to
> > > >> > add up how many of each are there and then calculate the
> percentage.
> > > >> >
> > > >> > Any help would be gratefully received!
> > > >>
> > > >> Since the numbers are conditionally formatted, use the same condition
> in
> > > >> your sum. For instance, if values less than 0 are formatted as red,
> use
> > > >>
> > > >> =SUMIF(A:A,"<0")/COUNT(A:A)
> > > >>
> > > >> (formatted as a percentage).
> > > >>
> > >
> > >
>
>

You're welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ann Knoff" <[email protected]> wrote in message
news:[email protected]...
> That works wonderfully!
>
> Thank you thank you thank you!
>
> Ann
>
> "Ragdyer" wrote:
>
> > Try this approach:
> >
> > ColA ColB ColC
> > 0 42 Green
> > 43 56 Yellow
> > 57 84 Amber
> > 85 10000 Red
> >
> > Format D1 to D4 as a Percent,
> > And enter this formula in D1:
> >
> >
=SUMPRODUCT(($O$1:$O$100<>0)*($O$1:$O$100>=A1)*($O$1:$O$100<=B1))/COUNT($O$1
> > :$O$100)
> >
> > Copy this formula down to D4.
> >
> > You should now have your percents alongside your colors.
> >
> > You *should* enter your largest possible value in B4!
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> >
> >
> > "Ann Knoff" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thank you - that works wonderfully, except (and I obviously wasn't
clear
> > on
> > > this - sorry) - I only need it to count the number of cells, not their
> > > contents.
> > >
> > > ie there is one red cell in the range, whose contents are 118, and the
> > > formula is returning 118 - I need it to return 1
> > >
> > > Does that make any sense?
> > >
> > > Ann
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > To sum yellow use
> > > >
> > > > =SUMIF(A1:A100,">=43")-SUMIF(A1:A100,">56")
> > > >
> > > > apply the same technique to the other conditions
> > > >
> > > > --
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > (No private emails please)
> > > >
> > > >
> > > > "Ann Knoff" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Thank you for responding so quickly. Unfortunately I still can't
get
> > it
> > > > > to
> > > > > work (probably because I am a complete novice)!
> > > > >
> > > > > The column the conditional formatting in is the O column and the
> > > > > conditions
> > > > > I am using are as follows:
> > > > >
> > > > > If the number is between 43 and 56 - Yellow
> > > > > If the number is between 57 and 84 - Amber
> > > > > If the number is greater than or equal to 85 - Red
> > > > >
> > > > > (Oh and the default is Green which is less than or equal to 42 but
I
> > > > > couldn't get 4 conditional formatting items to work)
> > > > >
> > > > > Any more help you can give would again be greatly appreciated
> > > > >
> > > > > Ann
> > > > >
> > > > > "JE McGimpsey" wrote:
> > > > >
> > > > >> In article <[email protected]>,
> > > > >> Ann Knoff <Ann [email protected]> wrote:
> > > > >>
> > > > >> > Is there any way to total up a column by the colours of a cell?
> > > > >> >
> > > > >> > I have a column that has Green, Yellow, Amber and Red cells and
I
> > need
> > > > >> > to
> > > > >> > add up how many of each are there and then calculate the
> > percentage.
> > > > >> >
> > > > >> > Any help would be gratefully received!
> > > > >>
> > > > >> Since the numbers are conditionally formatted, use the same
condition
> > in
> > > > >> your sum. For instance, if values less than 0 are formatted as
red,
> > use
> > > > >>
> > > > >> =SUMIF(A:A,"<0")/COUNT(A:A)
> > > > >>
> > > > >> (formatted as a percentage).
> > > > >>
> > > >
> > > >
> >
> >