COUNTIF using amount of time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dman - 23 Jan 2007 20:08 GMT

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

Reply to this Message

Earl Kiosterud - 23 Jan 2007 20:47 GMT

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.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

>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
[quoted text clipped - 16 lines]
> Any help would be appreciated,
> Dennis

Reply to this Message

George Nicholson - 23 Jan 2007 21:12 GMT

=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

Reply to this Message

Rate this thread: