Table of Contents
Counting unique dates in Excel can be done by using the COUNTIF function and selecting a range of cells containing dates. This will provide the total number of unique dates in the selected range. For example, if a range of cells contains dates from January 1st to January 5th, the COUNTIF function will return a value of 5, as there are 5 unique dates within that range. This method can be used for any set of dates in Excel to accurately count the number of unique dates.
Count Unique Dates in Excel (With Examples)
You can use the following formulas to count unique dates in Excel:
Method 1: Count Unique Dates
=COUNT(UNIQUE(B2:B11))
This particular formula counts the number of unique dates in the range B2:B11.
Method 2: Count Unique Dates with Criteria
=SUM(--(LEN(UNIQUE(FILTER(B2:B11,A2:A11="B","")))>0))
This particular formula counts the number of unique dates in the range B2:B11 where the corresponding value in the range A2:A11 is “B.”
The following examples show how to use each formula in practice with the following dataset in Excel that shows the number of sales made on various dates by two different retail stores:
Example 1: Count Unique Dates in Excel
Suppose we would like to count the number of unique dates in the Date column of our dataset.
We can type the following formula into cell E2 to do so:
=COUNT(UNIQUE(B2:B11))
The following screenshot shows how to use this formula in practice:
The output tells us that there are 6 unique dates in column B.
Example 2: Count Unique Dates with Criteria in Excel
We can type the following formula into cell E2 to do so:
=SUM(--(LEN(UNIQUE(FILTER(B2:B11,A2:A11="B","")))>0))
The following screenshot shows how to use this formula in practice:
The output tells us that there are 3 unique dates for Store B.
We can manually identify these three unique dates:
- 1/6/2024
- 1/7/2024
- 1/8/2024
Additional Resources
The following tutorials explain how to perform other common operations in Excel: