Calculations using "codes"

[AlexRN]

I'm trying to create an excel spreadsheet to calculate hours for scheduling workers. The problem I'm having is that I want to use "codes" that represent different number of hours, for example, entering "P" in a cell would have a value of "12" that I can use to calculate the total number of hours worked that week.

Attached is a sample spreadsheet. The "CODE" tab at the bottom of the worksheet is where I have the codes I want to use and the numerial representation. I need to be able to somehow get excel to translate the codes into numbers so I can then calculate in the total column.

Any help pointing to the right direction is greatly appreciated. thanks!

[DonkeyOte]

one possible solution:

=SUMPRODUCT(SUMIF(CODES!$A$1:$A$10,$B$3:$G$12,CODES!$B$1:$B$10))

However you will need to ensure each entry on "Schedule" is valid "Code" - eg CL8 is not listed on CODES and will cause the above to ignore that value.

Change ranges to suit.

[AlexRN]

Dear DonkeyOte,

you're a genius!!! thank you very much!!!!