how to count the number of times a word appear in a sentence in a column

[jennxf]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]video not working[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]youtube video[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]hello [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]yes[/TD]
[/TR]
</tbody>[/TABLE]

I have a table where col B is all text. How do i count the number of rows that contain the word "video"?

countif(B1:B4, "*video*") doesnt work.

Thanks,

 

[azumi]

=COUNTIF(B1:B4,"*video*") should work, nothing wrong with formula

 

[JLGWhiz]

countif(B1:B4, "*video*") doesnt work.

Try it like this.

=CountIf(B1:B4, "*"&video&"*")

 

[pgc01]

Hi

Not clear to me.

Can you clarify?

1 - can the word video appear more than once in a cell, like "video 1 is working video 2 is not working"?
What do you want to do in that case?

2 - can the word video appear as part of another word, like videogame, videocard?
What do you want to do in that case?
Could we assume the word separator is always the space character like in your examples, or can there also be others (which others)?

 

[jennxf]

Hi

Not clear to me.

Can you clarify?

1 - can the word video appear more than once in a cell, like "video 1 is working video 2 is not working"?
What do you want to do in that case?

2 - can the word video appear as part of another word, like videogame, videocard?
What do you want to do in that case?
Could we assume the word separator is always the space character like in your examples, or can there also be others (which others)?

sometimes 1 will occur. I will take that cell as 1 count of the word video instead of 2.
video may also be part of another word but in my data set it is very rare. i can ignore it.

 

[Peter_SSs]

countif(B1:B4, "*video*") doesnt work.

When you say it doesn't work, what does it return for that sample data?
I agree with azumi that the formula looks fine, and here it is working for me.

Edit: I suppose one thing could be if you are using a different language version of Excel and it requires a semicolon separator.
=COUNTIF(B1:B4; "*video*")

Excel Workbook
	B	C
1	video not working	2
2	youtube video
3	hello
4	yes
COUNTIF