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!
Prashanth,
I have a tricky one for you. I have data that is constantly auto-populating. The data is; package number, Status (Enroute, Arrived, Pending, Accepted), and date with time. I want to use a countif formula to know how many different statuses I have but only count the most up-to-date information.
Example:
I might have 10 entries for package Number 1. They are all time-stamped with varying statuses, but I only want to count package number 1 status once and based on its latest time stamp. I then want to add that single count for that package number with all the other most up-to-date package number counts.
Basically, I want to know how many Enroute statues I have but only based on the most recent submissions. Can google sheets do this? Is there a formula for this?
As always thank you so much for your help and all that you do. I very much appreciate it.
Hi, Monk,
This formula might help you to find the most recent “Enroute” statuses.
=sortn(sort(filter(indirect("A2:C"),indirect("B2:B")="Enroute"),3,0,1,1),9^9,2,1,1)
It would return results in two columns and multiple rows. So make two columns empty and insert the formula in the first cell of the first column.
To get the count, you can use this formula.
=counta(index(sortn(sort(filter(indirect("A2:C"),indirect("B2:B")="Enroute"),3,0,1,1),9^9,2,1,1),0,1))
If that doesn’t help, please prepare and share an example Sheet (URL) via the comment below.
Hi, can someone please help me to correct my function?
Count the number of cells with Date “Today” and Time “between 06:00 and 14:30″
=countifs('A:A">=Today() 06:00:00", 'A:A"<=Today() 14:30:00")Thanks
Hi, Alaa,
Here is the correct way to add time to today’s date.
=today()+TIME(hour, minute, second)So the formula will be;
=countifs(A:A,">="&Today()+time(6,0,0), A:A,"<="&Today()+time(14,30,0))Hi,
Thank you for this post. It has been the only formula that would work for what I wanted to do.
I do seem to have a problem with Formula 4 picking up midnight in the time range…”00:00″.
My formula is below:
=ArrayFormula(COUNTIFS(value(replace(AM3:AM50,1,11,)),”>=23:01″,VALUE(REPLACE(AM3:AM50,1,11,)),”<=00:00"))
AM3:AM50 contains this in one of its cells: 23 Jan 2020 00:00
However, this cell is not being included in the count.
Thank you so much.
Hi, Angie,
I guess the first DateTime is 23/01/2020 23:01 and the second DateTime is 23/01/2020 00:00. That means the second date is less than the first date as the time 00:00 means 12:00 am. So the formula won’t include the same in the count.
For including midnight time falls on the same day, try the second criterion as 23:59 instead of 00:00.
Best,