Countif of time intervals with multiple criteria

[emisa042185]

Hi there!

Hope some could help me. I have time stamps for several student in a class who will go for break lunch and coaching. I want to know what is the exact formula to use to count the number of occurance of either lunch break or coaching in an hourly interval basing from the timestamp.

i used countifs with predefined name for logged time, subject and actvity

formula used: =countifs(logged_time,">="&a1,logged_time,"<"&a2,Subject,"="&$A$5",Activity,"="&$A$6))

where: logged_time, Subject, Activity are pre-defined names which have the same range size
a1 is where the start time is (eg 7:00 AM)
a2 is where the end time is (eg 8:00 AM)
so basically i am looking for a formula to count the number of either break, lunch or coaching, in an hourly interval per subject.
a5, and a6 is where the reference of the subject (eg,, math or science)

many thanks

Ed

 

[sijpie]

Pretty much the formula that you had:

Excel 2010
	A	B	C	D	E	F	G
1	07:00		2
2	17:00
3			Logged_Time		Subject		Activity
4			02:00		English		Lunch
5	French		03:00		French		Lunch
6	coaching		04:00		German		Lunch
7			05:00		IT		coaching
8			06:00		IT		coaching
9			07:00		German		Lunch
10			08:00		French		coaching
11			09:00		English		coaching
12			10:00		English		Lunch
13			11:00		Physics		Lunch
14			12:00		Physics		Lunch
15			13:00		English		Lunch
16			14:00		IT		coaching
17			15:00		French		coaching
18			16:00		English		Lunch
19			17:00		French		Lunch
Sheet4

Cell Formulas
Range	Formula
C1	=COUNTIFS(logged_time,">="&A1,logged_time,"<"&A2,Subject,"="&$A$5,Activity,"="&$A$6)

Named Ranges
Name	Refers To	Cells
Activity	=Sheet4!$G$4:$G$19
logged_time	=Sheet4!$C$4:$C$19
Subject	=Sheet4!$E$4:$E$19

 

[emisa042185]

Hi Sijpie,

thanks for your response. This is the formula i am using right now, and it did working fine but some of the time from the time column were not being read. I can upload the file that Im having confusion if you want to, so you may be able to find out as well what the problem I am saying.

Appreciate your help. Thanks

 

[emisa042185]

Hi Sijpie,

this is the table im working into.

[TABLE="class: grid, width: 426"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Time Stamp
[/TD]
[TD]Subject[/TD]
[TD]Activity[/TD]
[TD]Break[/TD]
[TD]Lunch[/TD]
[TD]Coaching[/TD]
[/TR]
[TR]
[TD="align: right"]7:00 PM[/TD]
[TD]Math[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7:01 PM[/TD]
[TD]Science[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7:59 PM[/TD]
[TD]English[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8:00 PM[/TD]
[TD]Math[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8:01 PM[/TD]
[TD]Math[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8:30 PM[/TD]
[TD]Math[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9:00 PM[/TD]
[TD]Math[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9:01 PM[/TD]
[TD]Math[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10:01 PM[/TD]
[TD]Science[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11:00 PM[/TD]
[TD]Science[/TD]
[TD]Coaching[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11:59 PM[/TD]
[TD]Science[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12:58 AM[/TD]
[TD]Science[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1:57 AM[/TD]
[TD]Science[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2:56 AM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3:55 AM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4:54 AM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5:53 AM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6:52 AM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7:51 AM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8:50 AM[/TD]
[TD]English[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9:49 AM
[/TD]
[TD]Science[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10:48 AM[/TD]
[TD]Science[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11:47 AM[/TD]
[TD]Science[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12:46 PM[/TD]
[TD]English[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1:45 PM[/TD]
[TD]English[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2:44 PM[/TD]
[TD]English[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3:43 PM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4:42 PM[/TD]
[TD]English[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5:41 PM[/TD]
[TD]Math[/TD]
[TD]Lunch[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6:40 PM[/TD]
[TD]Math[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7:39 PM[/TD]
[TD]Math[/TD]
[TD]Beak[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[sijpie]

For which times does it not work? Just some quick tests with me seem to be OK

 

[emisa042185]

everything seems to be fine now, it was just a some formatting on the time stamp though. thanks for your help sijpie.