Explanation
The COUNTIFS function takes one or more criteria, entered as range/criteria pairs. In this example, the first range/criteria pair is:
B5:B11,">="&E5
Matching any time greater than or equal to the time E5 (5:00). The second range/criteria pair is:
B5:B11,"<"&E6
Matching any time less than the time in E6 (6:30).
With hard-coded values
The formula in E7 could be written with hard-coded time values as follows:
=COUNTIFS(B5:B11,">=5:00",B5:B11,"<6:30")
Excel translates a string like "5:00" into the correct numeric time value.
With the TIME function
The formula in E7 could be written with the TIME function like this:
=COUNTIFS(B5:B11,">="&TIME(5,0,0),B5:B11,"<"&TIME(6,30,0))
The TIME function provides a simple way to assemble a valid time using discreet hour, minute, and second values.
Related formulas
Round time to nearest 15 minutes
Calculate hours between two times
Extract time from a date and time
Group times into 3 hour buckets
Related functions
COUNTIFS Function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more conditions. Each condition is provided with a separate range and criteria, and all conditions must be TRUE for a cell to be included in the count. COUNTIF can be used to count cells...
TIME Function
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.
