COUNTIF or ?

RJW

New Member
Joined
Jan 18, 2005
Messages
5
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Jan 18, 2005
Messages
5
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
Joined
Jan 20, 2004
Messages
3,460
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
Joined
Jan 18, 2005
Messages
5

ADVERTISEMENT

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
Joined
Jan 20, 2004
Messages
3,460
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
Joined
Jan 20, 2004
Messages
3,460

ADVERTISEMENT

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
Joined
Jan 20, 2004
Messages
3,460
Again, the end should read:
--($b$2:$B$15<+E3+timevalue("1:00:00")))

Sorry.
 

RJW

New Member
Joined
Jan 18, 2005
Messages
5
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
Joined
Jan 20, 2004
Messages
3,460
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.
 

Forum statistics

Threads
1,147,848
Messages
5,743,525
Members
423,801
Latest member
paulj4177

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

Which adblocker are you using?

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
Top