How to Use Excel COUNTIF Between Time Range (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Use COUNTIF Between a Time Range in Excel: 2 Examples

In this section, we will show the counting between time range in Excel with the COUNTIF function 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

The COUNTIF function counts the number of cells within a range that meet the given condition.

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)

COUNTIF Function to Count within a Time in Excel

  • Press the Enter button to get the result.

We can check the given result with the given data.

Read More: COUNTIF Function to Count Cells That Are Not Equal to Zero


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)

Two of COUNTIF Functions to Count within a Time Range

We performed a subtraction operation here.

Read More: How to Use COUNTIF Function in Excel Greater Than Percentage


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

The COUNTIFS function counts the number of cells specified by a given set of conditions or criteria.

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) 

COUNTIFS Function to Count within Specific TimeR ange

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 Between Two Cell Values in Excel 


2. Excel SUMPRDUCT Function to Count Between Time Range

The SUMPRODUCT function returns the sum of products of corresponding ranges or arrays.

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))

SUMPRDUCT Function to Count between Time Range

We get the no. of shows in the range of 17:30 to 18:30.

Read More: Excel COUNTIF to Count Cells Greater Than 1


3. Excel SUMIFS Function to Sum Values Between Time Range

The SUMIFS function adds the cells specified by a given set of conditions or criteria.

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) 

SUMIFS Function to Sum Values Between Time Range

Two movie shows start within this time range and we get no. total sold tickets for those shows.

Read More: How to Use COUNTIF for Non Contiguous Range in Excel


4. Use of Excel IF Function Between Multiple Time Ranges

The SUM function adds all the numbers in a range of cells.

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,""))) 

Combination of IF & SUM functions Between Multiple Time Ranges

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 COUNTIF Function to Calculate Percentage 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: How to Compare Two Columns Using COUNTIF Function


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.


Related Articles


<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo