Formula Errors

Excel Formula to Count Unique Dates Within a Fixed Date Range

WPS Editor Sep 28, 2026 869 views

Question details

The user needs to calculate a single number representing the count of unique dates falling within a specific date range, avoiding formula spill or argument errors.

How to Count Unique Dates Within a Fixed Date Range in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting a distinct count of dates that fall strictly between a specified start and end date from a table containing repeated entries.
Observed behavior: The user encounters a "Too many arguments" error when separating multiple FILTER conditions with commas, and experiences a spill error when the formula returns a list of dates instead of a single count.

Before you start

Verify that your spreadsheet software supports dynamic array functions like UNIQUE, FILTER, and LET, which are required for the most efficient modern formula solutions.

Solution 1Recommended

Use Multiplication to Combine Conditions in FILTER

Multiply the start and end date criteria together within the FILTER function to evaluate them as a single array, preventing the "Too many arguments" error.

The FILTER function only accepts a maximum of three arguments. When you try to add a start date and an end date condition separated by commas, Excel registers them as extra arguments and throws an error.

By placing each condition in parentheses and multiplying them together, you create a single logical AND array that fits perfectly into FILTER's second argument.

Select the result cell

Click on the specific cell where you want the final unique date count to be displayed.

Enter the LET and FILTER combination formula

Type the formula: =LET(d,FILTER(INT(Table[Date]),(Table[Date]>=DATE(2023,7,1))*(Table[Date]<=DATE(2024,6,30)),""),IFERROR(COUNT(UNIQUE(d)),0))

Calculate the result

Press Enter. The COUNT(UNIQUE()) wrapping ensures that Excel returns a single integer rather than spilling the filtered dates into adjacent cells.

Use Multiplication to Combine Conditions in FILTER

Why This Works: The multiplication symbol (*) acts as a Boolean AND operator. It evaluates both date criteria simultaneously as 1s (TRUE) and 0s (FALSE) before passing them to the FILTER function.

Solution 2

Use FREQUENCY for Legacy Excel Versions

For older versions of Excel that do not support dynamic arrays, use a combination of FREQUENCY, IF, and SUM to count distinct values within a date range.

Advanced Data Analysis Tool

Easily Analyze Date Ranges with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and LET, making it simple to count unique dates within fixed ranges without complex workarounds.

1. Open your data table: Launch WPS Spreadsheet and open the document containing your date records.
2. Select the output cell: Click on the blank cell where you want the final distinct count number to appear.
3. Enter the dynamic formula: Paste the formula: =LET(d,FILTER(INT(A2:A100),(A2:A100>=DATE(2023,7,1))*(A2:A100<=DATE(2024,6,30)),""),IFERROR(COUNT(UNIQUE(d)),0))
4. View the exact count: Press Enter to instantly process the criteria and display your single numerical result.

100% compatible with Microsoft Excel formulas, formatting, and functionsNative support for modern dynamic arrays to handle complex data lookupsLightweight installation and completely free to use for daily tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a 'Too many arguments' error?

The FILTER function only accepts up to three arguments: the array, the include criteria, and the 'if empty' value. If you separate multiple conditions (like a start date and an end date) with commas, Excel treats them as extra arguments. You must multiply them together, like (Condition1)*(Condition2), to combine them into a single argument.

Why is my formula returning a list of dates instead of a single number?

If you only use FILTER and UNIQUE, the formula outputs all the unique matching dates, causing a list or a spill error. To get a single number, wrap your UNIQUE function inside a COUNT function, for example: COUNT(UNIQUE(filtered_range)).

How does the LET function help when filtering dates?

The LET function allows you to assign a variable name to a complex expression. In this case, defining 'd' as the filtered array prevents you from having to write out the long FILTER formula multiple times when applying the COUNT and UNIQUE functions, which improves both readability and calculation speed.

Excel & Data Problems