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.

Screenshot of spreadsheetFruit.xlsx
LVL 1
Alex CampbellAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
=COUNTIF(A2:A9,"*"&B1&"*")
should do it.
0
 
Patrick MatthewsCommented:
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
 
Alex CampbellAuthor Commented:
Great, thanks, very impressive
0
All Courses

From novice to tech pro — start learning today.