How to Count Unique Text Values Within a Date Range in Excel
Question details
The user needs to count the number of distinct text values (such as companies or clinical areas) that fall within a specific date range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing tabular data containing dates and text categories where duplicate text entries exist, and extracting a count of unique categories for a specified time period.
- Observed behavior
- The user wants to apply a formula to filter data by date and then count only the unique text occurrences within that filtered dataset.
Ensure your data is formatted as an Excel Table for easier formula referencing, and verify that your date column contains valid date values rather than text strings.
Use ROWS, UNIQUE, and FILTER Functions
Combine dynamic array functions to filter the data by date, extract unique values, and count the resulting rows.
This method leverages modern dynamic array functions available in newer versions of Excel and WPS Office. The FILTER function isolates the records within the date range, UNIQUE removes duplicates, and ROWS counts the final distinct items.
Click on the blank cell where you want the final unique count to appear.
Type the following formula: =ROWS(UNIQUE(FILTER(Table1[Company],(Table1[Date]>=DATE(2025,1,1))*(Table1[Date]<=DATE(2025,12,31)),"")))
Replace 'Table1[Company]' with the actual name of the column containing your text values, and 'Table1[Date]' with your date column.
Modify the DATE(YYYY,M,D) parameters in the formula to match your desired start and end dates.
Press Enter to execute the formula and calculate the total number of unique text values within that period.
Count Unique Values Easily with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like UNIQUE and FILTER, allowing you to perform complex data analysis and count distinct values within specific date ranges effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your data.
- 2. Format as Table: Select your data range and press Ctrl+T to format it as a Table for easier referencing.
- 3. Select a result cell: Click on a blank cell where you want the unique count to be displayed.
- 4. Input the formula: Enter the =ROWS(UNIQUE(FILTER(...))) formula tailored to your specific column names and date range.
- 5. Get your count: Press Enter to instantly calculate and display your unique count.
Frequently Asked Questions
What if my version of Excel doesn't support the UNIQUE or FILTER functions?
If you are using an older version of Excel (prior to Excel 2021 or Microsoft 365), these dynamic array functions are not available. You can use a more complex array formula combining SUM, IF, and FREQUENCY, or upgrade to a modern spreadsheet tool like WPS Office which supports these functions for free.
How can I reference cell values for the start and end dates instead of hardcoding them?
You can replace the DATE(2025,1,1) part of the formula with a cell reference. For example, if your start date is in cell G1 and end date is in G2, the condition becomes (Table1[Date]>=G1)*(Table1[Date]<=G2).
Why does the formula return a #CALC! error?
The #CALC! error usually occurs if the FILTER function finds no records matching your date range criteria. To fix this, ensure your date range is correct, or handle the error by wrapping the formula in an IFERROR function, like =IFERROR(ROWS(UNIQUE(FILTER(...))), 0).
Can I add more conditions, like filtering by a specific region?
Yes, you can add more criteria to the FILTER function by multiplying another condition. For example: *(Table1[Region]="North") added inside the FILTER criteria argument will restrict the unique count to the North region within your date range.
