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
0
Knoff (1)
10/30/2005 6:54:01 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
464 Views

Similar Articles

[PageSpeed] 5

In article <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
 Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
0
jemcgimpsey (6723)
10/30/2005 7:00:15 PM
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 Knoff@discussions.microsoft.com> wrote in message
news:6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com...
> 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


0
bob.phillips1 (6510)
10/30/2005 7:01:36 PM
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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
> 
0
AnnKnoff (5)
10/30/2005 7:16:04 PM
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" <AnnKnoff@discussions.microsoft.com> wrote in message 
news:2FBB2D3E-80C2-489B-B71A-D70F29998291@microsoft.com...
> 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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
>>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
>> 

0
terre081 (3244)
10/30/2005 7:45:10 PM
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" <AnnKnoff@discussions.microsoft.com> wrote in message 
> news:2FBB2D3E-80C2-489B-B71A-D70F29998291@microsoft.com...
> > 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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
> >>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
> >> 
> 
> 
0
AnnKnoff (5)
10/30/2005 8:08:01 PM
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" <AnnKnoff@discussions.microsoft.com> wrote in message
news:385055A2-0E32-4019-B7F9-4037EC01D515@microsoft.com...
> 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" <AnnKnoff@discussions.microsoft.com> wrote in message
> > news:2FBB2D3E-80C2-489B-B71A-D70F29998291@microsoft.com...
> > > 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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
> > >>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
> > >>
> >
> >

0
ragdyer1 (4060)
10/30/2005 8:23:47 PM
Just replace SUMIF with COUNTIF

-- 
Regards,

Peo Sjoblom

(No private emails please)


"Ann Knoff" <AnnKnoff@discussions.microsoft.com> wrote in message 
news:385055A2-0E32-4019-B7F9-4037EC01D515@microsoft.com...
> 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" <AnnKnoff@discussions.microsoft.com> wrote in message
>> news:2FBB2D3E-80C2-489B-B71A-D70F29998291@microsoft.com...
>> > 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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
>> >>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
>> >>
>>
>> 

0
terre081 (3244)
10/30/2005 8:28:10 PM
Just use COUNTIF instead of SUMIF.

-- 

HTH

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


"Ann Knoff" <AnnKnoff@discussions.microsoft.com> wrote in message
news:385055A2-0E32-4019-B7F9-4037EC01D515@microsoft.com...
> 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" <AnnKnoff@discussions.microsoft.com> wrote in message
> > news:2FBB2D3E-80C2-489B-B71A-D70F29998291@microsoft.com...
> > > 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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
> > >>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
> > >>
> >
> >


0
bob.phillips1 (6510)
10/30/2005 8:36:33 PM
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" <AnnKnoff@discussions.microsoft.com> wrote in message
> news:385055A2-0E32-4019-B7F9-4037EC01D515@microsoft.com...
> > 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" <AnnKnoff@discussions.microsoft.com> wrote in message
> > > news:2FBB2D3E-80C2-489B-B71A-D70F29998291@microsoft.com...
> > > > 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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
> > > >>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
> > > >>
> > >
> > >
> 
> 
0
AnnKnoff (5)
10/30/2005 8:41:01 PM
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" <AnnKnoff@discussions.microsoft.com> wrote in message
news:56FF66C4-123D-48EF-B5F7-87565B73E1BE@microsoft.com...
> 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" <AnnKnoff@discussions.microsoft.com> wrote in message
> > news:385055A2-0E32-4019-B7F9-4037EC01D515@microsoft.com...
> > > 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" <AnnKnoff@discussions.microsoft.com> wrote in message
> > > > news:2FBB2D3E-80C2-489B-B71A-D70F29998291@microsoft.com...
> > > > > 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 <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>,
> > > > >>  Ann Knoff <Ann Knoff@discussions.microsoft.com> 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).
> > > > >>
> > > >
> > > >
> >
> >

0
ragdyer1 (4060)
10/31/2005 12:35:13 AM
Reply:

Similar Artilces:

