Counting "*" and "?" as characters in Excel's COUNTIF: How to use tildes and potential pitfalls

About this article
This article was created using an automated generation workflow utilizing generative AI. Based on Microsoft's official documentation for the COUNTIF function, we have structured the mechanics of wildcards and escaping so they can be safely tested using a fictional table.

Verification status: Constructed based on official specifications. Execution and UI display on actual Excel software have not been verified.Display and operation may vary depending on the version, regional settings, and input format.

Wanting to count only "products with an asterisk" but ending up counting all of them

Suppose you are using a product management sheet where only special product names have an asterisk, such as "Standard", "Special*", and "Prototype?". If you try to use COUNTIF to count "rows containing an asterisk", you may get a larger count than expected. This happens because Excel's criteria interpret the asterisk not as a regular character, but as a symbol representing "any string of characters".

For example, if you specify just an asterisk as a criterion, not only cells with an asterisk, but various other strings will match. Even a small difference visually can result in a large difference in the aggregation results. To understand this, you need to distinguish between an asterisk as a character and an asterisk as a search symbol. Create a practice table without modifying values, deliberately make a broad match first, and then observe how adding a "~" changes the meaning. There is no need to edit live data directly. Note that this deals specifically with COUNTIF criteria expressions and does not follow the same rules as Excel-wide regular expressions or file name searches.

Creating practice data

Type "Product Name" in cell A1 of a blank worksheet, and sequentially enter "Standard", "Special", "Prototype?", "Stock~", "", and "No Space" from A2 to A7. Be sure to use single-byte (half-width) characters for asterisks, question marks, and tildes. Full-width "＊" and "？" will be treated as different characters.

Place the criteria formulas in another column, such as column C. Initially target the 6 rows from A2:A7, and update the range if rows are added. Since business sheets containing a mix of blank cells and numbers can alter result interpretation, start by testing with only the strings mentioned above.

The numerical values in the table are illustrativeexpected results and are not values verified by launching Excel at the time of creation. A condition consisting solely of "" acts as a wildcard representing any entire string. On the other hand, "~" matches a literal asterisk character. This is the first discovery.

The first three formulas to try

First, enter the following formula into C2.

=COUNTIF(A2:A7,"*")

Observe the phenomenon where you intend to search for products with an asterisk, but regular items are also counted. The asterisk in this condition is not a mark to check for the presence of a character, but a wildcard that matches any string. Since all practice tables consist of strings, you can expect the count to be 6. However, if a general table contains blanks, numbers, or formulas, it does not necessarily match the total number of rows.

Next, enter the following formula into C3.

=COUNTIF(A2:A7,"~*")

This formula searches for cases where the entire cell consists of a single asterisk. In this dataset, that applies only to row A6. Prepending a tilde allows the following asterisk to be treated as a literal character rather than a wildcard. Since "Special*" is not a single asterisk across the entire string, it does not match here.

Next, enter the following formula into C4.

=COUNTIF(A2:A7,"*~*")

The asterisk on the left represents an arbitrary prefix string, and the '~' on the right represents a literal asterisk.Therefore, 'Special Offer' and '*' are the candidate matches.Comparing this with the previous expression highlights the difference between 'exact match' and 'endswith match'. When you want to expand your search targets, the placement of the wildcard is crucial.

Visualizing 'Contains', 'Ends With', and 'Exact Match'

Even with the same asterisk, its role differs depending on the condition string. The following diagram is not a reproduction of Excel's internal implementation, but a conceptual diagram to help you understand search criteria.

flowchart TD
    A["COUNTIFの条件を読む"] --> B{"星印の前にチルダがある?"}
    B -->|はい| C["文字としての星印を照合"]
    B -->|いいえ| D["任意の文字列として照合"]
    C --> E["前後のワイルドカードで位置を指定"]
    D --> E
    E --> F["セルごとの一致数を合計"]

The key takeaway on the left side of the diagram is that the tilde toggles the meaning of the asterisk. The 'wildcards before and after' in the second half are used to determine the position you want to match. Because the entire condition string is interpreted in practice, please do not use this diagram as a performance explanation implying that Excel always processes them in this exact order.

To count cells that 'contain' a literal asterisk anywhere, you allow arbitrary strings before and after the asterisk. Specifically, it is '~'.The asterisks at the left and right ends are wildcards, and only the '~' in the middle represents a literal asterisk. Even if they appear as consecutive symbols, each has a different role. When reading such expressions, breaking them down into three parts—'arbitrary first half', 'real asterisk', and 'arbitrary second half'—prevents confusion.

