Counting occurrences of specific text but only once for each cell

[DavidBW]

hello

I'm using the COUNTIF function to count the number of instances of a particular word in a range of cells. However I only want to count that word once if it appears more than once in a cell. For example

A1 apple
A2 pear
A3 red apples and green apples
A4 banana

Using COUNTIF(A1:A4,"*apple*") to find how many cells contain the word "apple" (doesn't matter if singular or plural) gives the answer of 3 (1 in A1 & 2 in A3) whereas the desired answer is 2. This being derived from A1 & A3 both containing the word "apple" irrespective of how many times it occurs in any given cell.

Thanks in anticipation

[mrice]

Are you sure - it seems to give the answer 2 for me.

[tigeravatar]

DavidBW,

Did you actually try that formula? The result is in fact 2.

[Fotis1991]

Take a look to the example,pls.

[DavidBW]

thank you guys, my original list is so long that I just assumed it would count all occurrences. Would it be appropriate to ask how you can count all occurrences i.e. getting the answer 3 in the original example (or should I open a new thread?)

[tigeravatar]

Please Login or Register  to view this content.

[Fotis1991]

Something like this?

=SUMPRODUCT((LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"apples",""))))/6

[DavidBW]

Fotis,
what does the /6 at the end of your formula do?

[Fotis1991]

Take a look to the example to see with and without "/6"