*.csv Format Saving Problem
Hello: New at this. I have an Excel spreadsheet that I wish to save in .csv format. It never saves the formatting like Underlines, Bolds, etc., no matter what I do. This is the first time I've played with .csv Is there some reason why, with this format, these Bolds and Underlines can't be saved ? If so, why, and what else is effected ? Thanks, Bob By definition, Comma Separated Value files contain only values, not formatting. In article <aOednUD-bNWQUgDenZ2dnUVZ_sGdnZ2d@comcast.com>, "Robert11" <rgsros@notme.com> wrote: > New at this. > >...

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Irregular formation of numbers
Hi, I hope someone can help, I am trying to format numbers in a column. the numbers are all 10 digits (ie. 0405613020) and from cell to cel are all different. What I am trying to do is for them to take on the format below: 0405.61.30.20 I have tried different things.. but nothing seems to work.. I have ove 250 cells that need to look like that, and it's very long and tedious.. Hope someone can help me.. Diamonelle :confused -- Message posted from http://www.ExcelForum.com Hi You can put this formula in a helper column, copy it down as far a needed and then copy-paste special -...

Formating Dates
I need to update a list of dates to show all days as 01. For example, where date shows 03/28/2009, I need it to read 03/01/2009. I appreciate any guidance! -- LL28 On Thu, 6 May 2010 12:12:01 -0700, LovingLife28 <LovingLife28@discussions.microsoft.com> wrote: >I need to update a list of dates to show all days as 01. For example, where >date shows 03/28/2009, I need it to read 03/01/2009. I appreciate any >guidance! If you want to convert the dates to the first of the month, then, with your date in A1: =A1-DAY(A1)+1 If you want to leave the date a...

So many Tunnel Adapters
Hi, I understand that Tunnel Adapter LAN is for encapsulating IPv6 packets with an IPv4 header so that they can be sent across an IPv4 network. Few queries popped up in my mind based on this :- 1. Why is the numbering for the Tunnel adapter LAN not sequential ? It is like 6,7,12,13,14,15,16. A strange numbering scheme ?! I tried to figure it out by thinking of some arithmetic series. But, it does not seem to fit in. There is a huge gap between 7 and 12. Any ideas ? 2. What is the need for so many number of Tunnel Adapter LAN connection ? Can you tell me a scenario that re...

Date format on a drop down box
I have a dropdown box that is linked to a cell where a date is put that is used for other things. The drop down box is filling itself from a range, which contains a sequential list of dates. I have verified that the dates are indeed dates and not text values. However, whenever I select a date using the drop down list, the link cell reverts to a serial format. I have tried to reformat the cell to be mm/dd/yyyy manually but without any success. Should be a simple reason but I am stumped here. Hi You have to format the cell' where you select the date, too. Formatting the data va...

Is there a maximum number of users that can share an Outlook calen
I have 4 users sharing my Outlook calendar, I cannot see how to remove users, and wondered if there was a maximum as I cannot seem to add more users? Are you using Delegates or the permissions tab on the properties dialog? If there is a real limit, its well above 4 - I know of sites where thousands have permission to view a calendar. There is a practical limit for managing individual users but this can be worked around by giving permission to security groups. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cent...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

Change the assigned chart number
If you use the Macro Recorder and select several charts (in the same worksheet) in secession you can see that Excel has assigned a name to each chart such as Chart 1, Chart 2, ect.. Is there a way to rename say Chart 15 to Chart 3, assuming Chart 3 does not already exist? -- Al Eaton aleaton@bak.rr.com To manually rename a chart object: Hold the Ctrl key, and click on the chart to select it Click in the Name box, to the left of the formula bar Type a new name, or edit the existing name Press the Enter key To programmatically rename a chart object: ActiveSheet.ChartObjects("Ch...

Custom Number Format 04-12-10
I know nothing about using custom number format, and need to undersand when and how to use it. Any link to recommend? Thanks in advance. Regards, Emece.- This Microsoft link expains the concepts fairly well http://office.microsoft.com/en-us/excel/HP051995001033.aspx -- If this helps, please remember to click yes. "Emece" wrote: > I know nothing about using custom number format, and need to undersand when > and how to use it. Any link to recommend? > > Thanks in advance. > > Regards, > Emece.- ...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

Page numbers
I'm using Office 2000 Professional. Is there a way to include page numbers by default instead of having to insert them every time I create a new document? This newsgroup supports Microsoft Access, the relational database. I'm not sure how to interpret your question... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as ...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Conditional Formatting more than 3?
Excel 2000 ... Conditional formatting ... an abosolutely wonderful feature, but why limited to 3 conditions? Is there a way around this? I have a Tab Sheet containing 16 monthly Calendars (dates set by formula) ... These Calendars contain a target date based on date entered into another cell ... Within the Calendar I use Conditional Formatting to high-lite: All dates ... (Format White) ... (Condition 1) Target date ... (Format Yellow) ... (Condition 2) Above said ... I have various +/- tollerance windows around each target date (+/- window is ... 5, 14, 27, & 55 days) ... I have ...

Invoice Numbers
First let my apologies. I posted a question earlier but I can't find we're I posted it! I would like to know if there's a way to auto number an invoice before it's printed? I would think a new table would be required but I'm really not sure how to setup what's required. Thank you in advance. Don Don, It depends on how far in the past you want to generate the Invoice number before it is printed AND the steps you go thru to print the Invoice but I can give you a yes, it can be done. However, more information required to tell how? -- Gina ...

number of OWA certificates
Hi, I have published OWA using ISA server. If i want to use SSL bridging then how many commercial certificates "licenses" I need? I know logically or technically i can purchase a single server certificate and install it both on the ISA and OWA server. So, Do I need to purchase two certificates ? any help appreciated. thanks in advance, I'm not aware of any CA that charges by the number of servers a certificate is installed on. Please don't give them any ideas. :-) Ray "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:3F2...