Investigating Question Marks and Tildes Using the Same Concept

In COUNTIF criteria, a question mark is normally a wildcard that matches any single character. Setting just '?' as the condition does not search for a single literal question mark. To search for the question mark itself, you use '~?'.~?For example, '

' becomes a condition to search for strings containing a question mark. In the practice table, 'Prototype?' is a candidate.~~Even more confusing is the tilde itself. When you want to search for the character that has the role of escaping wildcards as a literal character, you double it as '~~'.

'

' represents a form that allows arbitrary strings before and after while searching for a literal tilde in the center. In the practice table, 'Inventory~' is assumed to apply here.

From the initial '~', try adding another asterisk at the end to change it to '~'.' is the condition ending with an asterisk.~does not match the added row, whereas the condition containing the asterisk "*~" does.

This comparison is an experiment to understand what changes when you add conditions. Rather than simply memorizing two formulas, changing just one data point makes the difference between "ends with" and "contains" much clearer. When creating search conditions for business operations, adding an example that should not match, in addition to the expected matching examples, makes it easier to notice miscalculations.

Furthermore, if you forget to update the range after adding A8, neither formula will evaluate the added row. This is not an error in the search condition, but an omission in specifying the aggregation range. The habit of checking the conditional expression and the data range separately is important.

Troubleshooting when counts are incorrect

First, check for half-width and full-width characters. Full-width asterisks are different from half-width wildcards. If copied characters only look similar, they will not match even if the formula is created correctly. Compare the characters in the input field directly and, if necessary, re-enter the half-width characters in a new practice sheet.

Next, check whether the condition requires an exact match for the entire cell or a partial match. "~" expects an exact match for a single asterisk, whereas "~*" is a string containing an asterisk. If you want to count "Special Offer" but use "~*", the cause is not the misuse of the escape character, but a lack of wildcards before and after.

The third factor is the range and cell data type. COUNTIF may yield unexpected results when numbers, blanks, formula results, and text are mixed. For example, some cells may look blank but actually contain spaces. Isolate whether spaces are mixed in at the beginning or end of the text, or whether the value is returned by a formula. When the counts differ, filtering the target column and visually checking the candidate items makes it easier to trace the cause.

The fourth factor is Excel's argument separator. Depending on regional settings, some environments use semicolons instead of commas in formulas. If pasting the formula results in a syntax error immediately, check Excel's regional settings and function input assistance before questioning the meaning of the condition. Since function name localization also varies by environment, it cannot be guaranteed that the formulas in this article will work identically in all environments.

How much should be delegated to COUNTIF in practical work?

If product names or application numbers simply contain symbols, COUNTIF wildcards and tildes may suffice. If you can maintain a separate classification code column, conditioning on the classification code is more meaningful than determining business "special offers" based solely on asterisks contained in the display name. If asterisks are merely decorative or inconsistently omitted by operators, tweaking the search formula will not resolve data quality issues.

Moreover, COUNTIF wildcards are not standard regular expressions. Trying to force complex rules, such as "any 3-digit number" or "match only immediately after a specific symbol," into a single conditional expression makes maintenance difficult. If necessary, consider creating auxiliary columns to visualize string inspection results as TRUE/FALSE and aggregating those results.

Before applying aggregations to production management sheets, prepare "matching examples," "non-matching examples," and "boundary examples" in a copied sheet. It is important not to overwrite the original data and to retain the formula results in a verifiable format. Especially in sales and inventory aggregation, conditional expressions returning incorrect counts without throwing errors are harder to detect.

Summary

In search conditions, asterisks in COUNTIF generally represent any string of characters. To search for an asterisk as a literal character, use "~*"; for a question mark, use "~?"; and for a tilde, use "~~". Furthermore, by placing wildcards before and after, you can distinguish between exact matches, suffix matches, and partial matches.

In this small practice table, the key takeaway is that "despite being the same asterisk, a single tilde changes the meaning of the aggregation." If the expected count differs, check the half-width/full-width status of symbols, the placement of conditions, the aggregation range, and the data types in order. Beyond memorizing correct formulas, confirming that unintended rows do not match ensures you can use them with confidence in daily aggregations.

Official Documentation

• Microsoft Support: COUNTIF function— Primary source for verifying the handling of wildcards and tildes.