How to count cells that contain part of a text?

[Rhadida]

I'd like to know if there is a formula or VBA code, that can count cells in a single column, that contain part of a text. Like you can do with an autofilter (contains..).

For example:

Say cell A3 contains the text "The customer is living in England" and so on.
How can I count the number of cells that contain England or Holland or living?

 

[Richard Schollar]

Hi

This is one way:

=SUM(COUNTIF(A:A,{"*England*","*Holland*"}))

Note that the above will count a cell containing both England and Holland twice - is this likely to be a problem?

 

[Rhadida]

Thanks for the very quick reply, didn't know about the {} in the formula's. This will centainly help me out in other spreadsheets! :D

 

[snoopyhr]

or try these:

Excel Workbook
	A	B	C	D	E	F	G
1	List of text
2	The customer lives in London		Word of interest	London	3	CTRL Shift Enter
3	London is a great city to live in				3	CTRL Shift Enter
4	Only London has double decker bus				3	Just Enter
Sheet2

 

[Rhadida]

or try these:

Excel Workbook
	A	B	C	D	E	F	G
1	List of text	*	*	*	*	*	*
2	The customer lives in London	*	Word of interest	London	3	CTRL Shift Enter	*
3	London is a great city to live in	*	*	*	3	CTRL Shift Enter	*
4	Only London has double decker bus	*	*	*	3	Just Enter	*
Sheet2

Thanks for the reply, this will be usefull as well

 

[Rhadida]

Btw do you also know, how I can exclude certain texts from the search?

Like counting all cells without the word "London" or "Holland"?

 

[Rhadida]

Btw do you also know, how I can exclude certain texts from the search?

Like counting all cells without the word "London" or "Holland"?

never mind figured it out

 

[Rhadida]

Hi

This is one way:

=SUM(COUNTIF(A:A,{"*England*","*Holland*"}))

Note that the above will count a cell containing both England and Holland twice - is this likely to be a problem?

This won't be a problem, since the name can only occur once.

btw is it also possible to count the amount cells, that meets 2 requirements in 2 different colomns? For example:

1 column contains the values: Man, Woman, Child and the other column contains the values likes b4, like "living in England'" or "Holland is his birthplace".
I'd like to count the amount of "Man" that also have "England" in the other column?

 

[ZVI]

...is it also possible to count the amount cells, that meets 2 requirements in 2 different colomns? For example:

1 column contains the values: Man, Woman, Child and the other column contains the values likes b4, like "living in England'" or "Holland is his birthplace".
I'd like to count the amount of "Man" that also have "England" in the other column?

Adjust the ranges and try:
=SUMPRODUCT(-(A1:A1000="Man"),-ISNUMBER(SEARCH("England",B1:B1000)))