Formula Errors

Fix Excel UNIQUE and FILTER Results for Year and Source

Nimra MalikNimra Malik Nov 18, 2026 869 views

Question details

The user needs an Excel formula that returns unique table items matching a specified year and source.

Fix Excel UNIQUE and FILTER Results for 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.
Before you start

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.

Solution 1Recommended

Correct the UNIQUE and FILTER Formula

Use the complete formula to apply both criteria before returning unique items.

1
Select the formula cell

Click the cell containing the existing UNIQUE and FILTER formula, then select the formula bar.

2
Enter the corrected formula

Replace the formula with =UNIQUE(FILTER(TRegistos[Item],(YEAR(TRegistos[Data])=$Q$5)*(TRegistos[Origem]=$Q$6))) and press Enter.

3
Check the criteria

Review Q5 and Q6 to confirm they contain the intended year and source. The multiplication sign combines the two conditions, so both must match.

4
Review the results

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.

Correct the UNIQUE and FILTER Formula
If the results are still unexpected: A missing parenthesis normally causes a formula syntax error, so correcting it alone may not explain extra results. Check the dates, source text, and table rows for unexpected matches.
Work with spreadsheet formulas

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. 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose the Excel workbook.
  2. 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. 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. 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.
Filter items using both year and source criteriaReturn distinct matching items with UNIQUEOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.