How to Count Unique Nonblank Values in Excel using UNIQUE and FILTER
Question details
The user needs to calculate the total number of distinct, non-empty values within a specific data column in a spreadsheet, ensuring that blank cells do not skew the result.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing data analysis where a dataset contains duplicate values and blank cells, and a precise count of unique valid entries is required.
- Observed behavior
- The goal is to return an accurate integer count of uniquely occurring values excluding empty cells.
Ensure your spreadsheet software version supports dynamic array functions (like UNIQUE and FILTER), as older versions may return a #NAME? error when these formulas are used.
Use the ROWS, UNIQUE, and FILTER Functions
Combine these dynamic array functions to extract unique values, exclude blanks, and count the remaining rows.
This method is the most efficient and robust way to handle dynamic ranges without relying on complex array formulas or helper columns.
Click on the cell where you want the final distinct count to be displayed.
Type the formula =ROWS(UNIQUE(FILTER(B2:B8, B2:B8<>""))) into the formula bar. Replace B2:B8 with the actual range of your data.
Press the Enter key. The cell will now display the total number of distinct, non-empty values in your specified range.
Alternative: Use SUMPRODUCT and COUNTIF for Older Versions
If your software does not support dynamic arrays, use a traditional SUMPRODUCT formula to achieve the same result.
Quickly Analyze Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and FILTER, allowing you to process distinct data counts seamlessly and efficiently.
- 1. Open your workbook: Launch WPS Office and open your spreadsheet file.
- 2. Select a result cell: Click on a blank cell where you want the distinct count to appear.
- 3. Apply the formula: Type =ROWS(UNIQUE(FILTER(B2:B8,B2:B8<>""))) and press Enter.
- 4. View the result: Instantly view the accurate count of unique non-blank values without extra configuration.
Frequently Asked Questions
Why does my UNIQUE function return a #NAME? error?
This error usually occurs if your spreadsheet software version is older and does not support dynamic array functions. Consider upgrading to the latest version of WPS Office or Microsoft 365, or use the SUMPRODUCT alternative provided in the solutions.
Can I count unique values based on multiple conditions?
Yes, you can add more conditions to the FILTER function by multiplying them. For example, use =ROWS(UNIQUE(FILTER(B2:B8,(B2:B8<>"")*(C2:C8="Yes")))) to count unique values where the corresponding row in column C equals 'Yes'.
Does this formula treat text and numbers differently?
The UNIQUE function evaluates distinct values exactly as they are formatted. Make sure there are no hidden trailing spaces in your text cells, as this will cause otherwise identical words to be treated as unique entries and counted twice.
