Excel Count Times in a Range? (Step-by-Step)

To count how many times a specific time appears within a range of cells in Excel, use the COUNTIF function with the time as the criteria. For example, =COUNTIF(A2:A10, "9:00 AM") returns the number of cells in that range that contain exactly 9:00 AM. This article explains the formulas, common pitfalls, and how to handle date-time values so you get accurate counts every time.

Formula

The core formula is COUNTIF(range, criteria), where range is the group of cells you are searching and criteria is the time you want to count. In Excel, times are stored as decimal fractions of a day (0.0 = midnight, 0.5 = noon), so the criteria can be a time literal like "9:00 AM", a cell reference containing a time, or the result of the TIME function. The formula counts only cells whose value exactly equals the criteria, not cells that fall within a time period.

For example, to count all cells in range A1:A100 that show 9:00 AM, use:

• =COUNTIF(A1:A100, "9:00 AM") – literal text, but beware of regional date formats.
• =COUNTIF(A1:A100, TIME(9,0,0)) – more robust because it uses Excel’s internal time value.
• =COUNTIF(A1:A100, B1) – where B1 contains a time value.

Worked Example

Suppose you have a list of appointment times in cells A2:A11 (10 rows) and you want to count how many are at 9:00 AM. The default values in the calculator above use 10 rows and a target of 9:00 AM. If we assume that all 10 cells contain 9:00 AM, the formula =COUNTIF(A2:A11, TIME(9,0,0)) returns 10. If only 3 of them are 9:00 AM, it returns 3. The calculator simply lets you set the range size and shows the count based on that size, but in a real spreadsheet you would replace the range with your actual cell references.

How to Choose the Right Function

Excel offers several counting functions, and picking the wrong one is a common mistake. Here is a quick guide:

• COUNTIF – counts cells that exactly match a single time. Use when you need an exact match.
• COUNTIFS – counts cells that meet multiple criteria, such as times between 9:00 AM and 10:00 AM. Use for ranges of times.
• SUMPRODUCT – a more flexible alternative for complex conditions, e.g., counting times that fall within a specific hour.
• COUNT – counts numeric cells, but since times are numbers, it counts all times regardless of value. Not useful for a specific time.

For counting times that fall within a range (e.g., between 9:00 and 10:00), use COUNTIFS:

=COUNTIFS(A1:A100, ">="&TIME(9,0,0), A1:A100, "<"&TIME(10,0,0))

Common Problems and How to Avoid Them

One of the most frequent issues is that Excel stores dates and times together. If a cell contains a date with a time (e.g., 1/1/2025 9:00 AM), the time alone is not the entire value. The value is a number like 45658.375, and COUNTIF with just the time will not match it. To handle this, you can use the MOD function to extract the time: =SUMPRODUCT(--(MOD(A1:A100,1)=TIME(9,0,0))).

Another common problem is regional date and time formats. When you type "9:00 AM" in a formula, Excel interprets it according to your system's locale. Using TIME(9,0,0) avoids this issue entirely. Also, be careful with rounding: times that look identical may differ by a fraction of a second due to floating-point arithmetic. For example, a time calculated as 9:00:00.0001 will not equal 9:00:00 exactly. In such cases, round the times in a helper column or use a tolerance in the criteria.

Comparing COUNTIF and Pivot Tables

For a quick one-off count, COUNTIF is simple and fast. However, if you need to count times across many categories or want a visual breakdown, a pivot table is often more efficient. To count times in a pivot table, drag the time field into the Rows area and another field (like a count of any column) into the Values area. The pivot table will group times automatically, but you can also group by hour by right-clicking the time field and selecting Group. This is especially useful when you want to count occurrences per hour, not just one specific time.

COUNTIF is better when you need a single number in a formula, while pivot tables are better for exploratory analysis and reporting.

Alternatives for Complex Scenarios

If your data contains times that are stored as text (e.g., “9:00 AMâ€� as a string), COUNTIF will not work because the cell contains text, not a time. You can convert text to time using TIMEVALUE in a helper column, or use SUMPRODUCT with TIMEVALUE directly: =SUMPRODUCT(--(TIMEVALUE(A1:A100)=TIME(9,0,0))). This works only if the text is in a recognizable time format.

For counting times that fall within a dynamic range (e.g., based on a start and end time in cells), you can use COUNTIFS with cell references: =COUNTIFS(A1:A100, ">="&B1, A1:A100, "<="&C1). This is more flexible than hardcoding times.

Frequently Asked Questions

How do I count times between two specific times in Excel?

Use COUNTIFS with two criteria: one for the lower bound (e.g., ">="&TIME(9,0,0)) and one for the upper bound (e.g., "<"&TIME(17,0,0)). For example, =COUNTIFS(A1:A100, ">="&TIME(9,0,0), A1:A100, "<"&TIME(17,0,0)) counts all times from 9:00 AM to before 5:00 PM.

Why does COUNTIF return 0 when I can see the time in the cell?

This usually happens because the cell contains a date-time value, not just a time. The time is stored as a fraction of a day, but the date part makes the whole number larger than 1. Use MOD to extract the time part, or change the cell format to show only the time, but the underlying value still contains the date. The SUMPRODUCT formula with MOD is the reliable fix.

Can I count times that are in a specific hour, like 9 AM to 10 AM?

Yes, use COUNTIFS with two conditions: ">="&TIME(9,0,0) and "<"&TIME(10,0,0). This counts all times from 9:00:00 up to but not including 10:00:00. If you want to include 10:00:00, change the second condition to "<="&TIME(10,0,0).

What if my times are in text format?

Convert them to real times using the TIMEVALUE function in a helper column, or use SUMPRODUCT with TIMEVALUE in the formula. For example, =SUMPRODUCT(--(TIMEVALUE(A1:A100)=TIME(9,0,0))) works if the text is like “9:00 AMâ€�. If the text is not a recognized time format, you may need to parse it manually with functions like LEFT, MID, and RIGHT.

How do I count times that are after a certain time, like after 5 PM?

Use COUNTIF with a greater-than criterion: =COUNTIF(A1:A100, ">"&TIME(17,0,0)). This counts all times after 5:00 PM. Remember to use the TIME function to avoid regional format issues.

Does COUNTIF count times that are equal to a time with seconds?

Yes, COUNTIF matches the exact value. If the cell contains 9:00:30 and you search for 9:00:00, it will not count it. You need to either round the times or use a range criterion, such as ">="&TIME(9,0,0) and "<"&TIME(9,1,0) to capture all times within that minute.

Related

• Arcade Codes: What They Are & How to Build One
• Observability vs Monitoring: What Alerts Miss
• NASA Mars Moon Missions: Origins Investigation

in t | Time to count | h:mm | 9:00 AM | 0
in r | Range size (rows) | - | 10 | 1
out count | Count of matching times | - | r | 0