COUNTIF Function for employee schedule

[BlindAlley]

Afternoon Guys,

Been lurking on this site for a while without posting, so a big thanks to all of you who have unwittingly helped me out over the years.

I am currently working on a spreadsheet for a Hospital Lab Department, I've got most of the sheet ready but I am currently stuck with a COUNTIF formula.

Premise of the worksheet:

The lab has approx 100 employees with each employee being assigned to a work station. These stations are coded and these codes are placed into a Master Schedule. The management staff have been asked to provide a report sheet to the Union on a bi-weekly basis. The idea behind this is to track employee hours for each department, hospital, and workstation.


At one point the codes remained the same eg: MG-V1 so it was a relatively easy requirement of a COUNTIF looking through the two week period on the
Master Schedule and multiplying the number of codes by the appropriate hours. I set up a sheet to do this no problem. However, after I presented the sheet the requirement changed. Instead of the codes remaining the same they are now going to get a prefix such as a 'V',(for vacation) so now, MG-V1 will become VG-V1 whenever an employee is moved from to replace the person going on vacation.

My problem:

There are 106 codes, any employee can use any or all 106 throughout the year, with the prefix applied this now becomes 530 codes.

On my 'bi-weekly' tab, I have tried using COUNTIFS with ** wildcards to no avail, I have tried a nested if function without success, and am now at the point where I am having a brain cramp to come up with an easier solution. I don't really want to use five rows per employee x 100.

I am providing the worksheet hoping that you guys could help me out.

Critique or Diss the worksheet as well if you like :-)

Thanks in advance,

[etaf]

could you use a lookup table to reduce the codes back to the original

VG-V1 = MG-V1
MG-V1 = MG-V1

and then use the countif() as you originally intended
or have i missed something ?

[BlindAlley]

could you use a lookup table to reduce the codes back to the original

VG-V1 = MG-V1
MG-V1 = MG-V1

and then use the countif() as you originally intended
or have i missed something ?

Thanks for the reply Etaf,

I was thinking along those lines but I need to track the codes with the prefix, converting them back may not help.

[BlindAlley]

I thought I had solved this by asking for the efix be added at the end of the code.

Using a wildcard at the end doesn't solve the problem of tracking the prefixed hours.