For convenience, the range B5:B15 isnameddata.
Note: The main formula described on this page is case-sensitive because theFIND functionis case-sensitive.
If you want a formula that is not case-sensitive, you might substitute theSEARCH functionfor the FIND function.
SUMPRODUCT formula
One way to solve this problem is to use theSUMPRODUCT functionwithISNUMBER+FIND.
The formula in E5 is:
This formula is based on aformula explained herethat finds text in a cell.
If the text is not found, FIND returns a #VALUE!
Next, we need to add these numbers up, but we don’t want to double count.
Note: this formula is an example ofusing Boolean algebrato apply “OR logic” in a formula.
Helper column solution
Another way to solve this problem is with ahelper column.
This breaks up a more complex problem into parts.
The asterisk (*) is awildcardwe can use for a contains search.
When the text is not found, FIND returns a #VALUE error.
COUNTIF can be used to count…
