Microsoft Excel - COUNTIFS Calculation / Count By Hour 1

[Enkrypted]

I have a spreadsheet that I'm trying get a count by hour breakdown on. It contains a list of people and their shift times and days worked:

I'm trying to get it to only count those that are working on a particular day (such as Friday). The COUNTIFS code I have works and it calculates the count by hour out correctly (Code will be in cell P2), but it takes all people regardless if they work that day or not. The code I'm using is:

=COUNTIFS($D$2:$D$10,"<="&O2,$E$2:$E$10,">="&O2)

I want it to calculate out the count by hour for only those that have the letter F in Column G and not count in anyone that shows an equal (=). I appreciate any help with this. Let me know if any additional info is needed. Thanks

Enkrypted
A+

 

[SkipVought]

Hi,

If you want help, plz post data that can be copied/pasted into Excel. A picture looks nice, but is virtually worthless.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

... or attach your Excel file to your post.


---- Andy

There is a great need for a sarcasm font.

 

[Enkrypted]

I've attached the Excel file. Sorry about that

Enkrypted
A+

 

[SkipVought]

Here's what I did to your workbook:

1) Created Named Ranges based you your headings.
2) Created this formula
[tt]
P2: =SUMPRODUCT((Start_Time<=O2)*(Stop_Time>=O2)*(INDIRECT(P$1)<>"="))
[/tt]

BTW, I rarely if ever use COUNTIFS or SUMIFS in favor of SUMPRODUCT, which is much more intuitive than the former.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Enkrypted]

Thanks for this info Skip

I applied the name range as you mentioned above and put the above code in P2 as mentioned. When it applied that, it's erroring out with a #REF. I'm not sure if I'm missing something or not. I've attached the updated file if you or someone want to look at it. Thank you for taking the time to help me with this

Enkrypted
A+

 

[SkipVought]

You also need to have the FRI to THURS ranges named.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Enkrypted]

Thanks Skip! That worked. I do have one more question, but how would I factor this for someone that works overnight, such as the highlighted portion on the attached file. It should calculate the 10:00PM and 11:00PM for Friday and then the remainder (from 12:00AM to 7:00AM) on Saturday? That was one thing I just thought of. Thanks again for all your help!

Enkrypted
A+

 

[SkipVought]

Add 1 to the times for the next day in ALL three columns where time values are used.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Enkrypted]

I'm sorry, I guess I don't understand what you mean by adding 1 to the times. Can you elaborate on that a little?

Enkrypted
A+

 

[SkipVought]

For 12:45 AM for Saturday Stop Time, enter...
[tt]
1 00:45
[/tt]

For 12:45 PM for Saturday Stop Time, enter...
[tt]
1 12:45
[/tt]

And don't forget that the corresponding values must occur in column O.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Enkrypted]

That did the trick. Thanks again Skip!!

Enkrypted
A+