Count times that are more than 5 minutes in a range from a newworksheet
 

Hi all,

I was wondering you anyone could help me out with my issue...

I have a column in one worksheet that contains either blank fields, or
time in [h]:mm format. I am trying to count in a new worksheet the
number of times time exceeds 5 minutes:

If I write the following formula within same worksheet, it works:

=COUNTIF((OFFSET(H2,1,0,250,1)),"<0:06")

However, from a new worksheet, I can do:
=COUNT('C:\Documents and Settings\elenak\Desktop\CORe\[HD-Alert
Processor - 05.11 - 05.17.xls]HD-Alerts'!$H$3:$H$250)
or on Mac:
=COUNT('Macintosh HD:Users:elenak:Documents:[HD-Alert Processor -
05.11 - 05.17.xls]HD-Alerts'!$H$3:$H$250)

and it will give me the correct count of cells that contain times

but none of these work:

=COUNTIF(OFFSET('C:\Documents and Settings\elenak\Desktop\CORe\[HD-
Alert
Processor - 05.11 - 05.17.xls]HD-Alerts'!H2,1,0,250,1), "<0:06")

=COUNTIF('C:\Documents and Settings\elenak\Desktop\CORe\[HD-Alert
Processor - 05.11 - 05.17.xls]HD-Alerts'!$H$3:$H$250,"<0:06")

=SUM(IF('Macintosh HD:Users:elenak:Documents:[HD-Alert Processor -
05.11 - 05.17.xls]HD-Alerts'!($H$3:$H$250-INT($H$3:$H
$250))*24<0.12,1,0))


(sorry, I am going back and forth between Mac and PC)

Thanks a ton in advance,
Lenchik

Count times that are more than 5 minutes in a range from a new worksheet
 

I am trying to count...the number of times time exceeds 5 minutes

Neither COUNTIF nor OFFSET will work on a *closed* file. Use SUMPRODUCT.

=SUMPRODUCT(--(your_path!H3:H250TIME(0,5,0)))

--
Biff
Microsoft Excel MVP


"Lenchik" wrote in message
...
Hi all,

I was wondering you anyone could help me out with my issue...

I have a column in one worksheet that contains either blank fields, or
time in [h]:mm format. I am trying to count in a new worksheet the
number of times time exceeds 5 minutes:

If I write the following formula within same worksheet, it works:

=COUNTIF((OFFSET(H2,1,0,250,1)),"<0:06")

However, from a new worksheet, I can do:
=COUNT('C:\Documents and Settings\elenak\Desktop\CORe\[HD-Alert
Processor - 05.11 - 05.17.xls]HD-Alerts'!$H$3:$H$250)
or on Mac:
=COUNT('Macintosh HD:Users:elenak:Documents:[HD-Alert Processor -
05.11 - 05.17.xls]HD-Alerts'!$H$3:$H$250)

and it will give me the correct count of cells that contain times

but none of these work:

=COUNTIF(OFFSET('C:\Documents and Settings\elenak\Desktop\CORe\[HD-
Alert
Processor - 05.11 - 05.17.xls]HD-Alerts'!H2,1,0,250,1), "<0:06")

=COUNTIF('C:\Documents and Settings\elenak\Desktop\CORe\[HD-Alert
Processor - 05.11 - 05.17.xls]HD-Alerts'!$H$3:$H$250,"<0:06")

=SUM(IF('Macintosh HD:Users:elenak:Documents:[HD-Alert Processor -
05.11 - 05.17.xls]HD-Alerts'!($H$3:$H$250-INT($H$3:$H
$250))*24<0.12,1,0))


(sorry, I am going back and forth between Mac and PC)

Thanks a ton in advance,
Lenchik