Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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

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.


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.


Similar Readings


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 vs COUNTIFS in Excel (4 Examples)


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.


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 the Combination of COUNTIF and SUMIF 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 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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo