Hi Everyone,
I need a little help with a project I am working on.

I am in the process of making a schedule for work through Excel for all our
employees. I work at a hospital and we have all different shifts. We do the
schedule for 2 weeks at a time. What I did was make all the shifts people
work into a dropdown selectable list. Now the part I need help with is, I'd
like the spreadsheet to be able to count the number of personnel assigned for
each shift.

For example is I have 3 people selected as working 7a-3:30p, 2 people
7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the bottom of the
spreadsheet, to automatically say that there are 5 people on day shift
(7a-3:30p) and then 7 people covering evening shift (3p-11:30). If someone
can tell me how I have to go about this or is able to help it me out, it
would be greatly apprecaited. If needed, I can send you my spreadsheet.

Thanks for all your help!

JC
If I understand you correctly, you have one column with the drop-downs.
Say this column is Column C and the shifts start with C2 and go to C50. So
in this column you will have a number of this shift, a number of that shift
and a number of the other shift. You want to know how many of each shift
you have. If this is correct, you need to use the COUNTIF function. Say
that , in the drop-down list, the first shift is written as "7a-3:30p".
Pick a cell in which you want the number of the first shift. In that cell
type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English, look in
the range C2 to C50 and count the number of cells that have 7a-3:30p in
them. Do the same in 2 other cells for the other 2 shifts. Is this what
you want? Your numbers in your example are somewhat scrambled, so maybe you
want to add 2 shifts together. If so, write the formula like:
=COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH Otto
"JC" <(E-Mail Removed)> wrote in message
news:584D0BF5-B398-4CBA-984C-(E-Mail Removed)...
> Hi Everyone,
> I need a little help with a project I am working on.
>
> I am in the process of making a schedule for work through Excel for all
> our
> employees. I work at a hospital and we have all different shifts. We do
> the
> schedule for 2 weeks at a time. What I did was make all the shifts people
> work into a dropdown selectable list. Now the part I need help with is,
> I'd
> like the spreadsheet to be able to count the number of personnel assigned
> for
> each shift.
>
> For example is I have 3 people selected as working 7a-3:30p, 2 people
> 7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the bottom of
> the
> spreadsheet, to automatically say that there are 5 people on day shift
> (7a-3:30p) and then 7 people covering evening shift (3p-11:30). If someone
> can tell me how I have to go about this or is able to help it me out, it
> would be greatly apprecaited. If needed, I can send you my spreadsheet.
>
> Thanks for all your help!

Thank you for the quick reply.

Sorry for the confusion. Let me see if I can explain this a little better.
But I believe what you replied will work, if you can just let mek now if what
you explained is what I have described below: "Day Shift" is considered
anything between 7a-3:30p. While we have some people that work the entire day
shift, their shift is 7a-3:30p, we have some people that only work half of
the day shift and stay into second shift. Their shift might be 11a-11p
(meaning they are working 4 hours on days and 8 hours on evenings).

As you were saying for say today, Tuesday November 25: The entire schedule
for today would be listed from C5:C50, tomorrow would be listed from D5:50,
etc.

My dropdown list choices are made up of all the possible shifts, which I
listed below:
7a-11:30a
7a-3:30p
7a-7:30p
7a-11:30p
11a-3:30p
11a-7:30p
11a-11:30p
11a-3:30a
3p-7:30p
3p-11:30p
3p-3:30a
3p-7:30a
7p-11:30p
7p-3:30a
7p-7:30a
11p-3:30a
11p-7:30a
3a-7:30a
3a-11:30a
3a-3:30p
3a-7:30p

Thanks again for all your help.

"Otto Moehrbach" wrote:

> JC
> If I understand you correctly, you have one column with the drop-downs.
> Say this column is Column C and the shifts start with C2 and go to C50. So
> in this column you will have a number of this shift, a number of that shift
> and a number of the other shift. You want to know how many of each shift
> you have. If this is correct, you need to use the COUNTIF function. Say
> that , in the drop-down list, the first shift is written as "7a-3:30p".
> Pick a cell in which you want the number of the first shift. In that cell
> type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English, look in
> the range C2 to C50 and count the number of cells that have 7a-3:30p in
> them. Do the same in 2 other cells for the other 2 shifts. Is this what
> you want? Your numbers in your example are somewhat scrambled, so maybe you
> want to add 2 shifts together. If so, write the formula like:
> =COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH Otto
> "JC" <(E-Mail Removed)> wrote in message
> news:584D0BF5-B398-4CBA-984C-(E-Mail Removed)...
> > Hi Everyone,
> > I need a little help with a project I am working on.
> >
> > I am in the process of making a schedule for work through Excel for all
> > our
> > employees. I work at a hospital and we have all different shifts. We do
> > the
> > schedule for 2 weeks at a time. What I did was make all the shifts people
> > work into a dropdown selectable list. Now the part I need help with is,
> > I'd
> > like the spreadsheet to be able to count the number of personnel assigned
> > for
> > each shift.
> >
> > For example is I have 3 people selected as working 7a-3:30p, 2 people
> > 7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the bottom of
> > the
> > spreadsheet, to automatically say that there are 5 people on day shift
> > (7a-3:30p) and then 7 people covering evening shift (3p-11:30). If someone
> > can tell me how I have to go about this or is able to help it me out, it
> > would be greatly apprecaited. If needed, I can send you my spreadsheet.
> >
> > Thanks for all your help!
>
>
>

