Excel: Count Unique Values Based on Date Range


You can use the following formula to count the number of unique values based on a date range in Excel:

=SUMPRODUCT(IF((A2:A11<=E2)*(A2:A11>=E1),1/COUNTIFS(A2:A11,"<="&E2,A2:A11,">="&E1,B2:B11,B2:B11),0))

This particular formula counts the number of unique values in the range B2:B11 where the corresponding date in the range A2:A11 is between the start date in cell E1 and the end date in cell E2.

The following example shows how to use this formula in practice.

Example: Count Unique Values Based on Date Range in Excel

Suppose we have the following dataset that contains information about products sold on various dates by some retail store:

Suppose we would like to count the number of unique items sold between 1/5/2023 and 1/20/2023.

We can type these start and end dates in cells E1 and E2, respectively, and then type the following formula into cell E3 to count the number of unique items sold between these dates:

=SUMPRODUCT(IF((A2:A11<=E2)*(A2:A11>=E1),1/COUNTIFS(A2:A11,"<="&E2,A2:A11,">="&E1,B2:B11,B2:B11),0))

The following screenshot shows how to use this formula in practice:

Excel count unique based on date

We can see that 3 unique items were sold between 1/5/2023 and 1/20/2023.

We can manually verify this is correct by identifying the three unique items sold between these dates:

  • Couch
  • Stool
  • Chair

Note that if we change the start or end date, the formula will automatically update to count the unique values between the new dates.

For example, suppose we change the end date to 1/30/2023:

We can see that 4 unique items were sold between 1/5/2023 and 1/30/2023.

We can manually verify this is correct by identifying the four unique items sold between these dates:

  • Couch
  • Stool
  • Chair
  • Clock

Feel free to change the start and end dates to whatever dates you’d like.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Count Unique Names
Excel: How to Count Unique Values by Group
Excel: How to Count Unique Values Based on Multiple Criteria

One Reply to “Excel: Count Unique Values Based on Date Range”

  1. Hi, I’m trying to use this formula and not having any luck. Any chance you could help out? Also, is this only going to work in the newest version of excel?

Leave a Reply

Your email address will not be published. Required fields are marked *