# Can COUNTIFS be used for text within a cell?

I want to be able to put a word in B1
and have countifs formula in C1 that will tell me how many times
the word in B1 appeared in the list A2:A9.

Fruit.xlsx
LVL 1
###### Who is Participating?

x

Commented:
=COUNTIF(A2:A9,"*"&B1&"*")
should do it.
0

Commented:
If you are trying to count the number of cells containing the text in B1, Rory's formula will work perfectly.  If you are counting the actual number of instances of that text (for example, counting "apple" twice if that word happens to appear twice in one of the cells), you need something a little more complex:

{=SUM(LEN(A2:A9)-LEN(SUBSTITUTE(A2:A9,B1,"")))/LEN(B1)}

That's an array formula, so enter it without the curly braces, and use Ctrl+Shift+Enter instead of just Enter to finish it off.  Excel will then display the formula with the braces.
0

Author Commented:
Great, thanks, very impressive
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.