I have several tabs for different dates with data in it. Colomn G
contains the time in format 13:30:30.

For my project, I require to know the number of data points in various
time series, pls see below.

7:30:00 8:30:00
8:30:00 9:30:00
9:30:00 10:30:00
10:30:00 11:30:00
11:30:00 12:30:00
12:30:00 13:30:00
13:30:00 14:30:00
14:30:00 15:30:00
15:30:00 16:00:00

I tried countif and if function but could not come up with the results,
PLS HELP. If I have to do it manually then I am dead......


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Do you mean something along the lines of

=SUMPRODUCT(--(G$2:$G$1000=TIME(7,30,0)),--(G$2:$G$1000<TIME(8,30,0)))

etc.

Best to put the comparison times in cells and use say

=SUMPRODUCT(--($G$2:$G$1000=A1),--($G$2:$G$1000<B1))

which can then be copied easily

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mohitmahajan"
wrote in message
...

I have several tabs for different dates with data in it. Colomn G
contains the time in format 13:30:30.

For my project, I require to know the number of data points in various
time series, pls see below.

7:30:00 8:30:00
8:30:00 9:30:00
9:30:00 10:30:00
10:30:00 11:30:00
11:30:00 12:30:00
12:30:00 13:30:00
13:30:00 14:30:00
14:30:00 15:30:00
15:30:00 16:00:00

I tried countif and if function but could not come up with the results,
PLS HELP. If I have to do it manually then I am dead......


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile:
http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

No...what I am trying to do is find the number of times the data lies
between these two parameters. For example, if the data in G colomn is
like below:

Time
7:32:57
7:48:26
7:55:05
7:57:37
8:02:17
8:08:36
8:14:05
8:19:27
8:21:10
8:25:56
9:08:11
9:09:48
9:19:56
9:29:06
9:32:05
9:47:04
9:53:58
9:55:51
10:01:41
10:06:08
10:10:05
10:23:34
10:29:22
10:41:46
11:00:09
11:09:48
11:16:05
11:20:23
11:25:37
11:28:45
11:34:51
11:41:27
11:47:47
11:52:14

Now from this data I want to know how many lie in the series (as I am
calling it) or in the time groups specified.
7:30:00 to 8:30:00 - In this all data points greater than or equal to
7:30:00 and less than 8:30:00 are to be taken into count. Likewise for
rest of the series....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

That was what Bob's formula did, did you even try it? It returns 10 using
you sample, if not show the result you expect

--
Regards,

Peo Sjoblom

(No private emails please)


"mohitmahajan"
wrote in message
news:mohitmahajan.1zyl7n_1134444301.4349@excelforu m-nospam.com...

No...what I am trying to do is find the number of times the data lies
between these two parameters. For example, if the data in G colomn is
like below:

Time
7:32:57
7:48:26
7:55:05
7:57:37
8:02:17
8:08:36
8:14:05
8:19:27
8:21:10
8:25:56
9:08:11
9:09:48
9:19:56
9:29:06
9:32:05
9:47:04
9:53:58
9:55:51
10:01:41
10:06:08
10:10:05
10:23:34
10:29:22
10:41:46
11:00:09
11:09:48
11:16:05
11:20:23
11:25:37
11:28:45
11:34:51
11:41:27
11:47:47
11:52:14

Now from this data I want to know how many lie in the series (as I am
calling it) or in the time groups specified.
7:30:00 to 8:30:00 - In this all data points greater than or equal to
7:30:00 and less than 8:30:00 are to be taken into count. Likewise for
rest of the series....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile:
http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Silly me....

All I had to do after putting in the formula by Bob was to change the
reference A1 and B1 to the ones I wanted and also change the
format.....

Well, its my fault....

Thanks both of you, you have been a great help....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Ok, here is the 2nd part of the problem.....

Now I have been asked to take out data per associate hour wise.....
I have attached the sheet with the table also in which info is
required....

Pls help


+-------------------------------------------------------------------+
|Filename: Book2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4117 |
+-------------------------------------------------------------------+

--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678