Fix Excel UNIQUE and FILTER Results for Year and Source
Question details
The user needs an Excel formula that returns unique table items matching a specified year and source.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering the TRegistos table by the year in Q5 and the source in Q6, then returning distinct values from the Item column.
- Observed behavior
- The UNIQUE and FILTER formula returns more results than expected and is missing a closing parenthesis.
Check that Q5 contains the year, Q6 contains the source, and TRegistos has Item, Data, and Origem columns. Make sure the Data column contains valid Excel dates.
Correct the UNIQUE and FILTER Formula
Use the complete formula to apply both criteria before returning unique items.
Click the cell containing the existing UNIQUE and FILTER formula, then select the formula bar.
Replace the formula with =UNIQUE(FILTER(TRegistos[Item],(YEAR(TRegistos[Data])=$Q$5)*(TRegistos[Origem]=$Q$6))) and press Enter.
Review Q5 and Q6 to confirm they contain the intended year and source. The multiplication sign combines the two conditions, so both must match.
Check the spilled results below the formula cell against the Item, Data, and Origem columns. Clear any cells blocking the spill range if Excel displays a spill error.

Filter Unique Items by Year and Source in WPS Spreadsheets
Open the workbook in WPS Spreadsheets and use the corrected formula to filter items by year and source. The formula requires a WPS version that supports UNIQUE, FILTER, and structured table references.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose the Excel workbook.
- 2. Check the criteria: Enter the year in Q5 and the source in Q6, and confirm the TRegistos table and its columns are present.
- 3. Enter the formula: Select the result cell and enter =UNIQUE(FILTER(TRegistos[Item],(YEAR(TRegistos[Data])=$Q$5)*(TRegistos[Origem]=$Q$6))), then press Enter.
- 4. Inspect the results: Check the spilled items against the table. If WPS does not recognize a function or structured reference, check whether your WPS version supports it.

Frequently Asked Questions
Why does the formula multiply the two conditions?
The multiplication sign in the FILTER include argument combines the year and source tests. A row is included only when both conditions are true.
What should I enter in Q5 and Q6?
Enter the year as a number, such as 2024, in Q5. Enter the source value to match against TRegistos[Origem] in Q6.
Why does Excel show a spill error?
The formula needs empty cells below the result cell for its output. Clear any blocking content in the intended spill range and try again.
What happens if no rows match the criteria?
Excel FILTER returns a calculation error when there are no matches and no fallback is specified. Add an if-empty argument, such as "No matches", inside FILTER if you want a message instead.




