Alok Paul
Excel COUNTIF function is used to count based on criteria. We can set criteria according to our requirements. Time range is one of the criteria. In this article, we will show the use of the COUNTIF function between a time range in Excel with proper illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Examples of Using COUNTIF Between a Time Range in Excel
In this section, we will show the counting between time range in Excel with the COUNTIF and other functions. We have a dataset of the time schedule of a cinema hall.
We will show the counting based on the time in Excel.
1. COUNTIF Function to Count Within a Time in Excel
Here, we will use the COUNTIF function to get the number of shows within a schedule.
📌 Steps:
- First, add two new rows in the dataset to input the time and count the number of shows within that time.
We want to get the number of shows that will start within 18:00.
- Put 18:00 on Cell F5.
- Then, insert the following formula on Cell F6.
=COUNTIF(C5:C9,">="&F4)
- Press the Enter button to get the result.
We can check the given result with the given data.
2. Use of Multiple COUNTIF Functions to Count Within a Time Range
In this section, we will use the combination of two COUNTIF functions. This will count the number of shows that start in the time range in Excel.
📌 Steps:
- We add new rows in the dataset for start and end times.
- We input the start and end times.
- After that, put the below formula based on the combination of two COUNTIF functions.
=COUNTIF(C5:C9,">"&F4)-COUNTIF(C5:C9,">"&F5)
We performed a subtraction operation here.
Similar Readings
- How to Use COUNTIF for Non Contiguous Range in Excel
- Excel COUNTIF Function with Conditional Formatting (7 Examples)
- How to Use COUNTIF to Count Date Less Than Today in Excel
- Difference Between SUMIF and COUNTIF Functions in Excel
- How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
Several Alternatives to COUNTIF Function for Time Ranges in Excel
In this section, we will see several alternatives to the COUNTIF function in Excel to perform various tasks between time ranges.
1. COUNTIFS Function to Count Within Specific Time Range
In this section, we will use the COUNTIFS function to count no. shows within a time range.
We already input the start and end times of movie shows.
📌 Steps:
- Move to Cell F6 and input the below formula.
=COUNTIFS(C5:C9,">"&F4,C5:C9,"<"&F5)Â
We get no. of shows successfully. In the case of the COUNTIF function, we needed two functions. But the same result we get using a single COUNTIFS function.
We can also insert the TIME function with this formula. The formula will look like the following one.
=COUNTIFS(C5:C9,">"&TIME(17,30,0),C5:C9,"<"&TIME(18,30,0))
Read More: COUNTIF vs COUNTIFS in Excel (4 Examples)
2. Excel SUMPRDUCT Function to Count Between Time Range
This SUMPRODUCT function will perform the same task that is already shown in the previous 2 methods.
📌 Steps:
- Put the following formula based on the SUMPRODUCT function in Cell F6.
=SUMPRODUCT((C5:C9>=F4)*(C5:C9<=F5))
We get the no. of shows in the range of 17:30 to 18:30.
3. Excel SUMIFS Function to Sum Values Between Time Range
In this section, we will sum values between a time range in Excel using the SUMIFS function.
📌 Steps:
- We modified the dataset. The number of sold tickets is added to the dataset. We want to count the number of tickets within a time range.
- Now, go to Cell F6 and put in the following formula.
=SUMIFS(D5:D9, C5:C9, ">=" & G4, C5:C9, "<=" & G5)Â
Two movie shows start within this time range and we get no. total sold tickets for those shows.
Read More: How to Use the Combination of COUNTIF and SUMIF in Excel
4. Use of Excel IF Function Between Multiple Time Ranges
The IF function checks whether a condition is met, and returns one value condition is TRUE and another value if FALSE
In this section, we will use the combination of SUM and IF functions to count between multiple time ranges. We want to know the no. of shows those will start before 17:30 and after 18:30.
📌 Steps:
- Go to Cell F6 and put in the following formula.
=SUM(IF(C5:C9<F4,1,""),(IF(C5:C9>F5,1,"")))Â
We get 3 in return. It means a total of 3 shows will start in the given two-time ranges.
Read More: How to Use IF and COUNTIF Functions Together in Excel
How to Use COUNTIF Function in Excel for a Date Greater Than 30 Days
In this part, we will use the Excel COUNTIF function to calculate the number of movies released in the last 30 days. We will use the TODAY function to get the present day.
We have a dataset of the name of movies with their release dates.
📌 Steps:
- Put the following formula on Cell C11.
=COUNTIF(C5:C9,">"&TODAY()-30)Â
We get the number of movies released in the last 30 days. We applied the COUNTIF function with multiple criteria with date ranges.
Read More: COUNTIF Date Is within 7 Days
Conclusion
In this article, we described 6 methods to count between time ranges in Excel with the COUNTIF function. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.
Related Articles
- How to Use Nested IF and COUNTIF Formula in Excel
- COUNTIF Excel Example (22 Examples)
- How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)
- Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)
- How to Use COUNTIF Function in Excel Greater Than Percentage
- COUNTIF Between Two Dates in Excel (4 Suitable Examples)
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
