Function Problems

How to Count Excel Records by Minute Using COUNTIFS

Jason Miller Oct 22, 2026 868 views

Question details

The user needs to count records based on one-minute intervals using the COUNTIFS function, but the formula is not returning the correct results for date-and-time data.

How to Count Excel Records by Minute Using COUNTIFS

Product: Excel
Device & OS: not provided
Scenario: Counting records from a column formatted as DD/MM/YY HH:MM:SS by specific one-minute intervals.
Observed behavior: COUNTIFS fails to count the records correctly, often because the date-and-time values are being evaluated as text strings rather than real numeric Excel dates.

Before you start

Verify whether your cells contain actual numerical date-time values or text strings. Real Excel date-time values are right-aligned by default, while text values are left-aligned.

Solution 1Recommended

Convert to Date-Time Values and Apply COUNTIFS

Ensure your data is recognized as numerical date-time values, then apply a standard COUNTIFS formula using greater-than and less-than criteria.

Excel requires actual numeric date-time values to evaluate greater than or less than comparisons correctly. If your timestamps are stored as text, the COUNTIFS formula will fail to interpret the chronological order.

Check Data Format

Select your column containing the date and time values. Use the 'Text to Columns' wizard in the Data tab to convert text-formatted dates into real numerical date-time values.

Set Up Time Intervals

In an empty cell (e.g., C1), type the exact start time of your one-minute interval. In the adjacent cell (e.g., C2), type the end time of that minute.

Enter the COUNTIFS Formula

In your result cell, enter the formula: =COUNTIFS($A$1:$A$100,">="&C1,$A$1:$A$100,"<"&C2) and press Enter to calculate the number of records within that minute.

Dynamic Intervals: Using cell references like C1 and C2 makes it easy to drag the formula down for consecutive minutes without rewriting the criteria.

Solution 2

Use SUMPRODUCT and TEXT Functions for Text-Based Dates

If you cannot alter the raw data formatting, use SUMPRODUCT in combination with the TEXT function to extract and count matching minutes directly.

Efficient Data Analysis with WPS Office

Easily Count and Analyze Time-Series Data in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like COUNTIFS, SUMPRODUCT, and automated text-to-date conversions. You can efficiently analyze high-frequency time-series data using a familiar and highly compatible interface.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your date and time records.
2. Format Cells: Highlight your date and time column, right-click, and select 'Format Cells' to verify the numeric date-time format.
3. Apply the Formula: Select an empty cell and type your =COUNTIFS() formula referencing your time intervals, exactly as you would in Microsoft Excel.
4. View Results: Press Enter to instantly view your counted records by the minute, and drag the fill handle to apply it across other time slots.

Fully compatible with Microsoft Excel formulas like COUNTIFS and SUMPRODUCTBuilt-in Text to Columns wizard for effortless date and time formattingLightweight software designed to process large datasets quickly and without laggingFree to use for everyday data analysis, reporting, and charting

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS return 0 when my dates and times look perfectly correct?

Your dates and times are likely stored as text. COUNTIFS requires numerical date-time values to evaluate greater than (>) or less than (<) criteria properly. Select your data, navigate to the Data tab, and use 'Text to Columns' to convert them into true date values.

How can I extract just the time from a DD/MM/YY HH:MM:SS cell for counting?

If the cell contains a real numeric date-time value, you can extract just the time using the MOD function (e.g., =MOD(A1, 1)). If you need it as a text string, you can use the TEXT function (e.g., =TEXT(A1, "hh:mm")).

Can I count records between two specific times while completely ignoring the date?

Yes. The best approach is to extract the time into a helper column using the =MOD(A1, 1) formula. You can then use the COUNTIFS function on that helper column, setting your criteria to only check the specific time boundaries.

Excel & Data Problems