How to Count Unique Excel Values Containing a Specific Character
Question details
The user needs to count distinct (unique) values in a data range that contain a specific letter or character, including options for both case-sensitive and case-insensitive matching.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data analysis requiring the extraction of unique items that meet a specific text criteria.
- Observed behavior
- The goal is to accurately calculate the total number of unique matching values without double-counting duplicates.
Ensure you are using a modern version of Excel or WPS Spreadsheet that supports dynamic array formulas, specifically the UNIQUE function.
Use Dynamic Array Formulas with UNIQUE and SEARCH (Recommended)
Combine SUM, ISNUMBER, SEARCH (or FIND), and UNIQUE to cleanly count specific values without creating extra helper columns.
This method uses dynamic array functions to first filter out duplicates, and then validates whether the remaining unique strings contain the target character.
Click on an empty cell in your worksheet where you want the final count to be displayed.
For a case-insensitive count (matches both 'P' and 'p'), type the formula: =SUM(--ISNUMBER(SEARCH("p",UNIQUE(A2:A18)))) and press Enter.
If you need an exact case match, use FIND instead of SEARCH: =SUM(--ISNUMBER(FIND("P",UNIQUE(A2:A18)))).
To count unique values that do not contain the target letter, swap ISNUMBER with ISERROR: =SUM(--ISERROR(SEARCH("p",UNIQUE(A2:A18)))).
Use UNIQUE with COUNTA and FILTER
An alternative dynamic array formula structure utilizing the FILTER and COUNTA functions to isolate and count the target values.
Count Unique Data Efficiently with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like UNIQUE and FILTER, allowing you to seamlessly analyze text criteria and count specific characters. Enjoy a familiar interface and strong compatibility with Microsoft Excel.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your target data column.
- 2. Apply the array formula: Click on a blank cell and type =SUM(--ISNUMBER(SEARCH("P", UNIQUE(A2:A18)))).
- 3. Calculate the result: Press Enter to instantly process the array and return the exact unique character count.
Frequently Asked Questions
Why does my UNIQUE formula return a #NAME? error?
This error occurs if you are using an older version of Excel that doesn't support the UNIQUE dynamic array function. You will need Microsoft 365, Excel 2021, or a modern alternative like WPS Spreadsheet to use this formula.
What is the difference between SEARCH and FIND in these formulas?
The SEARCH function is case-insensitive, meaning it treats 'P' and 'p' as the same character. The FIND function is case-sensitive and will only match the exact uppercase or lowercase character you specify.
How can I count unique values that exactly match a specific word instead of just containing a character?
You can use the formula =COUNTA(UNIQUE(FILTER(A2:A18, A2:A18="ExactWord"))). The FILTER function handles the exact match condition before UNIQUE removes duplicates and COUNTA calculates the total.
