Time Range Count

[nrichel]

Hi - I'm hoping I could get some help with a count I need to do.

In A I have a range of times that appear like this:
0:03:00
0:10:00
1:20:00
0:01:00

I would like to be able to count how many times in my 9,000 records does less than 3 mins appear
3 - 5 mins
5-7 mins
7-10 mins
10-20 and so on

Please help! I've been trying the COUNTIF and SUM functions, but no luck! Thanks!!

 

[Jonmo1]

If those are actual real times, then you can use sumproduct.
clarification though...

Should 5 minutes exactly be counted as 3-5 or 5-7?

 

[VoG]

Try like this

Excel Workbook
	A	B
1	00:03:00	3
2	00:10:00
3	01:20:00
4	00:01:00
Sheet4

 

[shg]

One way:

       ---A--- B ---C--- D ----------------------------E----------------------------
   1   0:07:23   0:03:00 4 D1: =COUNTIF($A$1:$A$20, "<" & C1)                       
   2   0:05:23   0:05:00 3 D2 and down: =COUNTIF($A$1:$A$20, "<" & C2) - SUM(D$1:D1)
   3   0:01:29   0:07:00 2                                                          
   4   0:06:18   0:10:00 2                                                          
   5   0:13:46   0:20:00 9                                                          
   6   0:04:58                                                                      
   7   0:18:16                                                                      
   8   0:14:54                                                                      
   9   0:01:02                                                                      
  10   0:10:53                                                                      
  11   0:16:00                                                                      
  12   0:03:01                                                                      
  13   0:02:38                                                                      
  14   0:08:58                                                                      
  15   0:12:59                                                                      
  16   0:10:04                                                                      
  17   0:14:07                                                                      
  18   0:00:07                                                                      
  19   0:04:58                                                                      
  20   0:10:10

EDIT: A little late to the party, I see ...

 

[nrichel]

Count it as 3-5, please and thanks

 

[shg]

3 to (less than) 5 appears in D2 in my example.

 

[Jonmo1]

Try

=SUMPRODUCT(--(MOD(A1:A10,1)<="0:03:00"+0),--(MOD(A1:A10,1)>"0:00:00"+0))
=SUMPRODUCT(--(MOD(A1:A10,1)<="0:05:00"+0),--(MOD(A1:A10,1)>"0:03:00"+0))
=SUMPRODUCT(--(MOD(A1:A10,1)<="0:07:00"+0),--(MOD(A1:A10,1)>"0:05:00"+0))
etc...

 

[nrichel]

That works! You are a rockstar!

 

[Jonmo1]

Glad to help, thanks for the feedback..


just FYI
The MOD in there may be a bit overkill.
It's there in case your times are actually DATE and Time..

But if your times are actually just time only, then you can remove the mod part

=SUMPRODUCT(--(A1:A10<="0:03:00"+0),--(A1:A10>"0:00:00"+0))

 

[nrichel]

What if I wanted to show a count of 30+ mins to catch anything else above 30 mins?