COUNTIF or ?

[RJW]

I am not sure what to use here. what I am trying to do is sort by Day and hour and name. The minutes arent needed, and what is hanging me up. The number of rows in the column can vary.as can the names like Frank and Bill. For instance, I would like to see how many Frank did in the hour of 14:00 (2pm-2:59) and do that for every hour of the day and get a total for that name, for every hour of each date. I hope I am making sense, and any help would really be appreciated!


Frank 12/30/2004 2:58
Frank 1/1/2005 22:52 
Frank 1/2/2005 11:33 
Bill 1/2/2005 22:46 
Frank 1/3/2005 13:30 
Frank 1/3/2005 14:21 
Frank 1/3/2005 14:59 
Bill 1/3/2005 16:40 
Frank 1/3/2005 22:02 
Frank 1/4/2005 11:48 
Frank 1/5/2005 10:37 
Bill 1/5/2005 13:58 

 

[just_jon]

Book2
	A	B	C	D	E	F
1	Frank	12/30/2004	2:58	Frank	0.583333	0.625
2	Frank	1/1/2005	22:52	2
3	Frank	1/2/2005	11:33
4	Bill	1/2/2005	22:46
5	Frank	1/3/2005	13:30
6	Frank	1/3/2005	14:21
7	Frank	1/3/2005	14:59
8	Bill	1/3/2005	16:40
9	Frank	1/3/2005	22:02
10	Frank	1/4/2005	11:48
11	Frank	1/5/2005	10:37
12	Bill	1/5/2005	13:58
13
14
15
Sheet1

Time is stored as a decimal [ 24 hrs = 1.0 ], so your start/stop criteria in F1/E1 are expressed in terms of start hours/24 and stop time/24.

Welcome to MfExcel.

in D1: =SUMPRODUCT(--(A1:A15=D1),--(C1:C15>=E1),--(C1:C15<F1))

 

[RJW]

Wow, that will work!! In another example I have, (wish these tables were all built the same) They have the date and time in one box, instead of two like the first. its like this: Column A has the name Frank, and column B has the date and time in the same box like this: 1/7/2005 8:54. so this other looks like this:
column A
Frank
Frank  
Bill
Frank

column b
1/1/2005 22:52
1/3/2005 13:30
1/3/2005 14:21
1/6/2005 18:44 
(Time and Date in same box)

Thank you! I would have never thought of this.

 

[tactps]

In E1, type the time and date that you are looking for, then change the formula to:

=SUMPRODUCT(--(A1:A15=D1),--(b1:b15>=E1))

 

[RJW]

Looks like I did something wrong, This would problably be easier if I could cut off the minutes on the time here, I wish on this table that they would have put the time in a different column. I tried =sumproduct with just these 2 columns, but nogo. Is it possible to get separate counts for each name for each hour of the day, without specifying an hour or day? In this case, Frank would have 2 hits during the 2pm hour on 1/3/2005. Bill would have 2 hits during the 1pm hour of 1/5, Frank would have 1 during that same day. Basically, I am trying to get an hourly count of hits per day per person. I changed the data a bit to make it easier to look at/deal with. Thanks again for all of the help you have given me. (sorry, I couldnt get this to post right with columns A and B next to eachother)

A
Frank
Bill
Frank
Bill
Frank
Frank
Frank
Bill
Frank  
Frank  
Frank
Bill
Bill
Frank

B
12/30/04 2:58
1/1/2005 22:52
1/2/2005 11:33
1/2/2005 22:46
1/3/2005 13:30
1/3/2005 14:21
1/3/2005 14:59
1/3/2005 16:40
1/3/2005 22:02
1/4/2005 11:48
1/5/2005 10:37
1/5/2005 13:01
1/5/2005 13:15
1/5/2005 13:58

 

[tactps]

How about this:

Type "Frank in cell E2
Type the date including the hour in cell E3.

Use this formula:
=SUMPRODUCT(--($A$2:$A$15=E2),--($B$2:$B$15>E3),--($B$2:$B$15<E3+TIMEVALUE("1:00:00")))

 

[tactps]

Sorry that seems to have pasted in incorrectly:

=SUMPRODUCT(--($A$2:$A$15=E2),--($B$2:$B$15>E3),--($B$2:$B$15<E3+TIMEVALUE("1:00:00")))

 

[tactps]

Again, the end should read:
--($b$2:$B$15<+E3+timevalue("1:00:00")))

Sorry.

 

[RJW]

Now that works great!! Is there a way to not have to specify a name and a time? I really do appreciate your help.

 

[tactps]

You might want to look into creating a pivot table (Data Menu). Read the help files in Excel.

Note, you may want to split your time and date columns to make it easier to work with.

If you want all times to be shown as the beginning of the hour, try something like:
=time(B1,0,0) where B1 contains the time.