1

I have this excel file containing multiple rows. I wanted to convert the following expression to an excel formula but can't find a reference on how to:

COUNTIF(CELL CONTAINS "APPLE" AND CELL CONTAINS "ORANGE" AND CELL CONTAINS "GRAPES")

The formula should count all the cells with all those words present in it.

In the example below, the result of the count must be 2 since there are 2 cells which contain all three words.

enter image description here

How will I do that?

1
  • Do you need to differentiate, for example Apple vs Pineapple? If you do, will making the search case sensitive do the trick? Or do you need something more sophisticated? Dec 10, 2015 at 11:55

1 Answer 1

4

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.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .