# COUNTIF or ?

#### RJW

##### New Member
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

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### just_jon

##### Legend
Book2
ABCDEF
1Frank12/30/20042:58Frank0.5833330.625
2Frank1/1/200522:522
3Frank1/2/200511:33
4Bill1/2/200522:46
5Frank1/3/200513:30
6Frank1/3/200514:21
7Frank1/3/200514:59
8Bill1/3/200516:40
9Frank1/3/200522:02
10Frank1/4/200511:48
11Frank1/5/200510:37
12Bill1/5/200513: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.

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

#### RJW

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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

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

Sorry.

#### RJW

##### New Member
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

##### Well-known Member
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.

Replies
7
Views
405
Replies
4
Views
227
Replies
0
Views
416
Replies
0
Views
312
Replies
19
Views
2K

Threads
1,181,824
Messages
5,932,271
Members
436,830
Latest member
Sochen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back