How can I make it so Excel checks a column to see if the are an exact set of
entries in a column, in no particular order, and with empty cells. I'm doing
a schedule, and none of the templates I've found work for me. Each column
represents either a day or night shift, and I need to be sure I've scheduled
enough people. I have to enter a variety of jobs and starting times with no
set ending times. I need a formula that will let me know if I've got all the
staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
"count" and "countif", but I can's quite figure it out.

I think you need to be more specific in what you're after, but I have a
suggestion.

Instead of having one daytime shifts column and one nightime shifts column
and trying to figure out if you have enough staff for each hour of the day,
you might separate this instead into start time and end time.

Then you could have a summary at the bottom of the day that has each hour
(7:00 AM in cell A50, 8:00 AM in cell A51, etc.) and use the following in B50
(I'll assume start time is in column B and end time in C):
=sumproduct(($B$2:$B$49<=$A50)*($C$2:$C$49$A50))

Hope that helps.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Frador" wrote:

How can I make it so Excel checks a column to see if the are an exact set of
entries in a column, in no particular order, and with empty cells. I'm doing
a schedule, and none of the templates I've found work for me. Each column
represents either a day or night shift, and I need to be sure I've scheduled
enough people. I have to enter a variety of jobs and starting times with no
set ending times. I need a formula that will let me know if I've got all the
staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
"count" and "countif", but I can's quite figure it out.

There are precise start times, but no precise end times. It's a restaurant,
so I have to schedule servers, bartenders, etc, and many of them start at
different times. There's no set end of shift time. It's based on how busy the
restaurant is. I just have to be sure that I have covered all the shifts. On
the weekends, it's a lot of people, and I often leave some out.

"KC Rippstein" wrote:

I think you need to be more specific in what you're after, but I have a
suggestion.

Instead of having one daytime shifts column and one nightime shifts column
and trying to figure out if you have enough staff for each hour of the day,
you might separate this instead into start time and end time.

Then you could have a summary at the bottom of the day that has each hour
(7:00 AM in cell A50, 8:00 AM in cell A51, etc.) and use the following in B50
(I'll assume start time is in column B and end time in C):
=sumproduct(($B$2:$B$49<=$A50)*($C$2:$C$49$A50))

Hope that helps.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Frador" wrote:

How can I make it so Excel checks a column to see if the are an exact set of
entries in a column, in no particular order, and with empty cells. I'm doing
a schedule, and none of the templates I've found work for me. Each column
represents either a day or night shift, and I need to be sure I've scheduled
enough people. I have to enter a variety of jobs and starting times with no
set ending times. I need a formula that will let me know if I've got all the
staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
"count" and "countif", but I can's quite figure it out.

Sorry it took so long to get back to you again. My PC at work died on me.

I'm still not sure I get what you're after, so if you'd like me to offer a
little more help, feel free to email your file to me at
kcrippstein<athotmail<dotcom. I'd be happy to help.

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Frador" wrote:

There are precise start times, but no precise end times. It's a restaurant,
so I have to schedule servers, bartenders, etc, and many of them start at
different times. There's no set end of shift time. It's based on how busy the
restaurant is. I just have to be sure that I have covered all the shifts. On
the weekends, it's a lot of people, and I often leave some out.

"KC Rippstein" wrote:

I think you need to be more specific in what you're after, but I have a
suggestion.

Instead of having one daytime shifts column and one nightime shifts column
and trying to figure out if you have enough staff for each hour of the day,
you might separate this instead into start time and end time.

Then you could have a summary at the bottom of the day that has each hour
(7:00 AM in cell A50, 8:00 AM in cell A51, etc.) and use the following in B50
(I'll assume start time is in column B and end time in C):
=sumproduct(($B$2:$B$49<=$A50)*($C$2:$C$49$A50))

Hope that helps.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Frador" wrote:

How can I make it so Excel checks a column to see if the are an exact set of
entries in a column, in no particular order, and with empty cells. I'm doing
a schedule, and none of the templates I've found work for me. Each column
represents either a day or night shift, and I need to be sure I've scheduled
enough people. I have to enter a variety of jobs and starting times with no
set ending times. I need a formula that will let me know if I've got all the
staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
"count" and "countif", but I can's quite figure it out.