Staffing Rota Trouble - converting letters into a numerical value to be added for a total

hi there i was woundering if anyone could help me, I am designing a staffing rota and have a litle problem. Shifts are based on letters not numbers so when inputting the shifts into the rota table i am wanting this to then be reflected as a number total in my total hours for month collum. so when i type in a letter or letters to represent a shift i am wanting the hours of shifts to be added up in the totals collum. I have only be able to do so for one shift in one cell, i am struggling to relate the process for a selection of letters and the total numbers of days. I was hoping someone would be able to help me.
Each day there are a number of different letter coded shifts on so a variety of options / calculations / hours have to be available i.e M shift is 14 hours, H shift is 7 hours and a WN shift is 10.5 and so on. I am wanting the rota to look as folows and work out as follows;

Days > 1 2 3 4 5 6 7 8 9 10 ...... Total Hours
Jake M H WN WN WN H 44
Pete H C M M H M 37
Sarah C C C WN WN WN C H 40
Lucha

If anyone would be able to help i would be greatful as i am currently just staring at a blank screen and am going crazy cheers.

Hi -

Try the attached spreadsheet. I just had to guess at some of your values.

Hope this helps.

values are
RM = 14.5
M = 14
A = 13
C = 12
H = 7
WN = 10.5

thankyou Login thats perfect after seeing that becomes obvouse tahnkyou greatly just to ammend the hours worked but its great Thankyou hopefully even though late i can kick on witht he design now

you could use a vlookup via reference table

so on a separate sheet you could put
column
0 > 0
A > B
M > 14
H > 7
WN > 10.5

then use
=vlookup()

=VLOOKUP(C3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(D3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(E3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(F3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(G3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(H3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(I3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(J3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(K3,Sheet2!$A$2:$B$20,2,FALSE)+VLOOKUP(L3,Sheet2!$A$2:$B$20,2,FALSE)

see attached

No problem B1 - If your problem is solved, please mark this thread as SOLVED using the Thread Tools immediately above and to the right of your first post.