Countif containing text but not including other specific texts

[camajto]

Hi Guys,

Bit hard to explain what im trying to achieve so i created a simple version. I would like to count how many times the animal "horse" appears in each cell **BUT** if there are multiple animals then i want to exclude the cell.

E.g. Cell a1 to a7 as follows and D1 = horse

black dog end
black cat end
black horse end
black horse cat end
black horse dog end
black horse cat dog end
black horse end

the answer should be 2 however if you do =COUNTIF($A$1:$A$7,"*"&D1&"*") you get 5

please note the real data can be 200 characters in length and words vary in length and position so LEFT and RIGHT formulas wouldnt work.

Thanks
Tom

[Khalidngo]

Hi Tom,

You have "end" too in your string, so your formula can be amended like:
=COUNTIF($A$1:$A$7,"*"&D1&" end"&"*")

Blessing

[camajto]

Hi Khalidngo,

Thanks for the suggestion. The cells themselves are unfortunately not that simple. End was used as an example in this demo.

The real data has 5-50 words and there is no common start or end phrases.

All i have is a list of 10 words and i would like to count 1 word and not count the 9 other words if they appear in the same cell.

Thanks

[FlameRetired]

Withdrawn by FR.

[cbatrody]

Can you upload a sample workbook with a list of those 10 words which include 9 words that should not be counted. See the following URL for help on how to upload a file.

http://www.excelforum.com/members/da...ch-a-file.html

[camajto]

Thanks Dave,

I copied and pasted cell A6 to A7 to test if the answer would change to 1, instead it jumped to 4.

:S

[bebo021999]

I use a helper column to display criteria.
See attachment.

[Aladin Akyurek]

[...]the answer should be 2
[...]

Row\Col	A	D	F
1	black dog end	horse	cat
2	black cat end	2	dog
3	black horse end		horse
4	black horse cat end		lion
5	black horse dog end		tiger
6	black horse cat dog end		jaguar
7	black horse end		bear

F1:F7 is defined as AnimalList, i.e., AnimalList refers to F1:F7.

In D2 control+shift+enter, not just enter:

Please Login or Register  to view this content.

[FlameRetired]

Thanks Dave,

I copied and pasted cell A6 to A7 to test if the answer would change to 1, instead it jumped to 4.

:S

Yes. It appears I didn't withdraw in time.