To count unique dates you can use a formula based on the UNIQUE and COUNT functions or apply the COUNTIF and FREQUENCY functions.
Today’s lesson is a part of our Excel formulas guide, your go-to for formula skills.
How to count unique dates in Excel?
Here are the steps to count unique dates in Excel:
- Open Excel.
- Type =COUNTA(UNIQUE(dates)) where dates is the range containing the dates you want to count.
- Press Enter.
- The formula will return the number of unique dates in the specified range.
For non-Microsoft 365 users:
- Open Excel.
- Type =SUM(–(FREQUENCY(dates,dates)>0)) as an array formula, replacing dates with the range of your data.
- Press Ctrl+Shift+Enter.
- The formula will return the count of unique dates.
Example
In this example, we have a dataset in range B3:B17 containing dates associated with flight details such as flight number, origin, destination, and status. The goal is to calculate the total number of unique dates in the dataset. The formula in cell H3 uses the UNIQUE function to identify distinct dates in the specified range and the COUNT function to count them. This approach dynamically determines the total number of unique dates present in the dataset.
We are using a named range “dates”, which is refers to B3:B17.
Formula:
=COUNT(UNIQUE(dates))

The formula counts how many distinct dates are in the B3:B17 range. The result, 9, represents the number of unique dates in your data.
Explanation
Here’s a detailed breakdown of the formula =COUNT(UNIQUE(dates)):
UNIQUE(dates): The UNIQUE function extracts distinct values from the dates range. If dates contains duplicates, UNIQUE ensures that each value is listed only once. For example, if dates is {2025-01-01, 2025-01-01, 2025-01-02}, the result of UNIQUE is {2025-01-01, 2025-01-02}.
This formula operates dynamically, meaning it updates automatically when the range changes.
COUNT(UNIQUE(dates)): The COUNT function calculates the number of entries in the array produced by UNIQUE(dates). Using the example above, the array contains nine unique dates, so COUNT returns 9.
Using SUMPRODUCT
For non-Microsoft 365 users, you can use a workaround with the following formula:
=SUMPRODUCT(1/COUNTIF(dates,dates))
The range B3:B17 represents the “Date” column in the table, containing the flight dates. These dates include some duplicates, such as 1/2/2025 appearing multiple times.
- COUNTIF(dates,dates) evaluates how many times each date appears in the range. For instance, for the range {1/1/2025, 1/2/2025, 1/2/2025}, the COUNTIF result would be {1, 3, 3} because: 1/1/2025 appears once and 1/2/2025 appears three times.
- 1/COUNTIF(dates,dates) dividing 1 by the COUNTIF results produces a fractional array. Dates that appear multiple times contribute fractions (e.g., 1/3 for duplicates). Dates that appear once contribute 1. For the above example, the array is {1, 0.333, 0.333}.
- SUMPRODUCT(…): SUMPRODUCT sums all these fractions, effectively counting each unique date as 1. For the example above, 1 + 0.333 + 0.333 = 1.666…, but since only two unique dates exist, the sum equals 2.
The formula processes the dates in your range, which contain duplicates such as 1/2/2025 and 1/10/2025. The result of SUMPRODUCT(1/COUNTIF(dates,dates)) is 9, reflecting the 9 unique dates in B3:B17.
Workaround with FREQUENCY function
Take a look at the formula below:
=SUM(–(FREQUENCY(dates,dates)>0))
- FREQUENCY(dates, dates): The FREQUENCY function calculates how many times each unique date appears in the dates range (B3:B17 in your table). It creates a “bin” for each unique date and counts occurrences for each. For example, if dates = {1/1/2025, 1/2/2025, 1/2/2025}, FREQUENCY(dates, dates) returns {1, 2}: 1/1/2025 appears once, so the first bin is 1. Then, 1/2/2025 appears twice, so the second bin is 2.
- FREQUENCY(dates, dates) > 0 checks which bins have counts greater than 0, converting the output to a Boolean array. Using the same example, the result is {TRUE, TRUE} because both bins contain values greater than 0.
- –(FREQUENCY(dates, dates) > 0) uses the double negative method to convert a boolean array into numbers.