JC
I don't know what you were getting at in your most recent post. Did you
mean to say that you need to break up a shift so that part of it is counted
in another shift? Post back and clarify what you mean. Otto
"JC" <(E-Mail Removed)> wrote in message
news:C2EF0008-C010-49DC-AD2F-(E-Mail Removed)...
> Thank you for the quick reply.
>
> Sorry for the confusion. Let me see if I can explain this a little better.
> But I believe what you replied will work, if you can just let mek now if
> what
> you explained is what I have described below: "Day Shift" is considered
> anything between 7a-3:30p. While we have some people that work the entire
> day
> shift, their shift is 7a-3:30p, we have some people that only work half of
> the day shift and stay into second shift. Their shift might be 11a-11p
> (meaning they are working 4 hours on days and 8 hours on evenings).
>
> As you were saying for say today, Tuesday November 25: The entire schedule
> for today would be listed from C5:C50, tomorrow would be listed from
> D5:50,
> etc.
>
> My dropdown list choices are made up of all the possible shifts, which I
> listed below:
> 7a-11:30a
> 7a-3:30p
> 7a-7:30p
> 7a-11:30p
> 11a-3:30p
> 11a-7:30p
> 11a-11:30p
> 11a-3:30a
> 3p-7:30p
> 3p-11:30p
> 3p-3:30a
> 3p-7:30a
> 7p-11:30p
> 7p-3:30a
> 7p-7:30a
> 11p-3:30a
> 11p-7:30a
> 3a-7:30a
> 3a-11:30a
> 3a-3:30p
> 3a-7:30p
>
> Thanks again for all your help.
>
> "Otto Moehrbach" wrote:
>
>> JC
>> If I understand you correctly, you have one column with the
>> drop-downs.
>> Say this column is Column C and the shifts start with C2 and go to C50.
>> So
>> in this column you will have a number of this shift, a number of that
>> shift
>> and a number of the other shift. You want to know how many of each shift
>> you have. If this is correct, you need to use the COUNTIF function. Say
>> that , in the drop-down list, the first shift is written as "7a-3:30p".
>> Pick a cell in which you want the number of the first shift. In that
>> cell
>> type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English, look in
>> the range C2 to C50 and count the number of cells that have 7a-3:30p in
>> them. Do the same in 2 other cells for the other 2 shifts. Is this what
>> you want? Your numbers in your example are somewhat scrambled, so maybe
>> you
>> want to add 2 shifts together. If so, write the formula like:
>> =COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH Otto
>> "JC" <(E-Mail Removed)> wrote in message
>> news:584D0BF5-B398-4CBA-984C-(E-Mail Removed)...
>> > Hi Everyone,
>> > I need a little help with a project I am working on.
>> >
>> > I am in the process of making a schedule for work through Excel for all
>> > our
>> > employees. I work at a hospital and we have all different shifts. We do
>> > the
>> > schedule for 2 weeks at a time. What I did was make all the shifts
>> > people
>> > work into a dropdown selectable list. Now the part I need help with is,
>> > I'd
>> > like the spreadsheet to be able to count the number of personnel
>> > assigned
>> > for
>> > each shift.
>> >
>> > For example is I have 3 people selected as working 7a-3:30p, 2 people
>> > 7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the bottom
>> > of
>> > the
>> > spreadsheet, to automatically say that there are 5 people on day shift
>> > (7a-3:30p) and then 7 people covering evening shift (3p-11:30). If
>> > someone
>> > can tell me how I have to go about this or is able to help it me out,
>> > it
>> > would be greatly apprecaited. If needed, I can send you my spreadsheet.
>> >
>> > Thanks for all your help!
>>
>>
>>

Otto,
Hope this is clear. Some time frames need to be counted as coverage for
another shift.

For example, if I am counting the number working 7a to 3p and also 3p to 11p
and I have someone working 7a to 11p, that person would need to be counted
for both the day shift and also evening shift.

Hope that is clear

"Otto Moehrbach" wrote:

