How to Categorize and Count Similar Text in Excel Using Formulas
Question details
The user needs to categorize support ticket titles by keywords and count similar topics automatically without relying on VBA macros.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Grouping and categorizing large lists of text strings (like support tickets) based on specific keyword criteria and dynamically counting their occurrences.
- Observed behavior
- The goal is to automatically classify text and calculate matching items dynamically, while handling advanced challenges like exact whole-word matching and adding conditional date filters.
Ensure your text data is organized consistently in a single column without blank rows, and create a separate reference list of the keywords you want to group and count.
Use COUNTIFS with Wildcards for Simple Keyword Counting
Best for categorizing simple support ticket keywords placed in a separate reference list.
The COUNTIFS function, combined with asterisk (*) wildcards, allows you to search for a specific substring within a larger text string. This is the most efficient way to group similar topics without complex coding.
Type your desired category keywords (e.g., 'password', 'login') in a dedicated column, such as Column D.
In the adjacent cell (E2), type the formula: =COUNTIFS($B$2:$B$10, "*"&D2&"*"), assuming your ticket titles are in column B.
Press Enter, then drag the fill handle down to apply the formula and calculate counts for all keywords in your list.
Combine COUNTIFS with Multiple Conditions (e.g., Date Filters)
Use this method when you need to count keywords only if they meet additional criteria, such as falling within a specific month.
Apply LET and TEXTSPLIT for Complex Multi-Term Categorization
Ideal for searching each title against multiple terms simultaneously and extracting categorized text arrays.
Categorize and Count Text Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced text functions, dynamic arrays, and wildcard formulas like COUNTIFS to help you organize support tickets and group similar text efficiently.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your support tickets.
- 2. Select a calculation cell: Click on an empty cell next to your predefined keyword list.
- 3. Apply the wildcard formula: Type =COUNTIFS($B$2:$B$10, "*"&D2&"*") and press Enter.
- 4. Fill the series: Double-click the fill handle in the bottom-right corner of the cell to categorize all remaining keywords instantly.
Frequently Asked Questions
How do I ensure my formula only matches exact whole words?
Standard wildcard matches (like "*topic*") will flag any word containing that sequence (e.g., 'topical' or 'atopic'). To match exact words using formulas, you can add spaces around your search terms (e.g., "* topic *"), though this requires your raw data to have consistent spacing. For strict whole-word logic, using Power Query or VBA regular expressions is recommended.
Can I use a formula to extract and list the exact rows that match the keyword?
Yes, you can use the FILTER function to extract the matching records. Use a formula like =FILTER($A$2:$C$10, ISNUMBER(SEARCH(D2, $B$2:$B$10))) to output all columns for the support tickets that contain the keyword found in cell D2.
Why is my COUNTIFS formula returning 0 even though the text exists?
This usually happens if the wildcards are not concatenated correctly with the cell reference. Ensure your syntax exactly matches "*"&CellRef&"*" with the ampersands outside the quotation marks, so Excel treats the asterisk as a text string attached to your specific keyword.
