Counting a word in a column when cells have multiple values...

[webster33]

I am trying to figure out the best way to COUNT how many times a word appears within a column. However, the column has several cells where there are multiple values separated by semicolons.

As an example, what would be the best way to write the formula to count the word "alpha" from this sample data set?

Thank you!

 

[Cubist]

Try:

Book1
	A	B	C
1	a		e
2	a; b		0
3	a
4	a
5	a; b ; c
6	a; b; c; d
Sheet6

Cell Formulas
Range	Formula
C2	C2	=LET(list,TRIM(TEXTSPLIT(TEXTJOIN(";",,A1:A6),,";")),IFERROR(ROWS(FILTER(list,list=C1)),0))

 

[kevin9999]

Or Countif?

Book1
	A	B	C
1	alpha		6
2	alpha; bravo
3	alpha
4	alpha
5	alpha; bravo; charlie
6	alpha; bravo; charlie; delta
7
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(A1:A6,"*alpha*")

 

[Cubist]

One draw back with COUNTIF is if the words are repeated in the same cell, they won't get counted.

 

[webster33]

Or Countif?

Book1
	A	B	C
1	alpha		6
2	alpha; bravo
3	alpha
4	alpha
5	alpha; bravo; charlie
6	alpha; bravo; charlie; delta
7
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(A1:A6,"*alpha*")

Thanks @kevin9999! That did the trick by adding the * on each side of the desired lookup value. Much appreciated!

 

[kevin9999]

Thanks @kevin9999! That did the trick by adding the * on each side of the desired lookup value. Much appreciated!

Happy to help, and welcome to the forum.