Function Problems

How to Use Excel UNIQUE and FILTER Formulas for Course Statistics

Chanuka Geekiyanage Oct 10, 2026 869 views

Question details

The user needs to count unique course numbers based on multiple criteria, such as completion status and academy classification, within a dynamic data range.

How to Calculate Course Statistics Using Excel UNIQUE and FILTER Formulas

Product: Excel
Device & OS: not provided
Scenario: Generating course statistics by filtering rows across different classification columns and extracting the unique course counts.
Observed behavior: Requires a dynamic formula setup using UNIQUE, FILTER, and COUNTA applied to columns for course numbers, class identifiers, and completion statuses.

Before you start

Ensure you are using a version of Excel that supports dynamic array functions, such as Microsoft 365 or Excel 2021, as older versions do not include the UNIQUE or FILTER functions.

Solution 1Recommended

Calculate Unique Courses Using COUNTA, UNIQUE, and FILTER

Apply multiplying Boolean conditions within the FILTER function to dynamically isolate specific courses, remove duplicates with UNIQUE, and count the remainder.

The FILTER function includes an 'include' argument that evaluates conditions as TRUE (1) or FALSE (0). By multiplying multiple criteria arrays together, you create an 'AND' logic requirement where only rows meeting every condition are included in the filtered array.

Define your dynamic range

Identify the cell containing your last row number (e.g., cell Y3). Construct your data ranges dynamically using the INDIRECT function, such as INDIRECT("$C$6:$C$"&$Y$3) for the course numbers column.

Set up the FILTER conditions

Combine multiple criteria by multiplying them. For example, to filter for completed courses in the specified academy, use (INDIRECT("$K$6:$K$"&$Y$3)="Yes")*(INDIRECT("$H$6:$H$"&$Y$3)="Yes").

Nest FILTER inside UNIQUE

Wrap the FILTER formula within the UNIQUE function to ensure that duplicate course numbers are only counted once in your final statistics.

Count the final array

Enclose the entire formula with the COUNTA function. Enter =COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$"&$Y$3),(INDIRECT("$K$6:$K$"&$Y$3)="Yes")*(INDIRECT("$H$6:$H$"&$Y$3)="No")))) into your target cell, such as Y10, and press Enter.

Calculate Unique Courses Using COUNTA, UNIQUE, and FILTER

Handling Blank Conditions: If you need to filter for blank cells in a column as part of your criteria, you can use empty quotation marks "" in your condition, for example: (INDIRECT("$K$6:$K$"&$Y$3)="").

Powerful Spreadsheet Tool

Calculate Complex Statistics with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like UNIQUE and FILTER, allowing you to seamlessly process complex statistical formulas, filter multiple criteria, and analyze data efficiently.

1. Open the dataset: Launch WPS Spreadsheet and open the workbook containing your course numbers and classification data.
2. Enter the nested formula: Select your target summary cell and type your nested =COUNTA(UNIQUE(FILTER(...))) formula with your specific criteria.
3. Calculate the result: Press the Enter key to instantly calculate the unique course counts based on your specified dynamic parameters.

Full compatibility with Excel dynamic array formulas like UNIQUE and FILTERFamiliar user interface requiring no steep learning curveHigh performance for processing large datasets and dynamic statistical ranges

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

The #CALC! error occurs when the FILTER function finds no matching records based on your given criteria. You can prevent this error by adding an alternative string or value for the optional [if_empty] argument at the end of the FILTER function syntax.

How does multiplying conditions in the FILTER function work?

Multiplying conditions acts as an 'AND' logic operator. Excel evaluates each condition as TRUE (1) or FALSE (0). When multiplied, only rows where all conditions equal 1 will be included in the final array, as any condition evaluating to FALSE (0) will turn the whole row's multiplication result to 0.

Can I count unique values with multiple conditions without dynamic array functions?

Yes, in older versions of Excel that lack UNIQUE and FILTER, you can use a complex combination of SUMPRODUCT and COUNTIFS, or array formulas entered with Ctrl+Shift+Enter. However, utilizing dynamic arrays is significantly simpler and more efficient.

Excel & Data Problems