> JC
> I don't know what you were getting at in your most recent post. Did you
> mean to say that you need to break up a shift so that part of it is counted
> in another shift? Post back and clarify what you mean. Otto
> "JC" <(E-Mail Removed)> wrote in message
> news:C2EF0008-C010-49DC-AD2F-(E-Mail Removed)...
> > Thank you for the quick reply.
> >
> > Sorry for the confusion. Let me see if I can explain this a little better.
> > But I believe what you replied will work, if you can just let mek now if
> > what
> > you explained is what I have described below: "Day Shift" is considered
> > anything between 7a-3:30p. While we have some people that work the entire
> > day
> > shift, their shift is 7a-3:30p, we have some people that only work half of
> > the day shift and stay into second shift. Their shift might be 11a-11p
> > (meaning they are working 4 hours on days and 8 hours on evenings).
> >
> > As you were saying for say today, Tuesday November 25: The entire schedule
> > for today would be listed from C5:C50, tomorrow would be listed from
> > D5:50,
> > etc.
> >
> > My dropdown list choices are made up of all the possible shifts, which I
> > listed below:
> > 7a-11:30a
> > 7a-3:30p
> > 7a-7:30p
> > 7a-11:30p
> > 11a-3:30p
> > 11a-7:30p
> > 11a-11:30p
> > 11a-3:30a
> > 3p-7:30p
> > 3p-11:30p
> > 3p-3:30a
> > 3p-7:30a
> > 7p-11:30p
> > 7p-3:30a
> > 7p-7:30a
> > 11p-3:30a
> > 11p-7:30a
> > 3a-7:30a
> > 3a-11:30a
> > 3a-3:30p
> > 3a-7:30p
> >
> > Thanks again for all your help.
> >
> > "Otto Moehrbach" wrote:
> >
> >> JC
> >> If I understand you correctly, you have one column with the
> >> drop-downs.
> >> Say this column is Column C and the shifts start with C2 and go to C50.
> >> So
> >> in this column you will have a number of this shift, a number of that
> >> shift
> >> and a number of the other shift. You want to know how many of each shift
> >> you have. If this is correct, you need to use the COUNTIF function. Say
> >> that , in the drop-down list, the first shift is written as "7a-3:30p".
> >> Pick a cell in which you want the number of the first shift. In that
> >> cell
> >> type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English, look in
> >> the range C2 to C50 and count the number of cells that have 7a-3:30p in
> >> them. Do the same in 2 other cells for the other 2 shifts. Is this what
> >> you want? Your numbers in your example are somewhat scrambled, so maybe
> >> you
> >> want to add 2 shifts together. If so, write the formula like:
> >> =COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH Otto
> >> "JC" <(E-Mail Removed)> wrote in message
> >> news:584D0BF5-B398-4CBA-984C-(E-Mail Removed)...
> >> > Hi Everyone,
> >> > I need a little help with a project I am working on.
> >> >
> >> > I am in the process of making a schedule for work through Excel for all
> >> > our
> >> > employees. I work at a hospital and we have all different shifts. We do
> >> > the
> >> > schedule for 2 weeks at a time. What I did was make all the shifts
> >> > people
> >> > work into a dropdown selectable list. Now the part I need help with is,
> >> > I'd
> >> > like the spreadsheet to be able to count the number of personnel
> >> > assigned
> >> > for
> >> > each shift.
> >> >
> >> > For example is I have 3 people selected as working 7a-3:30p, 2 people
> >> > 7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the bottom
> >> > of
> >> > the
> >> > spreadsheet, to automatically say that there are 5 people on day shift
> >> > (7a-3:30p) and then 7 people covering evening shift (3p-11:30). If
> >> > someone
> >> > can tell me how I have to go about this or is able to help it me out,
> >> > it
> >> > would be greatly apprecaited. If needed, I can send you my spreadsheet.
> >> >
> >> > Thanks for all your help!
> >>
> >>
> >>
>
>
>

JC

Excel is a beast of logic so I'm trying to put what you say into a logic
that Excel can understand. I assume that 7a-3:30p is the first or day
shift, 3p-11:30p is the evening or second shift, and 11:30p-7:30a is the
third or night shift. Is that right? Assuming it is, I've made the
following table where "A" is the first shift, "B" is the second shift, and
"C" is the third shift. A combination of 2 or 3 of those 3 letters means
that someone working that shift should be counted in each of those letter
shifts. Does that make sense? If it does, are the combinations I show
correct? Otto

7a-11:30a A

7a-3:30p A

7a-7:30p AB

7a-11:30p ABC

11a-3:30p A

11a-7:30p AB

11a-11:30p AB

11a-3:30a A

3p-7:30p B

3p-11:30p B

3p-3:30a ABC

3p-7:30a BC

7p-11:30p B

7p-3:30a BC

7p-7:30a BC

11p-3:30a C

11p-7:30a C

3a-7:30a C

3a-11:30a CA

3a-3:30p CA

3a-7:30p CAB

