How to Count Distinct Values Using Two Criteria in Excel
Question details
The user needs to count unique trial numbers based on two conditions: a column description must start with a specific word, and another column must not be blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing trial data where unique counts are required based on a text condition in one column and a non-blank condition in another.
- Observed behavior
- The goal is to accurately calculate the number of distinct trial numbers that meet both criteria without manual counting.
Ensure you are using a version of Excel or WPS Spreadsheet that supports dynamic array functions like UNIQUE, FILTER, and LET, such as Microsoft 365, Excel 2021, or the latest WPS Office.
Use FILTER and UNIQUE Functions
Combine the FILTER and UNIQUE functions to isolate the target data, then count the distinct results.
This method uses dynamic arrays to first filter the dataset according to your two criteria, extract only the unique values from that filtered list, and finally count the remaining items.
Determine the columns containing your trial numbers (e.g., Column A), the part descriptions (e.g., Column D), and the condition values (e.g., Column Z).
Use the FILTER function to return trial numbers where the description starts with your target word and the value column is not blank. For example: FILTER(A3:A15, (LEFT(D3:D15, 4)="leaf") * (Z3:Z15<>"")).
Wrap the FILTER formula inside the UNIQUE function to remove any duplicate trial numbers from the filtered array.
Finally, wrap the entire formula in the COUNTA function to get the total number of distinct trial numbers.
Use the LET Function for Advanced Error Handling
Use the LET function to streamline the formula and cleanly handle cases where no data matches the criteria.
Count Distinct Values Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and LET, allowing you to perform complex data analysis and conditional counting seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your trial data.
- 2. Select the target cell: Click on the cell where you want to calculate the distinct count.
- 3. Enter the formula: Input your dynamic array formula combining UNIQUE and FILTER, exactly as you would in Microsoft Excel.
- 4. Calculate the result: Press Enter to instantly view your distinct count based on the applied criteria.
Frequently Asked Questions
What if my Excel version doesn't support UNIQUE or FILTER?
Older versions of Excel (like Excel 2016 or 2019) do not support dynamic array functions. You would need to use complex array formulas combining SUM, IF, and FREQUENCY. Alternatively, you can upgrade to the latest WPS Office or Microsoft 365 to use these modern functions for free.
Can I add a third criterion to this formula?
Yes. You can add more criteria to the FILTER function by adding another multiplication condition inside the parentheses. For example, adding *(C3:C15="Active") will include a third condition checking for the word 'Active' in column C.
Why is my formula returning a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no records matching your specified criteria. You can prevent this by using the 'if_empty' argument in the FILTER function or by wrapping your formula in an IFERROR or LET function.
