Count Records in a Time Range with Excel COUNTIFS
Question details
Count records with times between 21:00 and 22:00 using an Excel formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to count cars recorded during a specified one-hour time range.
- Observed behavior
- The formula should count only records whose time values fall within the chosen boundaries.
Identify the cells containing the recorded times and check that they are real Excel time values, not text. The formulas below assume the cells contain times without dates.
Use COUNTIFS to count times between 21:00 and 22:00
Apply two criteria to the same time range and choose whether to include exactly 22:00.
In Excel, click an empty cell where you want the count to appear.
Enter =COUNTIFS(A2:A10,">="&TIME(21,0,0),A2:A10,"<="&TIME(22,0,0)) and press Enter. Replace both instances of A2:A10 with your time range.
To exclude exactly 22:00, change the second criterion from "<="&TIME(22,0,0) to "<"&TIME(22,0,0).
If the count is incorrect, select a time cell and check whether it is stored as text or includes a date. For a text time in A2, enter =TIMEVALUE(A2) in a helper column, fill it down, and use the converted range in COUNTIFS.

Repair Microsoft Office if formulas fail across workbooks
Try an Office repair if COUNTIF or COUNTIFS still fails in multiple workbooks after checking the formulas and data.
Count records by time in WPS Spreadsheets
Use COUNTIFS in WPS Spreadsheets to count time values within the same boundaries as in Excel.
- 1. Open your spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Select an empty cell: Click the cell where you want to display the number of records.
- 3. Enter COUNTIFS: Type =COUNTIFS(A2:A10,">="&TIME(21,0,0),A2:A10,"<"&TIME(22,0,0)) and press Enter. Replace A2:A10 with your time range.
- 4. Check the result: Confirm that the source cells contain real time values. Change "<" to "<=" in the second criterion if you also want to count exactly 22:00.

Frequently Asked Questions
Does the formula count exactly 22:00?
It does if the upper criterion is "<="&TIME(22,0,0). Use "<"&TIME(22,0,0) to exclude exactly 22:00.
Why does COUNTIFS return zero when I can see matching times?
The values may be stored as text or include dates. Use =TIMEVALUE(A2) in a helper column for text times, or =MOD(A2,1) to extract the time from a date and time value.
Can I count times across multiple dates?
Yes. To count by time of day regardless of date, enter =MOD(A2,1) in a helper column, fill it down, and apply the COUNTIFS time criteria to that column.
Should I reinstall Office if COUNTIFS fails in every workbook?
First check the formula, source values, and Formulas > Calculation Options. On Windows, you can then try Microsoft Office's Modify > Quick Repair option before considering a reinstall.




