VLOOKUP across sheets

[Drahmee]

Hi All,

This project is way above my abilities, so I'm asking for help.


Sheet 1 must contain the following data, in the following manner:

The employee's name in Column B, their Department in Column C, the day of the week in column D, their start time in Column E (on 24 hour clock), and their finish time in column F (on 24 hour clock). These five pieces of info must be listed in this manner, for a specific reason.

The sheets are set up in the following way:
Sheet 1 column titles (Columns B-F): Name, Department, Day of Week, Start Time, Finish Time
Sheet 2 and Sheet 3 column titles (Columns A-C): Sales, Shipping, Clerical
Sheet 2 and Sheet 3 row info (Rows 1-24): 00:00 - 01:00, 01:00 - 02:00, etc

If I want to use data from Sheet 1 to calculate staffing numbers for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 2 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.

If I want to use data from Sheet 1 to display staff names for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 3 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.


Thanks in advance for the help!

Doug

 

[Drahmee]

Is there a way I can create a simple formula in Sheet 2 which adds all instances where the following 4 conditions are met in Sheet 1:

Column B = Sales AND
Column C = Monday AND
Column D >= 600 AND
Column E <= 700

...and inserts the result in cell E9 in Sheet 2.




Thanks!

 

[KolGuyXcel]

Is there a way I can create a simple formula in Sheet 2 which adds all instances where the following 4 conditions are met in Sheet 1:

Column B = Sales AND
Column C = Monday AND
Column D >= 600 AND
Column E <= 700

...and inserts the result in cell E9 in Sheet 2.




Thanks!

Use Countifs!

Try

=COUNTIFS(Sheet1!$B:$B,"SALES",Sheet1!$C:$C,"MONDAY",Sheet1!$D:$D,">=600",Sheet1!$E:$E,"<=700")

 

[Drahmee]

This works if Column B is Sales, Column C is Monday, Column D is 600, but not if Column E isn't 700.

I want to break up an 8 hour shift into 1 hour blocks for the schedule, so I know how many people are on shift each hour. The formula doesn't work if D is 600 and E is 1400, as an example.

Thanks!