• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

countif the hours less than 24:00:00

Working fine for me with the following data in column A


24:00:00

19:00:00

10:00:00

36:00:00

47:15:00

18:20:00


Perhaps you can upload a sample?
 
Ramesh


or

=COUNTIF(A1:A99,"<1")


make sure your times don't include Dates as that will mess it up
 
Hi Dave,


Note able to find the upload option.

Try to Explain here only.

There are two columns M & P

so the formula should be used considered= countif M= less than 24 hours + P= less than 24 hours.

Means formula should count the values in the both columns and add it in a seperate cell.

Ex: M8 P8

24:00:00 23:00:00


So Result should be 2


Regards,

Ramesh
 
Hi rameshkumarsharma,


I appreciate you have closed the post, but did you get the result you wanted? Only the example you gave


'Ex: M8 P8

'24:00:00 23:00:00


'So Result should be 2


Means that 24:00:00 should be counted as well, so does this work for you?


=IF(M8<=1,1,0)+IF(N8<=1,1,0)
 
Hi


Thank you for checking.

i have used the other formula. I have used the below formula:-


=COUNTIF(M9:M219,"<"&0.999988)+COUNTIF(P9:p219,"<"&0.999988)


Regards,

Ramesh
 
Ramesh


The formula above

=COUNTIF(M9:M219,"<"&0.999988)+COUNTIF(P9:p219,"<"&0.999988)

Won't tell you if both Column M and P are true at the same time?

Which I though was your request?


If so try Countifs()

=COUNTIFS(M9:M219, "<1", P9:p219, "<1")
 
Hi Hui,


=COUNTIFS(M9:M219, "<1", P9:p219, "<1")


your formula counts only the P9:p219 reference, it's not counting the reference M9:M219.


=COUNTIF(M9:M219,"<"&0.999988)+COUNTIF(P9:p219,"<"&0.999988) - This fourmula is working fine and counts for both reference.


Regards,

Ramesh
 
You must log in or register to reply here.
Back
Top