COUNTIF using amount of time

[dman]

I have some data with various time intervals in mm:ss.ms and I want to
use the COUNTIF function to count how many of them fall between 1-2
minutes, 2-3 minutes, 3-4 minutes, etc., but I keep getting a count of
all the values. Am I comparing the time intervals wrong?

For example, the data (in the A column) is:
01:19.666
01:13.287
01:28.308
02:29.872
05:36.658
01:01.770

My formula for counting those between 1-2 minutes:
=COUNTIF(A:A,">=00:01.000&&<00:02.000")

The returned value is 6.

Any help would be appreciated,
Dennis

 

[Earl Kiosterud]

Dennis,

For 1 to 2 minutes, try
=SUMPRODUCT((A2:A7>=TIMEVALUE("01:00.0"))*(A2:A7<TIMEVALUE("02:00.0")))

You've put a boolean (TRUE/FALSE) in the COUNTIF, but it wants actual
comparison values. We sit outside the Excel building and bawl for SUMIF and
COUNTIF functions into which we can put booleans, but are told we won't get
them until the year 2057. So we have to write these geeky SUMPRODUCT
functions.

 

[George Nicholson]

=COUNTIF(A1:A6,">=01:00.00")-COUNTIF(A1:A6,">02:00.00")

(# of entries >= 1 minute) - (# of entries over 2 minutes)
= # of entries between 1 & 2 minutes.

HTH

 

[dman]

Thanks for everyone's help. I tried all the solutions and these two
worked.
(Oh and yeah, all the times I'm counting are under an hour).

=SUMPRODUCT(--(MINUTE(A1:A10)=1))
=SUMPRODUCT((MOD(A1:A10,1)>=1/(24*60))*(MOD(A1:A10,1)<2/(24*60)))

Dennis