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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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!