sumif formula
I have a a schedule I would like to count how many people are scheduled during a certain time frame.
2:45 AM - 11:30 AM
5:45 AM - 2:30 PM
8:45 AM - 5:30 PM
8:45 AM - 2:30 PM
8:45 AM - 5:30 PM
8:45 AM - 7:30 PM
6:45 PM - 3:00 AM
Column A is the "in" time and Column C is the "out" time. (Column B is the "-")
I would like to count how many people are scheduled at 3pm (answer is 3)
I'm thinking I will need to use a sumproduct and an OR statement for this.
Any help would be appreciated!! Thank you!
Hi Jwildcat,
See if
=COUNTIFS(A1:A7,"<"&E1,C1:C7,">"&E1)
doesn't do what you are asking for.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Looks like that works great!
Is there anyway to account people who work through midnight?
example:
6:45 PM - 3:00 AM
6:45 PM - 3:00 AM
4:45 PM - 3:00 AM
9:45 PM - 6:00 AM
4:45 PM - 10:00 PM
People that are schedule at 1am?
I think you'd need to put a Date and Time in all you cells to deal with the "through midnight" question.
The formula should work if you added dates and times to all cells.
Hmm...it doesn't seem to work. Any ideas?
You need to put the date in the question cell also. See this attached.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks