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

Example 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.

To get the number of shows within a schedule,

Steps:

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.
Insert the following formula on Cell F6.

=COUNTIF(C5:C9,">="&F4)

Press Enter to get the result.

Example 2 – Use of Multiple COUNTIF Functions to Count Within a Time Range

Steps:

Add new rows in the dataset for start and end times.
Enter the start and end times.

Enter the following formula based on the combination of two COUNTIF functions.

=COUNTIF(C5:C9,">"&F4)-COUNTIF(C5:C9,">"&F5)

We have performed a subtraction operation.

Several Alternatives to COUNTIF Function for Time Ranges in Excel

Method 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 the number shows within a time range.

We already input the start and end times of movie shows.

Steps:

Add the formula below in Cell F6.

=COUNTIFS(C5:C9,">"&F4,C5:C9,"<"&F5)

We get no. of shows successfully. In the case of the COUNTIF function, we needed two functions. Using a single COUNTIFS function, we get the same result.

We can also insert the following TIME function.

=COUNTIFS(C5:C9,">"&TIME(17,30,0),C5:C9,"<"&TIME(18,30,0))

Method 2 – Excel SUMPRDUCT Function to Count Between Time Range

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

Steps:

Enter the following formula based on the SUMPRODUCT function in Cell F6.

=SUMPRODUCT((C5:C9>=F4)*(C5:C9<=F5))

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

Method 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.

Steps:

We have modified the dataset by adding number of tickets sold. We want to count the number of tickets within a time range.

Go to Cell F6 and enter the following formula.

=SUMIFS(D5:D9, C5:C9, ">=" & G4, C5:C9, "<=" & G5)

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

Method 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

To find out the no. of shows that will start before 17:30 and after 18:30.

Steps:

Go to Cell F6 and enter 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.

How to Use COUNTIF Function in Excel for a Date Greater Than 30 Days

We have a dataset of the name of movies with their release dates. 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.