Share via

Counting unique dates in EXCEL

Anonymous
2023-01-22T02:18:12+00:00

Good day to all! I feel like there is a simple solution to my problem, but i cannot figure it out. In EXCEL I have a list of 5000 random dates in column A which span several years and there repeating (or duplicate) dates in the list. I want to count the number of unique dates for a given year. (e.g. How many unique dates in the list were from 2014?) Any solution or insight is greatly appreciated. Thank you!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2023-01-23T09:02:05+00:00

You are welcome.

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-01-23T08:55:07+00:00

    Ashish, thank you for commenting on my question. Your formula didn't work initially, as the cell result was the #CALC error. I realized that you might have forgot to include the COUNT function in your reply to this post. The resulting formula that solved the issue is:

      =COUNT(UNIQUE(FILTER(A2:A5001,YEAR(A2:A5001)=C1)))

    I didn't know that the FILTER function could solve the problem.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-01-23T08:39:05+00:00

    Sheeloo, I appreciate your input. Your solution provided me with the total number of unique dates, but I need to extract the total number for a given year.

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2023-01-22T23:19:06+00:00

    Hi,

    Assuming the dates are in range A2:A5001, type 2014 in cell C1 and enter this formula in cell C3

    =unique(filter(A2:A5001,year(A2:A5001)=C1))

    Hope this helps.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2023-01-22T04:55:15+00:00

    If you version of Excel has UNIQUE function then try

    =COUNTA(UNIQUE(A:A))-2

    • 2 to exclude heading and blank cells in Col A

    Was this answer helpful?

    0 comments