"JC" <(E-Mail Removed)> wrote in message
news:344DBD9C-D2A3-4844-8A36-(E-Mail Removed)...
> Otto,
> Hope this is clear. Some time frames need to be counted as coverage for
> another shift.
>
> For example, if I am counting the number working 7a to 3p and also 3p to
> 11p
> and I have someone working 7a to 11p, that person would need to be counted
> for both the day shift and also evening shift.
>
> Hope that is clear
>
> "Otto Moehrbach" wrote:
>
>> JC
>> I don't know what you were getting at in your most recent post. Did
>> you
>> mean to say that you need to break up a shift so that part of it is
>> counted
>> in another shift? Post back and clarify what you mean. Otto
>> "JC" <(E-Mail Removed)> wrote in message
>> news:C2EF0008-C010-49DC-AD2F-(E-Mail Removed)...
>> > Thank you for the quick reply.
>> >
>> > Sorry for the confusion. Let me see if I can explain this a little
>> > better.
>> > But I believe what you replied will work, if you can just let mek now
>> > if
>> > what
>> > you explained is what I have described below: "Day Shift" is
>> > considered
>> > anything between 7a-3:30p. While we have some people that work the
>> > entire
>> > day
>> > shift, their shift is 7a-3:30p, we have some people that only work half
>> > of
>> > the day shift and stay into second shift. Their shift might be 11a-11p
>> > (meaning they are working 4 hours on days and 8 hours on evenings).
>> >
>> > As you were saying for say today, Tuesday November 25: The entire
>> > schedule
>> > for today would be listed from C5:C50, tomorrow would be listed from
>> > D5:50,
>> > etc.
>> >
>> > My dropdown list choices are made up of all the possible shifts, which
>> > I
>> > listed below:
>> > 7a-11:30a
>> > 7a-3:30p
>> > 7a-7:30p
>> > 7a-11:30p
>> > 11a-3:30p
>> > 11a-7:30p
>> > 11a-11:30p
>> > 11a-3:30a
>> > 3p-7:30p
>> > 3p-11:30p
>> > 3p-3:30a
>> > 3p-7:30a
>> > 7p-11:30p
>> > 7p-3:30a
>> > 7p-7:30a
>> > 11p-3:30a
>> > 11p-7:30a
>> > 3a-7:30a
>> > 3a-11:30a
>> > 3a-3:30p
>> > 3a-7:30p
>> >
>> > Thanks again for all your help.
>> >
>> > "Otto Moehrbach" wrote:
>> >
>> >> JC
>> >> If I understand you correctly, you have one column with the
>> >> drop-downs.
>> >> Say this column is Column C and the shifts start with C2 and go to
>> >> C50.
>> >> So
>> >> in this column you will have a number of this shift, a number of that
>> >> shift
>> >> and a number of the other shift. You want to know how many of each
>> >> shift
>> >> you have. If this is correct, you need to use the COUNTIF function.
>> >> Say
>> >> that , in the drop-down list, the first shift is written as
>> >> "7a-3:30p".
>> >> Pick a cell in which you want the number of the first shift. In that
>> >> cell
>> >> type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English, look
>> >> in
>> >> the range C2 to C50 and count the number of cells that have 7a-3:30p
>> >> in
>> >> them. Do the same in 2 other cells for the other 2 shifts. Is this
>> >> what
>> >> you want? Your numbers in your example are somewhat scrambled, so
>> >> maybe
>> >> you
>> >> want to add 2 shifts together. If so, write the formula like:
>> >> =COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH Otto
>> >> "JC" <(E-Mail Removed)> wrote in message
>> >> news:584D0BF5-B398-4CBA-984C-(E-Mail Removed)...
>> >> > Hi Everyone,
>> >> > I need a little help with a project I am working on.
>> >> >
>> >> > I am in the process of making a schedule for work through Excel for
>> >> > all
>> >> > our
>> >> > employees. I work at a hospital and we have all different shifts. We
>> >> > do
>> >> > the
>> >> > schedule for 2 weeks at a time. What I did was make all the shifts
>> >> > people
>> >> > work into a dropdown selectable list. Now the part I need help with
>> >> > is,
>> >> > I'd
>> >> > like the spreadsheet to be able to count the number of personnel
>> >> > assigned
>> >> > for
>> >> > each shift.
>> >> >
>> >> > For example is I have 3 people selected as working 7a-3:30p, 2
>> >> > people
>> >> > 7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the
>> >> > bottom
>> >> > of
>> >> > the
>> >> > spreadsheet, to automatically say that there are 5 people on day
>> >> > shift
>> >> > (7a-3:30p) and then 7 people covering evening shift (3p-11:30). If
>> >> > someone
>> >> > can tell me how I have to go about this or is able to help it me
>> >> > out,
>> >> > it
>> >> > would be greatly apprecaited. If needed, I can send you my
>> >> > spreadsheet.
>> >> >
>> >> > Thanks for all your help!
>> >>
>> >>
>> >>
>>
>>
>>

Yes, I've corrected below. Basically you were correct, I just made a few
changes.

If you have an better idea on how I can go about listing these, I am welcome
to suggestions also.

> 7a-11:30a A
>
> 7a-3:30p A
>
> 7a-11:30p AB
>
> 11a-3:30p A
>
> 11a-7:30p AB
>
> 11a-11:30p AB
>
> 11a-3:30a ABC
>
> 3p-7:30p B
>
> 3p-11:30p B
>
> 3p-3:30a BC
>
> 3p-7:30a BC
>
> 7p-11:30p B
>
> 7p-3:30a BC
>
> 7p-7:30a BC
>
> 11p-3:30a C
>
> 11p-7:30a C
>
> 3a-7:30a C
>
> 3a-11:30a CA
>
> 3a-3:30p CA
>
> 3a-7:30p CAB

"Otto Moehrbach" wrote:

> JC
>
> Excel is a beast of logic so I'm trying to put what you say into a logic
> that Excel can understand. I assume that 7a-3:30p is the first or day
> shift, 3p-11:30p is the evening or second shift, and 11:30p-7:30a is the
> third or night shift. Is that right? Assuming it is, I've made the
> following table where "A" is the first shift, "B" is the second shift, and
> "C" is the third shift. A combination of 2 or 3 of those 3 letters means
> that someone working that shift should be counted in each of those letter
> shifts. Does that make sense? If it does, are the combinations I show
> correct? Otto
>
> 7a-11:30a A
>
> 7a-3:30p A
>
> 7a-7:30p AB
>
> 7a-11:30p ABC
>
> 11a-3:30p A
>
> 11a-7:30p AB
>
> 11a-11:30p AB
>
> 11a-3:30a A
>
> 3p-7:30p B
>
> 3p-11:30p B
>
> 3p-3:30a ABC
>
> 3p-7:30a BC
>
> 7p-11:30p B
>
> 7p-3:30a BC
>
> 7p-7:30a BC
>
> 11p-3:30a C
>
> 11p-7:30a C
>
> 3a-7:30a C
>
> 3a-11:30a CA
>
> 3a-3:30p CA
>
> 3a-7:30p CAB
>
> "JC" <(E-Mail Removed)> wrote in message
> news:344DBD9C-D2A3-4844-8A36-(E-Mail Removed)...
> > Otto,
> > Hope this is clear. Some time frames need to be counted as coverage for
> > another shift.
> >
> > For example, if I am counting the number working 7a to 3p and also 3p to
> > 11p
> > and I have someone working 7a to 11p, that person would need to be counted
> > for both the day shift and also evening shift.
> >
> > Hope that is clear
> >
> > "Otto Moehrbach" wrote:
> >
> >> JC
> >> I don't know what you were getting at in your most recent post. Did
> >> you
> >> mean to say that you need to break up a shift so that part of it is
> >> counted
> >> in another shift? Post back and clarify what you mean. Otto
> >> "JC" <(E-Mail Removed)> wrote in message
> >> news:C2EF0008-C010-49DC-AD2F-(E-Mail Removed)...
> >> > Thank you for the quick reply.
> >> >
> >> > Sorry for the confusion. Let me see if I can explain this a little
> >> > better.
> >> > But I believe what you replied will work, if you can just let mek now
> >> > if
> >> > what
> >> > you explained is what I have described below: "Day Shift" is
> >> > considered
> >> > anything between 7a-3:30p. While we have some people that work the
> >> > entire
> >> > day
> >> > shift, their shift is 7a-3:30p, we have some people that only work half
> >> > of
> >> > the day shift and stay into second shift. Their shift might be 11a-11p
> >> > (meaning they are working 4 hours on days and 8 hours on evenings).
> >> >
> >> > As you were saying for say today, Tuesday November 25: The entire
> >> > schedule
> >> > for today would be listed from C5:C50, tomorrow would be listed from
> >> > D5:50,
> >> > etc.
> >> >
> >> > My dropdown list choices are made up of all the possible shifts, which
> >> > I
> >> > listed below:
> >> > 7a-11:30a
> >> > 7a-3:30p
> >> > 7a-7:30p
> >> > 7a-11:30p
> >> > 11a-3:30p
> >> > 11a-7:30p
> >> > 11a-11:30p
> >> > 11a-3:30a
> >> > 3p-7:30p
> >> > 3p-11:30p
> >> > 3p-3:30a
> >> > 3p-7:30a
> >> > 7p-11:30p
> >> > 7p-3:30a
> >> > 7p-7:30a
> >> > 11p-3:30a
> >> > 11p-7:30a
> >> > 3a-7:30a
> >> > 3a-11:30a
> >> > 3a-3:30p
> >> > 3a-7:30p
> >> >
> >> > Thanks again for all your help.
> >> >
> >> > "Otto Moehrbach" wrote:
> >> >
> >> >> JC
> >> >> If I understand you correctly, you have one column with the
> >> >> drop-downs.
> >> >> Say this column is Column C and the shifts start with C2 and go to
> >> >> C50.
> >> >> So
> >> >> in this column you will have a number of this shift, a number of that
> >> >> shift
> >> >> and a number of the other shift. You want to know how many of each
> >> >> shift
> >> >> you have. If this is correct, you need to use the COUNTIF function.
> >> >> Say
> >> >> that , in the drop-down list, the first shift is written as
> >> >> "7a-3:30p".
> >> >> Pick a cell in which you want the number of the first shift. In that
> >> >> cell
> >> >> type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English, look
> >> >> in
> >> >> the range C2 to C50 and count the number of cells that have 7a-3:30p
> >> >> in
> >> >> them. Do the same in 2 other cells for the other 2 shifts. Is this
> >> >> what
> >> >> you want? Your numbers in your example are somewhat scrambled, so
> >> >> maybe
> >> >> you
> >> >> want to add 2 shifts together. If so, write the formula like:
> >> >> =COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH Otto
> >> >> "JC" <(E-Mail Removed)> wrote in message
> >> >> news:584D0BF5-B398-4CBA-984C-(E-Mail Removed)...
> >> >> > Hi Everyone,
> >> >> > I need a little help with a project I am working on.
> >> >> >
> >> >> > I am in the process of making a schedule for work through Excel for
> >> >> > all
> >> >> > our
> >> >> > employees. I work at a hospital and we have all different shifts. We
> >> >> > do
> >> >> > the
> >> >> > schedule for 2 weeks at a time. What I did was make all the shifts
> >> >> > people
> >> >> > work into a dropdown selectable list. Now the part I need help with
> >> >> > is,
> >> >> > I'd
> >> >> > like the spreadsheet to be able to count the number of personnel
> >> >> > assigned
> >> >> > for
> >> >> > each shift.
> >> >> >
> >> >> > For example is I have 3 people selected as working 7a-3:30p, 2
> >> >> > people
> >> >> > 7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the
> >> >> > bottom
> >> >> > of
> >> >> > the
> >> >> > spreadsheet, to automatically say that there are 5 people on day
> >> >> > shift
> >> >> > (7a-3:30p) and then 7 people covering evening shift (3p-11:30). If
> >> >> > someone
> >> >> > can tell me how I have to go about this or is able to help it me
> >> >> > out,
> >> >> > it
> >> >> > would be greatly apprecaited. If needed, I can send you my
> >> >> > spreadsheet.
> >> >> >
> >> >> > Thanks for all your help!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

