How to Count Distinct Partial Matches in Excel (COUNTIF & UNIQUE)
Question details
The user needs to count the number of unique items within a range that contain a specific partial text string.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a dataset to find the exact number of distinct varieties (e.g., unique product names) that include a specific search keyword, ignoring duplicates.
- Observed behavior
- The standard COUNTIF function counts every single row that matches the criteria, meaning duplicate values containing the search term are counted multiple times instead of just once.
Check your Excel version before proceeding, as modern dynamic array formulas (like UNIQUE and FILTER) are only available in Microsoft 365, Excel 2021, and updated versions of WPS Office.
Use Dynamic Array Formulas (Excel 365 & Modern Office)
The most efficient and direct method using modern array functions to filter, extract unique values, and count them in one step.
This approach nests several powerful functions. SEARCH looks for the partial text, ISNUMBER converts matches to TRUE/FALSE, FILTER isolates the matching rows, UNIQUE removes duplicates, and COUNTA counts the remaining distinct items.
Click on the cell where you want the distinct count to appear.
Type `=COUNTA(UNIQUE(FILTER(C2:C20,ISNUMBER(SEARCH(I2,C2:C20)))))`, assuming C2:C20 is your data range and I2 contains your search term (e.g., "bean").
Press Enter. The formula will automatically process the array and return the exact number of distinct partial matches.
Use a Helper Column and COUNTIF (Older Excel Versions)
A fully backward-compatible method for users on Excel 2019 or older who do not have access to the UNIQUE function.
Count Distinct Data Effortlessly in WPS Spreadsheet
WPS Office provides robust native support for modern data analysis functions, including dynamic arrays like UNIQUE and FILTER, allowing you to solve complex counting problems without complicated workarounds.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 2. Select the target cell: Click on the cell where you want to display the distinct count.
- 3. Enter the formula: Type the standard dynamic formula `=COUNTA(UNIQUE(FILTER(C2:C20,ISNUMBER(SEARCH(I2,C2:C20)))))`.
- 4. Get instant results: Press Enter to instantly calculate the unique instances of your partial text match.
Frequently Asked Questions
Why does the standard COUNTIF function count duplicates?
The COUNTIF function evaluates each cell in a range individually against the provided criteria. It does not possess a built-in mechanism to check if a matching value has already been counted previously in the range, which is why duplicates are included in the final tally.
How do wildcards work in partial match formulas?
Wildcards, represented by asterisks (*), stand for any number of characters. In formulas like `=COUNTIF(D2:D11,"*"&I2&"*")`, placing an asterisk before and after the cell reference tells the program to count cells where the text in I2 appears anywhere within the cell, regardless of what comes before or after it.
What happens if there are empty cells in my data range?
Empty cells can sometimes cause array functions like UNIQUE to return a '0' or an empty string, which COUNTA might accidentally count as a value (resulting in an inflated count). It is highly recommended to ensure your range is exact, or add an extra condition to your FILTER formula (e.g., `C2:C20<>""`) to exclude blanks entirely.
