Function Problems

How to Use COUNTIFS for ISO Dates and Not Available Values in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to count specific rows in a spreadsheet where cells match a specific string, fall within a certain year based on an ISO 8601 timestamp, or contain the text "Not Available".

Product: Excel
Device & OS: not provided
Scenario: Counting data records matching specific string conditions and timeframes that are formatted as ISO 8601 text strings.
Observed behavior: The spreadsheet contains text-based timestamps like "2024-12-23T21:45:53.336Z" alongside standard string markers, requiring a multi-condition formula to aggregate the counts properly.

Before you start

Ensure your ISO 8601 date column is consistently formatted as text (e.g., YYYY-MM-DDThh:mm:ss.000Z) so that the string comparison operators in your COUNTIFS function will work correctly.

Solution 1Recommended

Combine Multiple COUNTIFS Functions for Text-Based ISO Dates

Use a combination of COUNTIFS functions to add the counts of rows falling within a specific year and rows marked as "Not Available".

When ISO 8601 timestamps are stored as text, you can use standard alphabetical comparison operators (>= and <=) directly on the text strings within the COUNTIFS function. The '.000' represents milliseconds, and 'Z' denotes UTC time.

Because you need to count rows that either fall into a date range OR have a 'Not Available' status, you must create two separate COUNTIFS statements and add their results together.

Select the target cell

Click on the cell where you want the final calculated count to appear.

Enter the date range formula

Type the first part of the formula to count the dates within the target year: `=COUNTIFS($I:$I, "placeholder", H:H, ">=2024-01-01T00:00:00.000Z", H:H, "<=2024-12-31T23:59:59.999Z")`. Replace "placeholder" with your actual target string criterion.

Append the 'Not Available' condition

Add a plus sign and the second COUNTIFS function to include the 'Not Available' entries: `+COUNTIFS($I:$I, "placeholder", H:H, "Not Available")`.

Calculate the total

Press the Enter key to calculate the total combined count across both conditions.

End of Year Timestamp Accuracy: Make sure your ending timestamp uses '23:59:59.999Z' instead of '11:59:59.000Z' to accurately include the entire final day of the year in your count.

Efficient Data Analysis

Master Complex Formulas with WPS Spreadsheet

WPS Spreadsheet offers full support for advanced functions like COUNTIFS, making it incredibly easy to analyze complex datasets containing ISO 8601 dates and multiple text conditions.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your ISO dates.
2. Select a cell for calculation: Click on an empty cell where you wish to display the results.
3. Input the formula: Enter your combined COUNTIFS formula using standard Excel syntax.
4. View the result: Press Enter to instantly execute the formula and analyze your data.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Lightweight and fast, easily handling large datasets with complex text-based timestamps.Familiar interface, allowing for a seamless transition without having to relearn your spreadsheet tools.

microsoft office alternative - wps office

Frequently Asked Questions

What does the 'Z' in the ISO timestamp mean?

The 'Z' at the end of an ISO 8601 timestamp (such as 2024-12-23T21:45:53.336Z) stands for 'Zulu' time, which indicates that the time is in Coordinated Universal Time (UTC) with no local time zone offset.

Why do I need two COUNTIFS functions instead of one?

The COUNTIFS function inherently uses 'AND' logic for all of its criteria. To count rows that are EITHER in a specific date range OR marked as 'Not Available', you are dealing with 'OR' logic. You must calculate these two scenarios separately and add the results together using the plus (+) operator.

Can I use standard date formats instead of ISO text strings in COUNTIFS?

Yes. If you convert your ISO text strings into standard date/time values using the VALUE function, DATEVALUE function, or the Text-to-Columns feature, you can simplify your COUNTIFS formula to use standard date criteria like ">=1/1/2024".

Excel & Data Problems