using countif or countifs to count specific times in a column

[rmof]

<P>Hello everyone,</P>
<P> </P>
<P>I am having difficulty in counting the number in a column. my column has (or will have by year end), approx 10,000 records. I want to count the number between 2 times. the command I am using is</P>
<P>=COUNTIFS(F1:F9546,">=00:00:00", F1:F9546,"<=00:00:30")</P>
<P>With the above command the return is 0 (zero) but in reality it is course much much more. In the column F all records range between 00:00:00 - 00:48:23. The statistic I want is the number of records above zero but below & including 30seconds.</P>
<P> </P>
<P>I use anoither command which works perfectly well.</P>
<P>=COUNTIFS(B107:B9530,310,G107:G9530,"Caller abort")</P>
<P> </P>
<P>If anyone can help I would be very grateful.</P>
<P> </P>
<P>Many thanks,</P>
<P>Richard.</P>

 

[Andrew Poulsom]

Welcome to MrExcel:

=COUNTIFS(F1:F9546,">="&"00:00:00"+0, F1:F9546,"<="&"00:00:30"+0)

 

[rmof]

Hello,
many thanks for this, but unfortunately this still returns 0 records , what am I doing wrong?

 

[Kaps_mr2]

the fact that :-

=COUNTIFS(B107:B9530,310,G107:G9530,"Caller abort") works, suggests that perhaps column F is currently formatted as text rather than date / time. Hence reformat your cells and then apply your formulae. thanks

Kaps

 

[rmof]

It seems as though it is indeed a formatting problem. However, I have tried changing the format to time 13:30:00 & hh:mm:ss plus custom all to no avail. I will try another export of the data & see if it works afterwards.
Thanks for all your help,
Richard.

 

[Andrew Poulsom]

I can't test COUNTIFS because I don't have Excel 2007 where I am at the moment. But this worked for me in Excel 2003:

=COUNTIF(F1:F9546,">="&"00:00:00"+0)-COUNTIF(F1:F9546,">"&"00:00:30"+0)

 

[rmof]

In fact I am wondering if the issue is with xl2003. The raw data is zipped xl2003 & the opened in xl2007, initially it is a read only file & once saved I then use the data.
I will try your formulas in xl2003 & see if they work.
Thanks for everyones help it is very much appreciated.
Richard.