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