Counting cells

  • Thread starter Thread starter tommorgan
  • Start date Start date
T

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
 
You could use conditional formatting to colour the cells, and numbers to
mark the hours worked --

Select cells B2:P49
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.
 
Back
Top