Minutes - CountIf not working, why?

**virtualdark** · 09-07-2012, 03:47 AM

I have a column with time (hh:mm:ss), I cannot change that column format (it's in general)

I want to find out how many cells are bellow the 3 minutes
=COUNTIF(A:A;"<=00:03:00")
Hour.xlsx
It should work but it's not! I have tried several option with no luck.

Please Help!!

**Spencer101** · 09-07-2012, 03:57 AM

Try changing the formula in D3 to =COUNTIF(A:A,"<=time(0,3,0)")

**virtualdark** · 09-07-2012, 05:45 AM

THANK YOU
Geeesh, when I used that it gave me 0 as result because my separator it's ;
=COUNTIF(A:A;"<=time(0;3;0)")

To work I had to do it in a mix from mine to yours! =COUNTIF(A:A;"<=time(0,3,0)")
But now it works

Thank you

**Spencer101** · 09-07-2012, 05:58 AM

I use Excel 2010 also and the formula I gave above was just copied and pasted from Excel.
Perhaps regional settings for Portugal explain the difference in separators...

Glad it's sorted for you now

**daddylonglegs** · 09-07-2012, 06:43 AM

Originally Posted by Spencer101

Try changing the formula in D3 to =COUNTIF(A:A,"<=time(0,3,0)")

I don't think this works correctly, it doesn't evaluate the TIME function, it treats it as a string (so it counts letters below "t" in the alphabet but not after), it will count all text formatted time values, even those > 0:03:00.

In the data shown A1 is not counted, purely because it is numeric (try changing to 0:02:00, it still isn't counted)

To use TIME correctly that would need to be

=COUNTIF(A:A,"<="TIME(0,3,0))

[with commas changed to semi-colons in Portugal]

That should work OK on numeric data [as does the original =COUNTIF(A:A;"<=00:03:00")], so try converting to numeric by selecting column of times and using

Data > Text to Columns > OK

Alternatively this formula works on time or text values (or a mixture) and works unchanged in both Portugal and UK

=SUMPRODUCT((A1:A100+0<"0:03"+0)*(A1:A100<>""))