Function Problems

How to Count Unique Values with Multiple Conditions in Excel

Khadija Khan Oct 13, 2026 868 views

Question details

The user needs a formula to count unique text entries (such as town names) based on multiple conditions, including matching a specific depot, a specific year, ignoring blanks, and requiring a corresponding value greater than zero.

How to Count Unique Values with Multiple Conditions in Excel

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Data summarization requiring counting distinct text values across multiple criteria using dynamic arrays on a summary worksheet.
Observed behavior: The goal is to dynamically output the correct count of unique towns in a summary worksheet based on the source data conditions.

Before you start

Ensure your spreadsheet software supports dynamic array functions like UNIQUE and FILTER. If you are using an older version of Excel, you may need to use an alternative array formula approach.

Solution 1Recommended

Use COUNTA, UNIQUE, and FILTER Functions

Combine dynamic array functions to filter the dataset based on your conditions, extract the unique values, and then count them.

The most efficient way to solve this is by nesting the FILTER, UNIQUE, and COUNTA functions. The FILTER function isolates the data that meets your multiple criteria, UNIQUE removes duplicates, and COUNTA counts the final list.

Select the target cell

Click on the cell in your Summary sheet where you want the result to appear (for example, cell E6).

Construct the FILTER function

Use the FILTER function to isolate the towns that meet your criteria. Multiply your condition arrays together to enforce 'AND' logic. For example: FILTER(RS!$H$4:$H$451, (RS!$D$4:$D$451=$B$3)*(RS!$H$4:$H$451<>"")*(RS!$K$4:$K$451=B6)*(RS!$L$4:$L$451>0), "")

Wrap with the UNIQUE function

Wrap your FILTER formula inside the UNIQUE function to remove any duplicate town names from the filtered list: UNIQUE(FILTER(...))

Wrap with the COUNTA function

Finally, enclose the entire formula in the COUNTA function to count the total number of unique towns returned: =COUNTA(UNIQUE(FILTER(RS!$H$4:$H$451, (RS!$D$4:$D$451=$B$3)*(RS!$H$4:$H$451<>"")*(RS!$K$4:$K$451=B6)*(RS!$L$4:$L$451>0), "")))

Apply and fill down

Press Enter to calculate the result. You can then drag the fill handle down to apply the formula to other rows for different years.

Use COUNTA, UNIQUE, and FILTER Functions

Using Multiple Criteria in FILTER: The asterisk (*) acts as an AND operator in the FILTER function's include argument, ensuring that the row is only returned if all nested conditions evaluate to TRUE.

Easily Count Unique Values with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and FILTER, allowing you to seamlessly handle complex data summarization and multi-criteria counting tasks without needing a subscription.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the raw data and summary sheets.
2. Input the combined formula: Navigate to your summary cell and type the nested =COUNTA(UNIQUE(FILTER(...))) formula just as you would in Excel.
3. Apply to other cells: Press Enter to get your unique count, and drag the fill handle to seamlessly copy the formula to adjacent cells.

Fully supports dynamic array formulas like UNIQUE, FILTER, and SEQUENCE.Highly compatible with Microsoft Excel (.xlsx) file formats and functions.Lightweight software with a familiar, easy-to-use interface.Free to use for everyday data analysis and reporting.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return 1 when there are no matching results?

If FILTER finds no matches and returns an empty string (" ") or an error depending on your arguments, the COUNTA function still counts that empty string or error as 1 item. To prevent this, you can wrap the formula in an IF statement to check if the result is empty before counting.

Are UNIQUE and FILTER functions available in older versions of Excel?

No, UNIQUE and FILTER are dynamic array functions introduced in Excel 365 and Excel 2021. If you are using an older version, you would need to use complex INDEX/MATCH array formulas or a Pivot Table to count unique values based on conditions.

How do I ignore blank cells when counting unique text values?

You can add an additional condition to your FILTER function specifying that the target column is not equal to an empty string. For example, multiplying by (range<>"") ensures blank cells are excluded from the results.

Can I use 'OR' logic instead of 'AND' logic in the FILTER function?

Yes, while the asterisk (*) operator is used for 'AND' conditions, you can use the plus sign (+) operator to evaluate 'OR' conditions within the FILTER function's include argument.

Excel & Data Problems