compare, match and count cell contents between sheets

[bobsone1]

I am having a go at building a staff roster with excl 2007, I am stuck and not sure where to start.

In short; I want to calculate the “hours worked” per month for each person on the roster. Unfortunately I would like to have the formula and result situated in sheet 1 while deriving data from sheets 1 and 2. My reason for wanting it this way is to ensure that whenever shift start/finish times are altered or shifts are added/deleted then the formula wont need to be altered. So; Sheet 1 has the actual roster with all the presented info arriving from Vlookup/data validation type functions. Sheet 2 has tables containing all the relevant info (name, eft, shift codes, shift start/finish times, shift hours, etc) which can be altered whenever necessary. I think there are 3 key steps to this calculation.

1. I want to (somehow) have a solution which starts in the first cell (and steps through each successive cell) of sheet 2's shift code column, identifies the cells unique alpha-numeric shift code and then counts the instances of the code in each persons row on sheet 1.
2. I then want the sum of each unique alpha-numeric shift code to be multiplied by the shift length (hours worked) which is calculated on sheet 2.
3. Lastly, I want to add together each alpha-numeric shift codes "hours worked" sums to give an overall monthly hours worked for each person.

As previously stated I do not know where to start with this so any suggestions are welcome. Regards

[gebobs]

A sample sheet would be helpful with a problem as complex as this.

[bobsone1]

Does it have to be simple?
Perhaps I post what I am working on, there isn't any personal/sensitive info in it.

[gebobs]

That's fine.

[bobsone1]

I have uploaded the roster I am working on (I hope );

I am trying to create a roster on the Roster1 page which only contains information/values drawn from the Lists page.
On the Roster1 page, cells J9:J35 to AQ9:AQ35 are populated with the shift codes from column F7:F52 on the Lists page, with each row representing a single persons total restored shifts.

The column AW9:AW35 on the Roster1 page is intended to display a total restored hours. The problem is, I do not know where to start in creating a solution that works from the Lists page so shift start/finish times can be altered, shifts added/removed etc and the formula will automatically pick up the alterations and display the correct hours rostered total.

1. I want to (somehow) have a solution which starts in the first cell (and steps through each successive cell) of the shift code column (Lists F7:F52), identifies each cells unique alpha-numeric shift code and then counts the instances of the code in each persons row (e.g. J9:AQ9) on Roster1.
2. I then want the sum of each persons unique alpha-numeric shift code to be multiplied by the shift length (hours worked) which is calculated on Lists page (I7:I52).
3. Lastly, I want to add together each alpha-numeric shift codes hours worked sums to give an overall monthly hours worked for each person (Roster1 A9:A35).

Regards...

P.s. We are using a German version of Excel 2007 so we use ; instead of commas in our formulas.

[gebobs]

The equation you use to calculate the time for each shift doesn't work the way you think it does. I think what you are trying to do is to account for those shifts that span midnight and thus would seemingly give negative times if you subtracted the start from the finish. To this end, you add 24 to the end time and then perform the operation. This is both unnecessary and produces a completely erroneous result.

Excel stores dates/times as floating point numbers with each day equal to one and hours/minutes/seconds as appropriate fractions of one. January 0, 1900 (yep, 1/0/1900) is zero and each day thereafter is one more. Thus when you add 24 to a time, you are not adding 24 hours, you are adding 24 days. This will give you incorrect results any time an employee has an N4 shift.

For the table you have of the shifts with starts and finishes, it's all fine except for the one shift that spans midnight. All times are in h:mm format. But there is hidden meaning to the data: the start time for F1 of 6am is actually 6am on 1/0/1900. That may sound odd, but for the purpose of your calculations, it is sufficient and changing to any other date, while not affecting the result, is unnecessary.

Thus what you need to do is set the time for the end of N4 to be 6:36am on 1/1/1900. You just need to make sure the format is h:mm and the superfluous dates will be invisible and the equations will be seamless. Thus all your equations could be something like:

=IF(AND(G7>0,H7>0),H7-G7,"")

I've just changed it for those F1 - N4 in the attached file.

I did an equation for the first employee. It uses a vlookup for each shift so it's a tad kludgy, but it works as far as I can tell. The hours month to date are just over 100.

=COUNTIF(I9:AQ9,"F1*")*VLOOKUP("F1",Lists!F$7:I$52 ,4,FALSE)+COUNTIF(I9:AQ9,"F2*")*VLOOKUP("F2",Lists !F$7:I$52,4,FALSE)+COUNTIF(I9:AQ9,"S1*")*VLOOKUP(" S1",Lists!F$7:I$52,4,FALSE)+COUNTIF(I9:AQ9,"S2*")* VLOOKUP("S2",Lists!F$7:I$52,4,FALSE)+COUNTIF(I9:AQ 9,"S5*")*VLOOKUP("S5",Lists!F$7:I$52,4,FALSE)+COUN TIF(I9:AQ9,"N4*")*VLOOKUP("N4",Lists!F$7:I$52,4,FA LSE)

Perhaps someone else can figure out a better way. The two factors, hours and count, lend themselves nicely to a simple sumproduct equation which I've done to the right of the above, but it would require a bit of redesign. There certainly are other ways too.

But look this over and let me know if I'm on the right track. If you want we can explore more options.

[bobsone1]

Thanks for the work,
The time solution is more elegant (and reliable) than my effort, something to remember. The countif formula is big isn't it :-), your effort is appreciated. Do you know if there is a way to have a formula that has the ability to automatically account for new/removed shifts e.g. should a 3 hour F3 shift be added (09:00-12:00).

Regards

[gebobs]

Well, there may be a way to use the Sumproduct to do it though it would require a bit of redesign I think. Let me think about it and see if I can't figure out something. At least you can look at it and see if maybe there is a way you can think of to implement it.

[bobsone1]

Although this is a bit beyond me I will definitely have a go at a sumproduct solution :-)

Thanks again for the effort and input.

Regards.

[gebobs]

Bah...I can't figure it out.

What would be nice if there was some way you could sum those codes and have it sum the times they refer to. That would make adding a code seamless. The equation I have in the previous attachment would require modification if a code is added. Nothing major, but not seamless.

[bobsone1]

I am not getting anywhere, I wonder, is it possible this idea will have to go to the to hard basket?

[bobsone1]

So; working from the efforts of gebobs combined with a lot of net searches I have come up with this: =COUNTIF(I13:AQ13;"="&Lists!F7)*Lists!I7+COUNTIF.. ....

This solution still needs a specific formula for each shift Code (and calculated Hours) in the Lists sheet.
However, because it is using each specific shift Code and calculated shift Hours cells from the Lists sheet it should respond to shift Code and Hours alterations.

The next problem I need to solve is to come up with a way to make the formula ignore blank cells so that should a new shift type (e.g. F3 @ 08:00-12:00) be added, the formula wont need to be altered to reflect the addition of a new Code>Hours row. That is, I would like to have the formula incorporate (without having to go back and alter the formula) some unused Code>Hours cells for each shift type (Early, Middle, Late,...) in the Lists sheet.

Any Ideas? :-)

Regards.