Formula Errors

How to Count Values in Excel During Specific 15-Minute Time Intervals

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to count the occurrences of specific values, such as vehicle categories, within distinct 15-minute time intervals over a 12-hour period.

Product: Excel
Device & OS: not provided
Scenario: Analyzing time-stamped survey data to find the frequency of occurrences within specific 15-minute time buckets.
Observed behavior: Basic COUNTIF and COUNTIFS formulas fail to return the correct counts for the 15-minute intervals due to incorrect boundary conditions or mismatched data formats.

Before you start

Before applying any formulas, select your time data column, right-click, and choose 'Format Cells' to ensure all values are formatted as proper Time values rather than plain Text.

Solution 1Recommended

Use COUNTIFS with Upper and Lower Time Boundaries

Applying a COUNTIFS function that specifies a starting time (inclusive) and an ending time (exclusive) is the most accurate way to bin data into 15-minute intervals without double-counting.

To accurately capture time intervals, your formula must check that a time is greater than or equal to the start of the interval, and strictly less than the end of the interval. Using the TIME(0,15,0) function allows you to dynamically add 15 minutes to any start time.

Set up your interval bins

In an empty column, manually list the starting times for your 15-minute intervals (e.g., type 8:00 AM in cell C2, 8:15 AM in C3, etc.).

Enter the COUNTIFS formula

Assuming your raw time data is in column A and your first interval start time is in C2, click on cell D2 and enter the formula: =COUNTIFS(A:A, ">="&C2, A:A, "<"&C2+TIME(0,15,0))

Apply to the remaining intervals

Click the small square at the bottom-right corner of cell D2 (the fill handle) and drag it down to apply the formula to the rest of your interval rows.

Boundary Accuracy: Using the strictly less than '<' operator for the upper boundary prevents double-counting records that occur precisely on the 15-minute mark (e.g., exactly at 8:15 AM).

Solution 2

Convert Text Strings to Real Excel Time Values

If your COUNTIFS formula correctly references boundaries but still returns zero, your source time data might be stored as text strings instead of recognized serial time values.

Powerful Data Analysis

Easily Analyze Time-Based Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced COUNTIFS formulas and native time functions, allowing you to seamlessly analyze survey data and categorize times into precise intervals without formatting headaches.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the raw time records and categories.
2. Format your time values: Highlight your data, press Ctrl+1 to open the Format Cells dialog, and ensure the format category is set to 'Time'.
3. Apply the COUNTIFS interval formula: Type =COUNTIFS(A:A, ">="&C2, A:A, "<"&C2+TIME(0,15,0)) in your result column to instantly calculate the 15-minute interval frequencies.

100% compatibility with Microsoft Excel formulas like COUNTIFS and TIMEBuilt-in 'Text to Columns' tools to easily convert raw text into time formatsLightweight application with high performance for massive survey datasetsCompletely free to use for your everyday data analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return 0 for time intervals?

This usually happens when your source time data is stored as text rather than a recognized time format. You can fix this by highlighting the column, going to the Data tab, clicking 'Text to Columns', and immediately clicking 'Finish' to convert the text to readable time.

How do I add multiple criteria, like a specific vehicle category, to the 15-minute interval count?

You can extend the COUNTIFS formula by appending more range and criteria pairs. For example, =COUNTIFS(A:A, ">="&C2, A:A, "<"&C2+TIME(0,15,0), B:B, "Car") will count only the entries labeled 'Car' within that specific 15-minute block.

Can I use Pivot Tables instead of formulas to group data into 15-minute intervals?

Yes. You can insert a Pivot Table, place your time field in the Rows area, right-click any time value in the Pivot Table, select 'Group', uncheck everything except 'Minutes', and set the 'Number of minutes' option to 15.

Excel & Data Problems