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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

On Tue, 31 May 2005 13:28:35 -0400, "Tom Ogilvy" <[email protected]>
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

On Tue, 31 May 2005 18:26:24 +0100, "Bob Phillips"
<[email protected]> 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

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" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 31 May 2005 13:28:35 -0400, "Tom Ogilvy" <[email protected]>
> 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

On Tue, 31 May 2005 15:43:29 -0400, "Tom Ogilvy" <[email protected]>
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

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" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 31 May 2005 15:43:29 -0400, "Tom Ogilvy" <[email protected]>
> 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