How to Count Excel Values Starting with 50 Using Wildcards
Question details
The user needs to count cells containing values that begin with "50", but the wildcard formula is failing and counting literal asterisks instead.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing a counting function (like COUNTIF) using an asterisk wildcard to match values starting with a specific number pattern.
- Observed behavior
- Excel counts the literal asterisk symbol instead of treating it as a wildcard, typically because the target cells contain numeric data instead of text.
Verify whether the column you are trying to count contains numbers formatted as text or actual numeric values. In Excel, wildcards like the asterisk (*) only function correctly when applied to text criteria.
Use COUNTIF with Wildcards for Text Values
Use the standard COUNTIF formula with an asterisk wildcard when your target data is formatted as text.
The asterisk (*) is a wildcard that matches any sequence of characters, but it only works when used with text criteria. If your cells are already stored as text, this is the most straightforward method.
Click on an empty cell where you want the total count to be displayed.
Type =COUNTIF(C:C, "50*") into the formula bar, replacing 'C:C' with your actual target range.
Press Enter to execute the formula. Excel will count all text strings in the range that begin with '50'.
Use COUNTIFS for Numeric Values
If your values are stored as numbers, wildcards will not work. Instead, use COUNTIFS to count within a specific numeric range.
Use REGEXTEST for Advanced Matching
In newer Excel versions that support regular expressions, use the REGEXTEST function to identify starting characters.
Easily Count and Analyze Data with WPS Spreadsheet
WPS Spreadsheet offers full support for wildcards and advanced statistical functions like COUNTIF and COUNTIFS. You can effortlessly count specific text patterns or numeric ranges using the exact same formulas as Microsoft Excel, completely free of charge.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dataset.
- 2. Apply the COUNTIF formula: Click an empty cell and type =COUNTIF(A1:A100, "50*") to count text starting with 50.
- 3. View your results: Press Enter to instantly view your calculation results with perfect Excel formula compatibility.
Frequently Asked Questions
Why does my COUNTIF asterisk wildcard return zero or count actual asterisks?
Asterisks only function as wildcards when applied to text strings. If your target cells contain numeric values, Excel cannot process the wildcard and instead searches for the literal asterisk character, often resulting in a count of zero.
How do I convert my numbers to text to make the wildcard work?
Select your column of numbers, go to the 'Data' tab, click 'Text to Columns', click 'Next' twice, and select 'Text' under Column data format. Once converted to text, your COUNTIF wildcard formula will work normally.
Can I use other wildcards with the COUNTIF function?
Yes. In addition to the asterisk (*) which matches any number of characters, you can use a question mark (?) to match any single character. If you need to find an actual literal question mark or asterisk, place a tilde (~) before it in your formula.
