Suggestions on what function - staff record hours sheet
 

Well I thought I had it sussed but now I am not quite sure.

I am trying to devise a staff record sheet that summarises hours taken off
for annual leave, (AL), sick leave, (SK) study leave, (SD) and compassionate
leave, (CL).

I have the months detailed down in column A and each month has two rows. One
row is for the absence code (AL, SK, etc) and the cell immediately below this
code, there will be the hours that relates to this absence. Just to be
arkward, the times can vary greatly. I could do it fine if the hours were
always the same!

Further down the sheet I have summarised each months' absences according to
code but I need to instruct Excel to know which ones to count and when.
Therein lies the challenge.

The code that I have used so far is
=COUNTIF(A9,"AL")*A10+COUNTIF(B9."AL)*B10+COUNTIF( C9,"AL")*C10 etc etc etc
until the month is then completed. I then redo the same formula for the other
absence codes.

This obviously takes a long time to do and then sometimes it doesn't seem to
work and just gives #### in the total box. This might be just after the
formula has worked so I know it's not a trying error or a missing character.
Strangely though, I have copied to the formula and pasted it into the totals
box for the other months and it seems to work.

Any suggestions and recommendations gratel received. Be gently though, all I
know about Excel has been self taught or from the initernet!

Suggestions on what function - staff record hours sheet
 

=SUMIF(A9:Z9,"AL",A10:Z10)


"TraceyJ" wrote:

Well I thought I had it sussed but now I am not quite sure.

I am trying to devise a staff record sheet that summarises hours taken off
for annual leave, (AL), sick leave, (SK) study leave, (SD) and compassionate
leave, (CL).

I have the months detailed down in column A and each month has two rows. One
row is for the absence code (AL, SK, etc) and the cell immediately below this
code, there will be the hours that relates to this absence. Just to be
arkward, the times can vary greatly. I could do it fine if the hours were
always the same!

Further down the sheet I have summarised each months' absences according to
code but I need to instruct Excel to know which ones to count and when.
Therein lies the challenge.

The code that I have used so far is
=COUNTIF(A9,"AL")*A10+COUNTIF(B9."AL)*B10+COUNTIF( C9,"AL")*C10 etc etc etc
until the month is then completed. I then redo the same formula for the other
absence codes.

This obviously takes a long time to do and then sometimes it doesn't seem to
work and just gives #### in the total box. This might be just after the
formula has worked so I know it's not a trying error or a missing character.
Strangely though, I have copied to the formula and pasted it into the totals
box for the other months and it seems to work.

Any suggestions and recommendations gratel received. Be gently though, all I
know about Excel has been self taught or from the initernet!