JC

You said that each day's schedule is in rows 5:50 in sequential
columns. When you run this macro (below) Excel will have to find the column
with which you want Excel to work. There are several ways for Excel to find
this column. I chose to have Excel find the last occupied cell in row 5.
That will then be the column. If this doesn't suit you for any reason, like
maybe you have entries in row 5 to the right of the pertinent column, let me
know and I'll change how it finds the pertinent column. Note that with this
method of finding that column, the wrong column will be selected if the cell
in row 5 of that column is empty. Another way to find that column would be
for you to select a cell, any cell, in that column BEFORE you run the macro,
but, for now, the first method is used.

I assumed that the list of employees (names) are in Column A
starting with A5 and down to the last entry in Column A. This way, the
number of employees is not limited. The sum of each shift is placed
immediately below the last entry in the column that has the shifts.

Look at the macro. Find the line that says "Select Case." and
the line that says "End Select". All the shifts you provided are listed
between these 2 lines. It is very important that the text of the shifts
that you have in your drop-down cells match EXACTLY the text shown in the
macro. Any extra spaces here and there will cause a mismatch and,
subsequently, an error in the results.

If you wish, send me an email and I'll send you the small file I
used to develop this macro. That file has this macro properly placed and a
button on the sheet to execute this macro. My email address is
(E-Mail Removed). Remove the "extra" from this address. HTH
Otto

