The simplest method is just to use COUNTIFS
:
=COUNTIFS(A1:A11, "*APPLE*", A1:A11, "*ORANGE*", A1:A11, "*GRAPES*")
The asterisks are necessary to search for the text anywhere within the cell.
Alternatively, and because I already typed it out, you could also use an array formula like so:
=SUM(IF(ISERROR(SEARCH("APPLE", A:A) & SEARCH("ORANGE", A:A) & SEARCH("GRAPES", A:A)), 0, 1))
To enter an array formula, hold CTRL
and SHIFT
while pressing ENTER
.
The SEARCH
function finds text within another text and gives an error when the text is not found. This function simply concatenates three searches together; if any one of them results in an error, then the concatenation results in an error. In which case, the ISERROR function will return true.
The IF
function then returns a 0
when the error happens, and a 1
when an error does not happen.
By using SUM
with an array formula, the function is applied to every cell in the column A:A
since that is what was specified, and then sums the results together.
Apple
vsPineapple
? If you do, will making the search case sensitive do the trick? Or do you need something more sophisticated?