You can easily learn the use of COUNTIFS in a time range in Google Sheets. It’s not rocket science. But things are not that much easy when you want to use COUNTIFS in a date and time column called timestamp aka DateTime column.

I am going to give you the necessary tips for using COUNTIFS in a time column as well as in a date and time (timestamp) column to count a certain time range.

In precise, the time range here means a certain 1 hour, 2 hours like time segments. For example, count all the activities or tickets that were recorded between the time 8:00:00 am to 10:00:00 am.

Before going to that see how to use the DateTime (Timestamp) criteria in Countifs.

The Countifs formula if A1:A contains DateTime:

=countifs(A1:A,">=01/07/2018 08:05:00", A1:A,"<=01/07/2018 16:05:00")

How to use the function COUNTIFS in a Time Range in Google Sheets

This tutorial is divided into two parts. In the first part, you can learn how to use COUNTIFS in a time column to count the time range. In the second part, the same thing I am explaining but with a timestamp or you can say DateTime column.

The Use of COUNTIFS in a Time Column

The below formula is an example of how to use COUNTIFS in a time range in Google Sheets.

The following formula counts the time range from 08:30:00 to 09:30:00 both inclusive. If any time in column A falls in this range, it got counted.

Formula 1: Direct Use of Time Criteria in the Countifs Formula

=countifs(A2:A8,">=08:30:00",A2:A8,"<=09:30:00")

Alternatively, you can key in the criteria in cell E2 (08:30:00) and F2 (09:30:00) and refer to that in the formula.

=countifs(A2:A8,">="&E2,A2:A8,"<="&F2)

Counting time by time duration is that much simple. But things are not that much smoother if your column A in the above example contains timestamps aka DateTime, not pure time.

The Use of COUNTIFS in a DateTime aka Timestamp Column

How to count the time range when the time is in date and time format or you can say timestamp format?

Here I can suggest you two-three different options. It’s all about how you extract the time from the date.

Here are the example formulas.

Formula 2: Using Query+Split to Split DateTime

As you can see, column A contains timestamps. So you can’t use the range A2: A as the criteria range in COUNTIFS this time.

To understand this, see my Formula 1 again.

=countifs(A2:A8,">=08:30:00",A2:A8,"<=09:30:00")

Now see Formula 2.

=ArrayFormula(COUNTIFS(query(split(A2:A," "),"Select Col2"),">=8:30",
 query(split(A2:A," "),"Select Col2"),"<9:30"))

Instead of the range A2:A8 in Formula 1, here in Formula 2, I have used a Query formula as below.

=ArrayFormula(query(split(A2:A," "),"Select Col2"))

Note: The array formula moved to the beginning in Formula 2.

What does it mean?

I have used the SPLIT function to split the date time in column A into date and time. It creates two columns – one with the date and another with the time. Then I used the Query to select column 2 which contains the time.

The same thing you can achieve in different ways. See that in the below formulas 3 and 4.

Formula 3: Using Time Functions to Extract Time from DateTime

Another formula example to COUNTIFS in a Time Range in Google Sheets.

You can use the above same Formula 1 by replacing the range A2:A with the below formula.

=ArrayFormula(TIME(hour(A2:A8),minute(A2:A8),second(A2:A8)))

This formula can also extract time from the timestamp and the final formula would be like;

Must Read: Learn Google Sheets Time Functions.

=ArrayFormula(COUNTIFS(TIME(hour(A2:A8),minute(A2:A8),second(A2:A8)),">=8:30",
 TIME(hour(A2:A8),minute(A2:A8),second(A2:A8)),"<9:30"))

Formula 4: Using Replace Formula to Replace The Date in DateTime and Extract Time

This is my final formula that tells you how to use COUNTIFS in a time range in a timestamp column.

I recommend this formula to use when you want to count time segments in the date and time column. For me, this Formula 4 seems better than Formula 2 and 3 above.

Here again, how you are going to extract time from the timestamp column is important. In the earlier two examples, I’ve used Query and Time functions. Here I am going to use the REPLACE function for this. This code is cleaner.

You can use A2:A8 with the below REPLACE formula.

=ArrayFormula(value(replace(A2:A8,1,11,)))

See the final formula and output.

=ArrayFormula(COUNTIFS(value(replace(A2:A8,1,11,)),">=8:30",
 value(replace(A2:A8,1,11,)),"<9:30"))

That’s all. Enjoy!