using countif or countifs to count specific times in a column

rmof

New Member
Joined
Oct 24, 2011
Messages
4
<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>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

rmof

New Member
Joined
Oct 24, 2011
Messages
4
Hello,
many thanks for this, but unfortunately this still returns 0 records :(, what am I doing wrong?
 

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
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

New Member
Joined
Oct 24, 2011
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Oct 24, 2011
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,111
Messages
5,640,156
Members
417,127
Latest member
shakilk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top