Sub SumShifts()
Dim TheCol As Long, rColA As Range, Dest As Range
Dim TheShift As String, ShiftCode As String
Dim i As Range, CountA As Long, CountB As Long
Dim CountC As Long
TheCol = Cells(5, Columns.Count).End(xlToLeft).Column
Set rColA = Range("A5", Range("A" & Rows.Count).End(xlUp))
Set Dest = rColA(rColA.Count).Offset(1, TheCol - 1)
CountA = 0
CountB = 0
CountC = 0
For Each i In rColA
If IsEmpty(i.Offset(, TheCol - 1)) Then _
GoTo NextEmployee
TheShift = i.Offset(, TheCol - 1).Value
Select Case TheShift
Case "7a-11:30a", "7a-3:30p", "11a-3:30p": ShiftCode = "A"
Case "7a-11:30p", "11a-7:30p", "11a-11:30p": ShiftCode = "AB"
Case "11a-3:30a": ShiftCode = "ABC"
Case "3p-7:30p", "3p-11:30p", "7p-11:30p": ShiftCode = "B"
Case "3p-3:30a", "3p-7:30a", "7p-3:30a", "7p-7:30a": ShiftCode =
"BC"
Case "11p-3:30a", "11p-7:30a", "3a-7:30a": ShiftCode = "C"
Case "3a-11:30a", "3a-3:30p": ShiftCode = "CA"
Case "3a-7:30p": ShiftCode = "CAB"
End Select
If InStr(ShiftCode, "A") > 0 Then CountA = CountA + 1
If InStr(ShiftCode, "B") > 0 Then CountB = CountB + 1
If InStr(ShiftCode, "C") > 0 Then CountC = CountC + 1
NextEmployee:
Next i
Dest.Value = "Shift A= " & CountA
Dest.Offset(1).Value = "Shift B= " & CountB
Dest.Offset(2).Value = "Shift C= " & CountC
End Sub

