Function Problems

Count Records in a Time Range with Excel COUNTIFS

Tauseeq MagsiTauseeq Magsi Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select a result cell

In Excel, click an empty cell where you want the count to appear.

2
Enter the formula

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.

3
Choose the upper boundary

To exclude exactly 22:00, change the second criterion from "<="&TIME(22,0,0) to "<"&TIME(22,0,0).

4
Check unexpected results

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.

Use COUNTIFS to count times between 21:00 and 22:00
Counting date and time values: If cells contain both dates and times, use a helper column with =MOD(A2,1), fill it down, and apply COUNTIFS to that column to count by time of day.
Count time ranges in WPS

Count records by time in WPS Spreadsheets

Use COUNTIFS in WPS Spreadsheets to count time values within the same boundaries as in Excel.

  1. 1. Open your spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Select an empty cell: Click the cell where you want to display the number of records.
  3. 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. 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.
Count records within a time range using COUNTIFSOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.