Combine countif and vlookup? Other possible solution?

kapadons

Board Regular
Joined
Jun 16, 2012
Messages
70
a17-3
a211-5
a312-5
a411-8
a511-10
a68-8
a77-3
a86-4
a92-10
a10
a11day8
a12night4

<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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
a17-3
a211-5
a312-5
a411-8
a511-10
a68-8
a77-3
a86-4
a92-10
a10
a11day8
a12night4

<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

Board Regular
Joined
Jun 16, 2012
Messages
70
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

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Shift</td><td style=";">Start</td><td style=";">End</td><td style="text-align: right;;"></td><td style=";">day </td><td style="text-align: right;;">8 </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">7-15</td><td style="text-align: right;;">7 </td><td style="text-align: right;;">15 </td><td style="text-align: right;;"></td><td style=";">night </td><td style="text-align: right;;">7 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">11-17</td><td style="text-align: right;;">11 </td><td style="text-align: right;;">17 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">12-17</td><td style="text-align: right;;">12 </td><td style="text-align: right;;">17 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">11-20</td><td style="text-align: right;;">11 </td><td style="text-align: right;;">20 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">11-22</td><td style="text-align: right;;">11 </td><td style="text-align: right;;">22 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">8-20</td><td style="text-align: right;;">8 </td><td style="text-align: right;;">20 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">7-15</td><td style="text-align: right;;">7 </td><td style="text-align: right;;">15 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">6-16</td><td style="text-align: right;;">6 </td><td style="text-align: right;;">16 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">14-22</td><td style="text-align: right;;">14 </td><td style="text-align: right;;">22 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">"-",A2</font>)-1</font>)*1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=RIGHT(<font color="Blue">A2,LEN(<font color="Red">A2</font>)-FIND(<font color="Red">"-",A2</font>)</font>)*1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=COUNTIF(<font color="Blue">B2:B10,"<14"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=COUNTIF(<font color="Blue">B2:B10,">=14"</font>)+COUNTIF(<font color="Blue">C2:C10,">=17"</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,301
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top