| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count how many times a certain value is in a certain range | Excel Discussion (Misc queries) | |||
| Count the number of times a cell value is within a specific range | Excel Worksheet Functions | |||
| How can I count the # of times the word "Yes" appears in a range | Excel Worksheet Functions | |||
| how do I calculate minutes when given two times? | Excel Worksheet Functions | |||
| how do I count the number of times a word is repeated in a range? | Excel Discussion (Misc queries) | |||
Linear Mode
