Counting cells

[tommorgan]

Hi,
I am trying to write a formula that will allow me to count horizontal cells.
I am trying to build a worksheet that allows me to easily see how many
people are working in a particular half hour period, to achieve this I list
all the staff members in columns B to P and in rows 2 to 49 each half hour
period during the day. I enter as a block in colour the hours the staff
member in column B works eg Fred (column B works 8 hours from 9 am to 5 pm,
this will block in black vertically 16 cells each representing half hour) I
then follow the same procedure for columns C to P. The worksheet then has a
series of black cells, which allow easy identification of the coverage of
staffing for particular time periods. I then want to be able to tell in
column Q how many people work each half hour period for example the 9am to
9.30am period. If I add a number to the cell it is easy to use the count
function, but this is messy and is an extra entry I have to make. Is there
any way of counting (or otherwise) the number of cells in a row that are
coloured as opposed to blank cells (I have tried all the count functions
like countblank but no luck) Any help will be greatly appreciated.

Cheers,
Tom

 

[Debra Dalgleish]

You could use conditional formatting to colour the cells, and numbers to
mark the hours worked --

Select cells B249
Choose Format>Conditional Formatting
Leave the first dropdown as 'Cell Value Is'
From the next dropdown, choose 'Equal to'
In the text box, type: 1
Click the Format button
On the Patterns tab, select the black colour
Click OK, click OK

To mark an employee's hours --
Select a block of cells
Type a 1
Press Ctrl+Enter, to enter the 1 in all selected cells

Now, you can use a SUM formula, to count the people working in any
half-hour period.

 

[Dave Hawley]

Hi Tom

I have a Custom Funtion here that may help you:
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****