0

Looking to get the count of rows that fall within a certain hour of the day.

I can perform IF statements against the times, but not COUNTIF(S)...

So far I have tried:

=countifs(timevalue(A2:A),">=b2",timevalue(A2:A),"<=c2")

And even tried hard coding the times in, e.g.:

=countifs(timevalue(A2:A),">=05:00:00",timevalue(A2:A),"<=05:59:59")

Also not really sure if/why TIMEVALUE is needed, since it seems I am working with actual datetime cells... however it seemed to help on the IF statements.

Here is my sample spreadsheet showing data: https://docs.google.com/spreadsheets/d/1gEmuc00W1sY_TClx1uR9lqYRRtsHzzWyQvO8o3G8epE/edit#gid=255648219

1
1

You want to count of the rows where the time value in Column A falls within the "START" and "END" values in Column B and C

Try =query(A2:C22,"select COUNT(A) where timeofday '"&TEXT(A2,"HH:mm:ss")&"'>= B and C>= timeofday '"&TEXT(A2,"HH:mm:ss")&"'")

  • timeofday is a data type supported by query.
  • Columns B and C are already formatted for time.
  • Column A has a datetime value and needs to be recast as time. TEXT(A2,"HH:mm:ss") converts the value in Column A number into text according to the specified format.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.