< Go Back

Count times in a specific range

★ 25 minute read

If you have Excel data of events that happened within a certain period, how would you count times in a specific range? COUNTIFS function is what you need to provide the solution to such problems.

Count times in a specific range

Formula

This is how the formula looks like:

=COUNTIFS(rng,">="&start,rng,"<"&end)

Assume that you have some events that require tracking down by time. If you want to count times in a specific range or count events that took place with a specific time range (6:00 and 8:20), this is how COUNTIFS function is applied. The check out this example

Example 1

 

 

Figure 1. Count times in a specific range

 

From the screenshot, the formula highlighted for cell (F9) is:

=COUNTIFS(B6:B13,">="&F7,B6:B13,"<"&F8)

As seen from this example, the function takes two ranges or criteria pairs. Working from the inside, the 1st criteria pair is:

B6:B13,">="&F7

The range matches any time that is greater than or equal to the value in cell F7, i.e., 6:00.

The 2nd criteria pair is:

B6:B13,"<"&F8

which matches any value (time) that’s less than the value in the cell F8 (8:20).

That is how you count times in a specific range i.e., between 6:00 to 8:30  in this example.

You can as well write the formula in hard-coded format, and it would look like this:

=COUNTIFS(B6:B13,">=6:00"&F7,B6:B13,"<8:20"&F8)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar