+ Reply to Thread
Results 1 to 8 of 8

Cont question

  1. #1
    Peter
    Guest

    Cont question

    Hi,

    I know I can count the number of appearances of, say, the letter G in
    a particular row or column using the COUNTIF function.

    But can anyone please advise if it is possible to count, say, the
    number of times the letter G appears in a row or column, but only in
    cells that have a coloured background?


    --
    Cheers

    Peter

    Please remove the invalid to reply

  2. #2
    Bob Phillips
    Guest

    Re: Cont question

    Take a look at http://www.xldynamic.com/source/xld.ColourCounter.html, and
    use a formula like

    =SUMPRODUCT(--(ColorIndex(A1:A100)=3),--(A1:A1000="G"))

    --
    HTH

    Bob Phillips

    "Peter" <peter.thomasINVALID@nidum.plus.com> wrote in message
    news:6k5p919ospfr1gcaae64bb9nbos4n8afet@4ax.com...
    > Hi,
    >
    > I know I can count the number of appearances of, say, the letter G in
    > a particular row or column using the COUNTIF function.
    >
    > But can anyone please advise if it is possible to count, say, the
    > number of times the letter G appears in a row or column, but only in
    > cells that have a coloured background?
    >
    >
    > --
    > Cheers
    >
    > Peter
    >
    > Please remove the invalid to reply




  3. #3
    Tom Ogilvy
    Guest

    Re: Cont question

    My best guess would be that you would have to write a custom user defined
    function (UDF) using VBA. No builtin worksheet functions can detect
    background color.

    If the color is produced with conditional formatting, then you could
    probably integrate some built in functions that check the same condition as
    the conditional formatting and add the condition for the value G.

    Perhaps you can post back with particulars.

    --
    Regards,
    Tom Ogilvy

    "Peter" <peter.thomasINVALID@nidum.plus.com> wrote in message
    news:6k5p919ospfr1gcaae64bb9nbos4n8afet@4ax.com...
    > Hi,
    >
    > I know I can count the number of appearances of, say, the letter G in
    > a particular row or column using the COUNTIF function.
    >
    > But can anyone please advise if it is possible to count, say, the
    > number of times the letter G appears in a row or column, but only in
    > cells that have a coloured background?
    >
    >
    > --
    > Cheers
    >
    > Peter
    >
    > Please remove the invalid to reply




  4. #4
    Peter
    Guest

    Re: Cont question

    On Tue, 31 May 2005 13:28:35 -0400, "Tom Ogilvy" <twogilvy@msn.com>
    wrote:

    >My best guess would be that you would have to write a custom user defined
    >function (UDF) using VBA. No builtin worksheet functions can detect
    >background color.
    >
    >If the color is produced with conditional formatting, then you could
    >probably integrate some built in functions that check the same condition as
    >the conditional formatting and add the condition for the value G.
    >
    >Perhaps you can post back with particulars.



    Hi Tom,

    Thanks very much for your reply. The cells that are coloured have been
    set that way - they're weekends - to distinguish them from weekdays.

    An example would be:

    Cells A1 - A5 - no colour
    Cells A6 -A7 - Yellow
    Cells A8 - A12 - no colour
    Cells A13 - A15 - Yellow

    Any one of the cells could have an entry - in this case it would be
    "D". I need to be able to count the number of Yellow coloured cells
    that contain the letter "D".

    As a point of interest this will allow me to deduct the number of such
    cells from the total number of cells that contain the letter "D" to be
    able to work out the number of dayshifts an individual is rostered to
    work on weekends during the year.

    --
    Cheers

    Peter

    Please remove the invalid to reply

  5. #5
    Peter
    Guest

    Re: Cont question

    On Tue, 31 May 2005 18:26:24 +0100, "Bob Phillips"
    <phillips@tiscali.co.uk> wrote:

    >Take a look at http://www.xldynamic.com/source/xld.ColourCounter.html, and
    >use a formula like
    >
    >=SUMPRODUCT(--(ColorIndex(A1:A100)=3),--(A1:A1000="G"))



    Hi Bob,

    Thanks for the suggestion. I shall try it out.


    --
    Cheers

    Peter

    Please remove the invalid to reply

  6. #6
    Tom Ogilvy
    Guest

    Re: Cont question

    Bob gave you a function you can use in the context he stated.

    However, if the dates are listed at the top, then you could identify the
    weekends using the builtin weekday function. This wouldn't work for
    holidays, but you didn't say anything about holidays. the sumproduct
    formula Bob provided could again be utilized to check on the condition of
    the weekday value and the entry in the cell.

    I assume you are working for some period less than a year.

    --
    Regards,
    Tom Ogilvy

    "Peter" <peter.thomasINVALID@nidum.plus.com> wrote in message
    news:i4dp91p4jier4hqh9csut42g7rs3k5ttbe@4ax.com...
    > On Tue, 31 May 2005 13:28:35 -0400, "Tom Ogilvy" <twogilvy@msn.com>
    > wrote:
    >
    > >My best guess would be that you would have to write a custom user defined
    > >function (UDF) using VBA. No builtin worksheet functions can detect
    > >background color.
    > >
    > >If the color is produced with conditional formatting, then you could
    > >probably integrate some built in functions that check the same condition

    as
    > >the conditional formatting and add the condition for the value G.
    > >
    > >Perhaps you can post back with particulars.

    >
    >
    > Hi Tom,
    >
    > Thanks very much for your reply. The cells that are coloured have been
    > set that way - they're weekends - to distinguish them from weekdays.
    >
    > An example would be:
    >
    > Cells A1 - A5 - no colour
    > Cells A6 -A7 - Yellow
    > Cells A8 - A12 - no colour
    > Cells A13 - A15 - Yellow
    >
    > Any one of the cells could have an entry - in this case it would be
    > "D". I need to be able to count the number of Yellow coloured cells
    > that contain the letter "D".
    >
    > As a point of interest this will allow me to deduct the number of such
    > cells from the total number of cells that contain the letter "D" to be
    > able to work out the number of dayshifts an individual is rostered to
    > work on weekends during the year.
    >
    > --
    > Cheers
    >
    > Peter
    >
    > Please remove the invalid to reply




  7. #7
    Peter
    Guest

    Re: Cont question

    On Tue, 31 May 2005 15:43:29 -0400, "Tom Ogilvy" <twogilvy@msn.com>
    wrote:

    >Bob gave you a function you can use in the context he stated.
    >
    >However, if the dates are listed at the top, then you could identify the
    >weekends using the builtin weekday function. This wouldn't work for
    >holidays, but you didn't say anything about holidays. the sumproduct
    >formula Bob provided could again be utilized to check on the condition of
    >the weekday value and the entry in the cell.
    >
    >I assume you are working for some period less than a year.


    Hi Tom,

    Yes, I'm going to try Bob's suggestion out tomorrow.

    I want to count the number of weekend (and public holiday) day shifts
    worked by each man in a 7 man 24/7 rota covering a twelve month
    period, but broken down into months.


    --
    Cheers

    Peter

    Please remove the invalid to reply

  8. #8
    Bob Phillips
    Guest

    Re: Cont question

    Peter,

    If you do have the dates above, you can do it without colours.

    As an example, say the dates are in A1:H1, id (D) in A2:H2, and a list of
    holidays in range with a name of holidays, you can use

    =SUMPRODUCT(--(WEEKDAY(A1:H1,2)>5),--(A2:H2="D"))+SUMPRODUCT(--(ISNUMBER(MAT
    CH(A1:H1,holidays,0))),--(A2:H2="D"))

    This has the advantage of updating automatically when a change is made to
    the worksheet, which the ColorIndex function does not.

    --
    HTH

    Bob Phillips

    "Peter" <peter.thomasINVALID@nidum.plus.com> wrote in message
    news:84jp91lmhhieltmpelu62f91uuqp60lbju@4ax.com...
    > On Tue, 31 May 2005 15:43:29 -0400, "Tom Ogilvy" <twogilvy@msn.com>
    > wrote:
    >
    > >Bob gave you a function you can use in the context he stated.
    > >
    > >However, if the dates are listed at the top, then you could identify the
    > >weekends using the builtin weekday function. This wouldn't work for
    > >holidays, but you didn't say anything about holidays. the sumproduct
    > >formula Bob provided could again be utilized to check on the condition of
    > >the weekday value and the entry in the cell.
    > >
    > >I assume you are working for some period less than a year.

    >
    > Hi Tom,
    >
    > Yes, I'm going to try Bob's suggestion out tomorrow.
    >
    > I want to count the number of weekend (and public holiday) day shifts
    > worked by each man in a 7 man 24/7 rota covering a twelve month
    > period, but broken down into months.
    >
    >
    > --
    > Cheers
    >
    > Peter
    >
    > Please remove the invalid to reply




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1