excel noob looking for some help

[tminogue]

Hi,

At work we have a Rota as people work on shift patterns and then I have to look at the rota and complete a separate spreadsheet basically translating the rota in numbers for a monthly total (in hours)

This is extremely boring and time consuming and I have been trying to work out the right formula to make this task simple.

E.G

in the rota people wither work Early shifts, Late shifts or Long Days:
E = 7.5
L = 7.5
LD = 10
DO = 0 (Day off)

I have created a spreadsheet using list's so that you can only put certain words in cells (E, L, LD, DO) but I firstly don't know how to assign a value to "E" so that every time an E is input the spreadsheet knows its value is 7.5 and then can sum the total hours for all the E (early Shifts) worked that month



[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD]name[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Frid[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]LD[/TD]
[TD]E[/TD]
[TD]LD[/TD]
[TD]LD[/TD]
[TD]DO[/TD]
[TD]37.5[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]DO[/TD]
[TD]DO[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]DO[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]


so i want the table to look at Johns weekly schedule and calculate total hours worked by looking at his shifts LD, E, LD, E, LD, DO and know that it actually means 10 + 7.5 + 10 + 10 + 0 and give a total (37.5)

I hope this makes sense, and thanks in advance for any help you can provide

 

[Kaladin]

Ok. Try

=(7.5*COUNTIF(2:2,"E"))+(7.5*COUNTIF(2:2,"L"))+10*(COUNTIF(2:2,"LD"))

 

[gaz_chops]

Try

=SUM(ISTEXT(B2:F2)*LOOKUP(B2:F2,{"DO","E","L","LD"},{0,7.5,7.5,10}))

 

[gaz_chops]

This one is better, in case there are blanks.
Must be entered with Ctrl, Shift & Enter

=SUM(IFERROR(LOOKUP(B2:F2,{"DO","E","L","LD"},{0,7.5,7.5,10}),0))