Ignoring Special Characters in COUNTIF

Peter has a worksheet where column A contains semi-random text strings. In cell D1 he enters a string that he wants to search for in column A. In another cell he uses the formula =COUNTIF(A:A,D1) to determine how many times a string occurs within column A. However, a string in column A may include what COUNTIF considers logical operators (>,<,<>,=) or wildcards (*,?). This can throw off what Peter expects COUNTIF to return. For instance, if column A contains both "abc123def" and "abc*def" and he places the latter string in cell D1, then COUNTIF returns 2 because of the asterisk. Peter wonders if there is a way he can force COUNTIF to not pay attention to logical operators or wildcards so that his desired count of text strings is correct.

To explain what is going on, let's start by coming up with a det of data that can be used to demonstrate the behavior that Peter is seeing. (See Figure 1.)

Figure 1. Sample data for evaluation.

Note that the value in cell D1 really does return (in cell D5) the value 2, even though "abc*def" appears only once in the sample data. This occurs because what is being searched for includes a wildcard character, the asterisk.

Remember that Peter wants matches to characters, not anything that takes wildcards into account. This can be handled by modifying, slightly, what you search for in cell D1. Change the search text to "abc~*def" and you'll get a result of 1 in cell D5, as expected. That is because the tilde character (~) is treated by Word as an "escape character," meaning that the wildcard character immediately following it should be treated as a literal instead of as a wildcard.

This can present issues, however. Notice the entry that is at cell A12. If you place this exact text into cell D1, the formula at D5 reports that there were 0 matches in column A. Why? Because, again, the tilde character is viewed as an escape character that only affects the wildcard that follows it. In other words, the COUNTIF function ends up searching for "{*,M'"m" because the tilde indicates that you want an actual asterisk.

To do a real match of such a string, you would need to add the escape character to both the tilde and to the asterisk, which means you would end up placing "{~~~*,M'"m" into cell D1. This places a tilde before the original tilde and another before the asterisk.

Adding the tilde to wildcards can work, but it doesn't work in all the situations that Peter is experiencing. Notice that cell A6 contains "<234". Place that into cell D1, and the formula at D5 returns 2 matches. These two are actually the contents of cells A13 and A14, both of which are less than 234 (<234). What Peter would want is a result of 1, matching the contents of cell A6.

In this situation, adding a tilde to the search value in D1 won't work. The tilde only works with wildcard characters, not with comparison characters. There is no special character you can add in such a case to "escape" the comparison operator.

The best solution for Peter is not to use COUNTIF to get the count. You can use the SUM function, instead, in this manner:

=SUM(--(A:A=D1))

The (A:A=D1) portion of the formula returns an array of TRUE or FALSE values for each cell in column A. Each element in the array corresponds to a cell in column A, with the element being TRUE if the cell is equal to what is in D1 and FALSE if it is not. The -- (double negative) portion converts the TRUE/FALSE values to either 1 (TRUE) or 0 (FALSE), and the SUM function adds all the 1/0 values together. What you end up with is a count of cells where the cell contains a value equal to D1.

Using this approach is case insensitive (as is the COUNTIF approach), but if you want it to pay attention to case, then you could modify the formula very slightly:

=SUM(--(EXACT(A:A,D1)))

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13948) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.