Table of Contents
Counting unique dates in Excel can be done by using the COUNTIF function and formatting the date column as a date. First, select the cell where you want the count to appear and enter the formula “=COUNTIF(A2:A10,A2)”, where A2:A10 is the range of cells that contains the dates you want to count. Then, press enter and the cell will display the number of unique dates in the selected range. To format the date column, select the cells and go to the “Home” tab, click on the “Number Format” drop-down menu, and choose the “Date” format. This will convert the dates into a recognizable format for Excel to count. Repeat this process for any other date columns you want to count.
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: