Explanation
In this example, the goal is to count cells thatdo not containa specific substring.
This problem can be solved with theCOUNTIF functionor theSUMPRODUCT function.
Both approaches are explained below.
Although COUNTIF isnotcase-sensitive, the SUMPRODUCT version of the formula can be adapted to perform a case-sensitive count.
For convenience,datais thenamed rangeB5:B15.
COUNTIF function
TheCOUNTIF functioncounts cells in a range that meet supplied criteria.
To be included in the count, a cell must contain “apple” and only “apple”.
If a cell contains any other characters, it will not be counted.
To do this, we need to use the asterisk (*) character as awildcard.
The COUNTIF function supports three different wildcards,see this pagefor more details.
Note the COUNTIF formula above won’t work if you are targeting a particular number and cells containnumericdata.
This is because the wildcard automatically causes COUNTIF to look for text only (i.e.
to look for “2” instead of just 2).
In addition, COUNTIF isnotcase-sensitive, so you’ve got the option to’t perform a case-sensitive count.
The SUMPRODUCT alternative explained below can handle both situations.
This approach has the benefit of being case-sensitive if needed.
To count cells that contain specific text with SUMPRODUCT, you’re able to use theSEARCH function.
SEARCH returns the position of text in a text string as a number.
error:
Notice we do not need to use any wildcards because SEARCH will automatically find substrings.
If we get a number from SEARCH, we know the substringwasfound.
If we get an error, we know the substringwas notfound.
errors indicate cells where “a” was not found.
One benefit of this formula is it will find a number inside a numeric value.
Notice we have replaced “a” with “A” because FINDiscase-sensitive.
The syntax used by COUNTIF is unique toa group of eight functionsand is therefore not as useful or portable.
SEARCH returns the position offind_textinsidewithin_textas a number.
When the text is not found, FIND returns a #VALUE error.
NOT Function
The Excel NOT function returns the opposite of a given logical or Boolean value.
When given TRUE, NOT returns FALSE.
When given FALSE, NOT returns TRUE.
Use the NOT function to reverse a logical value.