Formatting Question
I've got a cell that's linked to two other cells which are percentages, and I want my cell to show each of them at 3 decimals. Ex. 6.000%/7.125%, where it links to one cell who's value is 6% and another who's value is 7.125%. The only way I can do it now it shows UP TO three decimals, but shortens it if there are zeros. So the above example would display as 6%/7.125% Any ideas? Thanks in advance, Scott Hi Scott try =TEXT(A1,"0.000%") & "/" & TEXT(A2,"0.000%") where A1 and A2 store your two percent values Frank Scott wrote: > I&#...

how can I enter a 21 digit number in a cell & keep it from change
What changes should i make to my set up if I need to be able to enter a number into a cell that is 20 or more digits long & I want to keep it from changing to scientific notation (as it does when a number is more than 12 digits long)? Excel 2003 professional edition Hi alex, alex wrote: > What changes should i make to my set up if I need to be able to enter > a number into a cell that is 20 or more digits long & I want to keep > it from changing to scientific notation (as it does when a number is > more than 12 digits long)? > Excel 2003 professional edition Putti...

Too Many Different Cell Format
I got error message " Too Many Different Cell Format". Anyone know what caused it? Thank you. for further assistance on this particular issue, click on https://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&tid=1d4df007-3dc4-41f0-8182-aafe32548d4f&cat=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1 -- learn - n - enjoy ------------------------------------- click yes below, if it works for you "Bamboo" wrote: > I got error message " Too Many Different Cell Format". Anyone know ...

How do I show number of records that meet criteria filter
I have a large spreadsheet containing several hundred rows of data. I want to filter the list and I want to be able to show the number of records that match the filter criteria. For example, un-filtered the total number of records in the list is displayed and, when filtered, the number of records in the list that match the criteria is displayed. Can onyone help me please? Kind regards, Cliff Cliff, Here is a little function that returns the count, and works from VBA. Just pass the header cell to the function Function FilteredListCount(rng As Range) Dim iLastrow As Long Dim rngTemp ...

PDF format pagination
I have several reports utilizing Landscape layout. I followed the Report Writers Guide from the SDK with the Width at 11 and Height 8.25 or smaller. However, when I export it to the PDF format, the layout shows up Portrait. How could I change this? The export is fine in Excel with the correct margin setup. Thanks. ...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

tables and numbering
I have created a six column table in Publisher. The items in each column (or cell) are numbered items using " bullets/numbers". The first three columns are listing items by number 1,2,3, etc. However in the last three columns they are acting like they are linked: first column lists 1,2,3, second column lists 4, third column lists 5. Second and third columns should begin with listing "1". The formatting under the bullets/numbering option seems to be formatted correctly. Any ideas? Suggestions? Do a return, then another return, the next column will revert to one...

conditional formatting: multiple conditions with min
Hello, I am using Excel 2007 and I am having an issue with conditional formatting. I have 2 columns of data that I'm working with: Column "c" which contains both names and some empty cells, and column "i" which contains dates and some empty cells. If there's a name in column c then there is definitely a corresponding date in the same row in column i. However, there are sometimes blank cells in column c dates that have dates in column i and some rows that have blanks in both c and i. See the following example: Sue --- 10/1/2010 ___ --- 10/1/2010 Joe --- 10/23/2010 ...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...