Excel 2007 : How do I find the number of times a time appears in a report ?

**stonefacedolly** · 02-26-2010, 05:29 PM

I have been trying to create a formula that would let me calculate the number of time a particular time shows up in a report. Let’s say I wanted to count how many times entries for 23:00:00 hours apeairs in the below example on Mondays. I can count 8 but is there a way to make excel count it. I have been told it is impossible and just don’t believe it.

Date 4-Jan 5-Jan 6-Jan 7-Jan 11-Jan
Day Mon Tue Wed Thu Mon
23:21:34 23:20:07 23:54:01 23:31:44 23:58:17
22:54:20 23:16:25 23:43:36 23:21:08 23:47:16
22:20:52 22:50:11 23:17:26 23:18:40 23:45:58
21:39:22 22:47:56 22:45:00 23:16:24 23:36:41
21:37:15 22:30:45 22:43:04 23:10:05 23:24:03
21:36:18 22:25:20 22:13:33 22:49:48 23:17:03
21:32:54 21:49:24 22:04:11 22:39:40 23:10:41
20:59:06 21:42:46 21:47:20 22:31:16 22:46:39
20:36:56 21:18:07 21:45:40 22:23:41 22:15:19
20:29:59 20:07:09 21:29:35 22:22:14 22:03:01

**zbor** · 02-26-2010, 05:45 PM

Here you can try this:

=SUMPRODUCT(--($B$3:$F$3&HOUR($B$4:$F$13)="Mon"&23))

(of course, you can make references instead of "Mon"&23 and put for example drop down menu in G1&H1 with Mon, tue, Wed.... and 1,2,3,4...

**oldchippy** · 02-26-2010, 05:45 PM

Hi,

If you had Mon,Tue,Wed,Thur,Mon in H2:L2, then this in H3

=COUNTIF(A$3:A$12,">=23:00:00") drag over to L3 this will give the answer for each day

**zbor** · 02-26-2010, 05:53 PM

That won't work if he for example wants for 21:00 where it will count all up to 24:00

**oldchippy** · 02-26-2010, 05:55 PM

Yeah missed read that one, thought he was looking for anything over 23:00:00

**stonefacedolly** · 03-02-2010, 04:15 PM

=COUNTIF(A$3:A$12,">=23:00:00") seems to total all the calls before 23:00 I think i needs to add a < some where ?

**stonefacedolly** · 03-02-2010, 04:15 PM

An thank you every one for your help!!!!

Excel 2007 : How do I find the number of times a time appears in a report ?

LinkBack

Thread Tools

Rate This Thread

Display

How do I find the number of times a time appears in a report ?

Re: How do I find the number of times a time appears in a report ?

Re: How do I find the number of times a time appears in a report ?

Re: How do I find the number of times a time appears in a report ?

Re: How do I find the number of times a time appears in a report ?

Re: How do I find the number of times a time appears in a report ?

Re: How do I find the number of times a time appears in a report ?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions