Have you ever wondered how to count events in specific timeslots in Google Sheets? Well, worry no more! I’ve got not one, but two amazing solutions for you.
Count Events Using Formulas
Let’s start with the first method, using formulas. Imagine you have a column in Google Sheets that contains timestamps of calls received from customers. Now, you want to count the number of calls received within a particular timeslot, let’s say between 10:00:00 and 12:00:00.
To achieve this, you can use the COUNTIFS formula or a combination of COUNT and FILTER. Even if you’re familiar with COUNTIFS, things might get a little tricky if you have a date-time (timestamp) column instead of a time column.
But don’t worry, I’ve got your back! You can also rely on a pivot table if you want to count events in hourly timeslots, such as the number of events at 10 AM, 11 AM, and 12 PM.
Count Events Using Pivot Tables
In our example, let’s assume the timestamps are recorded in column A2:A. Now, to count the events (calls received) in the timeslot from 10:00:00 to 12:00:00, you can use the following COUNTIFS formula:
=ArrayFormula(countifs(mod(A2:A,1),">="&time(10,0,0),mod(A2:A,1),"<"&time(12,0,0)))
In the formula above, we hardcode the criteria into the formula using ">="&time(10,0,0) and "<"&time(12,0,0).
If you prefer to refer to the criteria given in two cells, you can enter the time 10:00:00 in cell C1 and 12:00:00 in cell D1. Then, use the below COUNTIFS formula:
=ArrayFormula(countifs(mod(A2:A,1),">="&C1,mod(A2:A,1),"<"&D1))
Now, let me explain the formula. The core part of the COUNTIFS formula is the MOD function. It converts the timestamp to time within the formula, extracting the time from the timestamp. MOD is not the only option for this purpose, but it’s definitely a useful one.
But wait, there’s more! I have an alternative solution to the count events in a particular timeslot problem. Check it out below.
Count Events Using FILTER and COUNT
With this method, we will filter the timestamps that fall within the given timeslot and count them, all in one formula. Here’s the magic formula:
=count(filter(A2:A,mod(A2:A,1)>=time(10,0,0),mod(A2:A,1)<time(12,0,0)))
The FILTER function plays a significant role here, filtering the range A2:A (the timestamp column) that matches the timeslot criteria. Again, the MOD function is crucial in this process.
To count events in different hourly timeslots in Google Sheets, we can rely on the power of Pivot Tables. Let me show you how.
Counting Events Using Pivot Tables
For this method, we’ll continue using the sample data in column A2:A12. Follow these steps to count events in timeslots using Pivot Tables in Google Sheets:
- Select the range A1:A12.
- Click on DATA and select PIVOT TABLE (you’ll find all the data-related commands under the DATA menu).
- Choose “Existing sheet,” then select cell D1, and click “Create.”
- On the Pivot Table editor panel, click the “Add” button next to “Rows” and select “Time.”
- Click the “Add” button next to “Values” and select “Time.” Then, under “Summarize by,” choose “Count.”
To better understand steps 4 and 5, I’ve included an image below.
- Right-click on any cell in the pivot table range (for example, cell D2).
- Click on “Create pivot date group” and select “Hour.”
Voila! You now have an hour-wise summary of timestamps.
And there you have it! You’ve learned how to count events in particular timeslots in Google Sheets using formulas and Pivot Tables. Now go ahead and put your newfound knowledge to good use.
If you want to explore more exciting tips and tricks for Google Sheets, head over to Crawlan.com.
Thanks for joining me on this journey. Enjoy your newfound spreadsheet superpowers!
