Combine countif and vlookup? Other possible solution?

[kapadons]

a1		7-3
a2		11-5
a3		12-5
a4		11-8
a5		11-10
a6		8-8
a7		7-3
a8		6-4
a9		2-10
a10
a11	day	8
a12	night	4

<tbody>
</tbody>

Basically I am using excel to make a work schedule. What I want to do is have it update automatically with how many people I have for day or for night. I have done this using a separate sheet and a lookup table, and a "count if" and just having the value from second sheet update to main schedule. The issues I have are; I need to find a way to have some of the values ( IE 11-10, 8-8 etc ) count for both day and night if possible. I also would really like to find a way to cut out the need for a secondary sheet as I'll run into nightmares if I have to add or delete new rows for additional employees from sheet 1 .

My thoughts were to use a countIF and a vlookup in the same formula, but my google and mrexcel forums searchs for how to do that have proven fruitless. Essentially have the formula check the table to see if 6-2 is day or night and if it is day add 1 to the day cell, have 11-10 add 1 to day cell and 1 to night cell.

I currently have 18 different sets of hours possible so I'm fairly certain I need to use a table. Also, the hours posted in main schedule have to be in text format otherwise they get treated as an equation.

Any advice would be greatly appreciated.

 

[Sal Paradise]

a1		7-3
a2		11-5
a3		12-5
a4		11-8
a5		11-10
a6		8-8
a7		7-3
a8		6-4
a9		2-10
a10
a11	day	8
a12	night	4

<tbody>
</tbody>

Basically I am using excel to make a work schedule. What I want to do is have it update automatically with how many people I have for day or for night. I have done this using a separate sheet and a lookup table, and a "count if" and just having the value from second sheet update to main schedule. The issues I have are; I need to find a way to have some of the values ( IE 11-10, 8-8 etc ) count for both day and night if possible. I also would really like to find a way to cut out the need for a secondary sheet as I'll run into nightmares if I have to add or delete new rows for additional employees from sheet 1 .

My thoughts were to use a countIF and a vlookup in the same formula, but my google and mrexcel forums searchs for how to do that have proven fruitless. Essentially have the formula check the table to see if 6-2 is day or night and if it is day add 1 to the day cell, have 11-10 add 1 to day cell and 1 to night cell.

I currently have 18 different sets of hours possible so I'm fairly certain I need to use a table. Also, the hours posted in main schedule have to be in text format otherwise they get treated as an equation.

Any advice would be greatly appreciated.

Could you please specify a bit more clearly what your inputs are, what your desired solution is, and what rules you would use to get from the input to the output?

There is probably a way to do it if you're willing to help us understand the requirements of the problem.

 

[kapadons]

My inputs are work schedules based on a 24 hour AM/PM cycle. IE 7AM-3PM, 11AM-8PM , 5PM-10PM. After I make the scedule I have to go back and manually count how many employees I have assigned for day and how many I have assigned for night (IE 5 day employees and 4 night employees) . I want to have this update automatically depending on what "shifts" I have entered. I dont need nor want the words "day " or "night" to appear on the main sheet. Just want to use it as a parameter for a countif to tally at the bottom.

I would like to have anything that begins before 2PM be counted as a day shift (IE 11am-5pm would be considered day) and anything that starts after 2 pm to be considered night(IE 5pm-10pm would be night). I also am wanting anything that "ends" after 5PM to be considered a night as well. (11am-10pm would be night AND day,11 am- 8pm as well. ) As I said, I have 18 different possible "shifts" so using just an IF formula wont work. I have made a lookup table that has defined the first 2 requirements(what is day and what is night ) I dont know how to define the last one( shifts that are both day and night).

I'm guessing I could create two tables with one defining those shifts as day and one defining them as night and just use the respective table for each formula. But was curious as to whether or not there was another solution.

Also, I basically made a "sheet2", a copy of sheet1, just to clearly identify what shifts would day and what is night by using my current vlookup formula and tables. On that same sheet I used a countIF formula to take that data and give me my total employee number ( how many I have day or night) for each day. I then made that number automatically update to sheet1, my main document. I would like to cut out this middle step of creating a secondary sheet just to clearly define what each shift is before being able to use a countIF formula. Essentially have a formula use my lookup table to define day or night and then add +1 to my cell to give me a grand total. The reason I want to cut this out is I would have to update both sheets identically every time I subtract or add an employee, which isn't a hard issue for myself, but I won't be the only person using this document.


I hope that helped define a little better.

Appreciate your time.

 

[Sal Paradise]

My inputs are work schedules based on a 24 hour AM/PM cycle. IE 7AM-3PM, 11AM-8PM , 5PM-10PM. After I make the scedule I have to go back and manually count how many employees I have assigned for day and how many I have assigned for night (IE 5 day employees and 4 night employees) . I want to have this update automatically depending on what "shifts" I have entered. I dont need nor want the words "day " or "night" to appear on the main sheet. Just want to use it as a parameter for a countif to tally at the bottom.

I would like to have anything that begins before 2PM be counted as a day shift (IE 11am-5pm would be considered day) and anything that starts after 2 pm to be considered night(IE 5pm-10pm would be night). I also am wanting anything that "ends" after 5PM to be considered a night as well. (11am-10pm would be night AND day,11 am- 8pm as well. ) As I said, I have 18 different possible "shifts" so using just an IF formula wont work. I have made a lookup table that has defined the first 2 requirements(what is day and what is night ) I dont know how to define the last one( shifts that are both day and night).

I'm guessing I could create two tables with one defining those shifts as day and one defining them as night and just use the respective table for each formula. But was curious as to whether or not there was another solution.

Also, I basically made a "sheet2", a copy of sheet1, just to clearly identify what shifts would day and what is night by using my current vlookup formula and tables. On that same sheet I used a countIF formula to take that data and give me my total employee number ( how many I have day or night) for each day. I then made that number automatically update to sheet1, my main document. I would like to cut out this middle step of creating a secondary sheet just to clearly define what each shift is before being able to use a countIF formula. Essentially have a formula use my lookup table to define day or night and then add +1 to my cell to give me a grand total. The reason I want to cut this out is I would have to update both sheets identically every time I subtract or add an employee, which isn't a hard issue for myself, but I won't be the only person using this document.


I hope that helped define a little better.

Appreciate your time.

Okay, I see two big issues.

The first is that there is no way to distinguish "AM" and "PM" in your data. For instance, is 7-3 7pm-3am, or 7am-3pm? Is 2-10 2am-10am, or 2pm-10pm?

So the first thing I'd do is create a "start" and an "end" time that is calculated in 24 hour time (rather than AM/PM, PM times have 12 added to them, so 5pm is 17). Then it's easy to do the math:

Excel 2003
	A	B	C	D	E	F
1	Shift	Start	End		day	8
2	7-15	7	15		night	7
3	11-17	11	17
4	12-17	12	17
5	11-20	11	20
6	11-22	11	22
7	8-20	8	20
8	7-15	7	15
9	6-16	6	16
10	14-22	14	22
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND("-",A2)-1)*1
C2	=RIGHT(A2,LEN(A2)-FIND("-",A2))*1
F1	=COUNTIF(B2:B10,"<14")
F2	=COUNTIF(B2:B10,">=14")+COUNTIF(C2:C10,">=17")