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:
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
