How many cells match a text string?

[George Marshall]

I have a sheet where I need to display a sum in one cell of a row that
indicates how many other cells in the row contain text matching a given
string. FIND or SEARCH can be used to tell whether any given cell
contains a match, but is there a way, short of programming, to determine
how many cells contain a match?

Thanks,

George Marshall

 

[Andy B]

George

Try:

=COUNTIF(A2:L2,"*text*")

Andy

 

[Paul]

I have a sheet where I need to display a sum in one cell of a row that
indicates how many other cells in the row contain text matching a given
string. FIND or SEARCH can be used to tell whether any given cell
contains a match, but is there a way, short of programming, to determine
how many cells contain a match?

Thanks,

George Marshall

I assume you want to count any cell where the text CONTAINS a particular
string, rather than exactly matching that string. If so, try this array
formula (adjust the range and text to suit):
=SUM(ISNUMBER(FIND("abc",A1:F1))*1)
Array-formulas must be entered using Ctrl+Shift+Enter rather than just
Enter.
Alternatively, you can use
=SUMPRODUCT(ISNUMBER(FIND("abc",A1:F1))*1)
which doesn't need to be array-entered.