Counting with multiple criteria

[amcclintock]

I need help getting a formula to count how many instances happen between a certain time on a certain day.

For example in column A5:A15 I have the Date of the incident and in column B5:B15 I have the Time of the incident. I want to count how many incidents happened between a certain time on certain days. For example, how many incidents happened between 06:00 am and 12:00 pm on Monday. Then how many happened between 12:00pm and 18:00 on Monday.

Basically I have 6 hour windows and want to know how many incidents happen between each time frame on each specifc day. The closest I have gotten is I get it to count the number of times it happens on Monday and the number of times between the time frame, but then it multiplies the numbers.

Here is the formula I have:

=SUMPRODUCT(--(WEEKDAY(May!$A$5:$A$45)=4)*(COUNTIFS(May!$B$5:$B$45,">="&TIME(6,0,0))-COUNTIF(May!$B$5:$B$45,">"&TIME(12,0,0))))

I'm using the WEEKDAY function to count the days and a COUNTIF to count the time. "May!" refers to the worksheet I want to pull from.

HELP PLEASE!! I'm starting to go insane from trying to figure this out!

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

=SUMPRODUCT(--(WEEKDAY(May!$A$5:$A$45)=4),--(May!$B$5:$B$45>=TIME(6,0,0)),--(May!$B$5:$B$45<=TIME(12,0,0)))