totalling staff timesheets

[Beanymonster]

Hi All,
Hopefully this is in the right forums - if not, I apologise.

I have a staff work rota spreadsheet where the shifts are summarised by letters for simplicity e.g.
O= 0hrs,
AL = 8hrs
E = 8 hrs
L= 8.5 hrs
N = 10 hrs

currently the months hours are totalled by hand, converting the letters to numbers which is laborious. Is there a way to auto total the rows based on the letters in it? I am guessing there is a way to total the number of AL (e.g. 7) and multiply that by the value of AL (8), then do the same for other letters for the final monthly totals but exactly how to do it has got me stumped

Any help will be very well received.

Thanks in advance

B

[tghcogo]

Assuming the data for shift type is in column C rows 2 to 20, use this in the cell cell where you need a total for AL

=COUNTIF(C2:C20,"AL") *8

similarly for L

=COUNTIF(C2:C20,"L") *8.5


etc

[Beanymonster]

works great - many thanks

[DonkeyOte]

If you wish to total all in one cell:

=SUM(COUNTIF(range,{"AL","E","L","N"})*{8,8,8.5,10})

this will give you total hours

(change range as appropriate of course)