"JC" <(E-Mail Removed)> wrote in message
news:F04304CA-C8B3-4BA1-BD54-(E-Mail Removed)...
> Yes, I've corrected below. Basically you were correct, I just made a few
> changes.
>
> If you have an better idea on how I can go about listing these, I am
> welcome
> to suggestions also.
>
>> 7a-11:30a A
>>
>> 7a-3:30p A
>>
>> 7a-11:30p AB
>>
>> 11a-3:30p A
>>
>> 11a-7:30p AB
>>
>> 11a-11:30p AB
>>
>> 11a-3:30a ABC
>>
>> 3p-7:30p B
>>
>> 3p-11:30p B
>>
>> 3p-3:30a BC
>>
>> 3p-7:30a BC
>>
>> 7p-11:30p B
>>
>> 7p-3:30a BC
>>
>> 7p-7:30a BC
>>
>> 11p-3:30a C
>>
>> 11p-7:30a C
>>
>> 3a-7:30a C
>>
>> 3a-11:30a CA
>>
>> 3a-3:30p CA
>>
>> 3a-7:30p CAB
>
> "Otto Moehrbach" wrote:
>
>> JC
>>
>> Excel is a beast of logic so I'm trying to put what you say into a
>> logic
>> that Excel can understand. I assume that 7a-3:30p is the first or day
>> shift, 3p-11:30p is the evening or second shift, and 11:30p-7:30a is the
>> third or night shift. Is that right? Assuming it is, I've made the
>> following table where "A" is the first shift, "B" is the second shift,
>> and
>> "C" is the third shift. A combination of 2 or 3 of those 3 letters means
>> that someone working that shift should be counted in each of those letter
>> shifts. Does that make sense? If it does, are the combinations I show
>> correct? Otto
>>
>> 7a-11:30a A
>>
>> 7a-3:30p A
>>
>> 7a-7:30p AB
>>
>> 7a-11:30p ABC
>>
>> 11a-3:30p A
>>
>> 11a-7:30p AB
>>
>> 11a-11:30p AB
>>
>> 11a-3:30a A
>>
>> 3p-7:30p B
>>
>> 3p-11:30p B
>>
>> 3p-3:30a ABC
>>
>> 3p-7:30a BC
>>
>> 7p-11:30p B
>>
>> 7p-3:30a BC
>>
>> 7p-7:30a BC
>>
>> 11p-3:30a C
>>
>> 11p-7:30a C
>>
>> 3a-7:30a C
>>
>> 3a-11:30a CA
>>
>> 3a-3:30p CA
>>
>> 3a-7:30p CAB
>>
>> "JC" <(E-Mail Removed)> wrote in message
>> news:344DBD9C-D2A3-4844-8A36-(E-Mail Removed)...
>> > Otto,
>> > Hope this is clear. Some time frames need to be counted as coverage for
>> > another shift.
>> >
>> > For example, if I am counting the number working 7a to 3p and also 3p
>> > to
>> > 11p
>> > and I have someone working 7a to 11p, that person would need to be
>> > counted
>> > for both the day shift and also evening shift.
>> >
>> > Hope that is clear
>> >
>> > "Otto Moehrbach" wrote:
>> >
>> >> JC
>> >> I don't know what you were getting at in your most recent post.
>> >> Did
>> >> you
>> >> mean to say that you need to break up a shift so that part of it is
>> >> counted
>> >> in another shift? Post back and clarify what you mean. Otto
>> >> "JC" <(E-Mail Removed)> wrote in message
>> >> news:C2EF0008-C010-49DC-AD2F-(E-Mail Removed)...
>> >> > Thank you for the quick reply.
>> >> >
>> >> > Sorry for the confusion. Let me see if I can explain this a little
>> >> > better.
>> >> > But I believe what you replied will work, if you can just let mek
>> >> > now
>> >> > if
>> >> > what
>> >> > you explained is what I have described below: "Day Shift" is
>> >> > considered
>> >> > anything between 7a-3:30p. While we have some people that work the
>> >> > entire
>> >> > day
>> >> > shift, their shift is 7a-3:30p, we have some people that only work
>> >> > half
>> >> > of
>> >> > the day shift and stay into second shift. Their shift might be
>> >> > 11a-11p
>> >> > (meaning they are working 4 hours on days and 8 hours on evenings).
>> >> >
>> >> > As you were saying for say today, Tuesday November 25: The entire
>> >> > schedule
>> >> > for today would be listed from C5:C50, tomorrow would be listed from
>> >> > D5:50,
>> >> > etc.
>> >> >
>> >> > My dropdown list choices are made up of all the possible shifts,
>> >> > which
>> >> > I
>> >> > listed below:
>> >> > 7a-11:30a
>> >> > 7a-3:30p
>> >> > 7a-7:30p
>> >> > 7a-11:30p
>> >> > 11a-3:30p
>> >> > 11a-7:30p
>> >> > 11a-11:30p
>> >> > 11a-3:30a
>> >> > 3p-7:30p
>> >> > 3p-11:30p
>> >> > 3p-3:30a
>> >> > 3p-7:30a
>> >> > 7p-11:30p
>> >> > 7p-3:30a
>> >> > 7p-7:30a
>> >> > 11p-3:30a
>> >> > 11p-7:30a
>> >> > 3a-7:30a
>> >> > 3a-11:30a
>> >> > 3a-3:30p
>> >> > 3a-7:30p
>> >> >
>> >> > Thanks again for all your help.
>> >> >
>> >> > "Otto Moehrbach" wrote:
>> >> >
>> >> >> JC
>> >> >> If I understand you correctly, you have one column with the
>> >> >> drop-downs.
>> >> >> Say this column is Column C and the shifts start with C2 and go to
>> >> >> C50.
>> >> >> So
>> >> >> in this column you will have a number of this shift, a number of
>> >> >> that
>> >> >> shift
>> >> >> and a number of the other shift. You want to know how many of each
>> >> >> shift
>> >> >> you have. If this is correct, you need to use the COUNTIF
>> >> >> function.
>> >> >> Say
>> >> >> that , in the drop-down list, the first shift is written as
>> >> >> "7a-3:30p".
>> >> >> Pick a cell in which you want the number of the first shift. In
>> >> >> that
>> >> >> cell
>> >> >> type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English,
>> >> >> look
>> >> >> in
>> >> >> the range C2 to C50 and count the number of cells that have
>> >> >> 7a-3:30p
>> >> >> in
>> >> >> them. Do the same in 2 other cells for the other 2 shifts. Is
>> >> >> this
>> >> >> what
>> >> >> you want? Your numbers in your example are somewhat scrambled, so
>> >> >> maybe
>> >> >> you
>> >> >> want to add 2 shifts together. If so, write the formula like:
>> >> >> =COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH
>> >> >> Otto
>> >> >> "JC" <(E-Mail Removed)> wrote in message
>> >> >> news:584D0BF5-B398-4CBA-984C-(E-Mail Removed)...
>> >> >> > Hi Everyone,
>> >> >> > I need a little help with a project I am working on.
>> >> >> >
>> >> >> > I am in the process of making a schedule for work through Excel
>> >> >> > for
>> >> >> > all
>> >> >> > our
>> >> >> > employees. I work at a hospital and we have all different shifts.
>> >> >> > We
>> >> >> > do
>> >> >> > the
>> >> >> > schedule for 2 weeks at a time. What I did was make all the
>> >> >> > shifts
>> >> >> > people
>> >> >> > work into a dropdown selectable list. Now the part I need help
>> >> >> > with
>> >> >> > is,
>> >> >> > I'd
>> >> >> > like the spreadsheet to be able to count the number of personnel
>> >> >> > assigned
>> >> >> > for
>> >> >> > each shift.
>> >> >> >
>> >> >> > For example is I have 3 people selected as working 7a-3:30p, 2
>> >> >> > people
>> >> >> > 7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the
>> >> >> > bottom
>> >> >> > of
>> >> >> > the
>> >> >> > spreadsheet, to automatically say that there are 5 people on day
>> >> >> > shift
>> >> >> > (7a-3:30p) and then 7 people covering evening shift (3p-11:30).
>> >> >> > If
>> >> >> > someone
>> >> >> > can tell me how I have to go about this or is able to help it me
>> >> >> > out,
>> >> >> > it
>> >> >> > would be greatly apprecaited. If needed, I can send you my
>> >> >> > spreadsheet.
>> >> >> >
>> >> >> > Thanks for all your help!
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>