Explanation
In this example, the goal is to count cells thatcontaina specific substring.
This problem can be solved with the SUMPRODUCT function or the COUNTIF function.
Both approaches are explained below.
The SUMPRODUCT version can also perform a case-sensitive count.
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.
This is because the wildcard automatically causes COUNTIF to look for text only (i.e.
to look for “2” instead of just 2).
Because a text value won’t ever be found in a true number, COUNTIF will return zero.
In addition, COUNTIF isnotcase-sensitive, so you’re able to’t perform a case-sensitive count.
The SUMPRODUCT alternative below can handle both cases.
SUMPRODUCT function
Another way to solve this problem is with theSUMPRODUCT functionandBoolean algebra.
This approach has the benefit of being case-sensitive if needed.
SEARCH returns the position of text in a text string as a number.
errors indicate cells where “a” was not found.
One benefit of this formula is it will find a number inside a numeric value.
SEARCH returns the position offind_textinsidewithin_textas a number.
When the text is not found, FIND returns a #VALUE error.
