COUNTIF Function

[Leon]

Help! I'm trying to use countif similar to:

=COUNTIF(A2:A9,"*Smith*") However, I want the "SMITH" to
be a cell reference.

If Cell A10 has a cell value of "Smith" and individual
cells from range A2:A9 contains multiple names [Smith,
Jones] or [Johnson, Smith], then I want a COUNTIF
statement:
=COUNTIF(A2:A9,"*A10*").

Any ideas?
Right now, the work around is do hard-code the COUNTIF
statement with "Smith", but I want to be able to change
the value of "Smith" without having to update the COUNTIFs
everytime.

Thanks.

 

[Alan Beban]

Help! I'm trying to use countif similar to:

=COUNTIF(A2:A9,"*Smith*") However, I want the "SMITH" to
be a cell reference.

If Cell A10 has a cell value of "Smith" and individual
cells from range A2:A9 contains multiple names [Smith,
Jones] or [Johnson, Smith], then I want a COUNTIF
statement:
=COUNTIF(A2:A9,"*A10*").

Any ideas?
Right now, the work around is do hard-code the COUNTIF
statement with "Smith", but I want to be able to change
the value of "Smith" without having to update the COUNTIFs
everytime.

Thanks.

=COUNTIF(A2:A9,"="&"*"&A10&"*")

Alan Beban

 

[JulieD]

Hi Leon

try
=COUNTIF(A2:A9,"*"&A10&"*")

Cheers
JulieD

 

[Leon]

Thanks Alan and Julie, that fixed my problem!
Leon

 

[JulieD]

Hi Leon

glad its solved - thanks for the feedback.

Cheers
JulieD

Thanks Alan and Julie, that fixed my problem!
Leon

-----Original Message-----
Help! I'm trying to use countif similar to:

=COUNTIF(A2:A9,"*Smith*") However, I want the "SMITH" to
be a cell reference.

If Cell A10 has a cell value of "Smith" and individual
cells from range A2:A9 contains multiple names [Smith,
Jones] or [Johnson, Smith], then I want a COUNTIF
statement:
=COUNTIF(A2:A9,"*A10*").

Any ideas?
Right now, the work around is do hard-code the COUNTIF
statement with "Smith", but I want to be able to change
the value of "Smith" without having to update the COUNTIFs
everytime.

Thanks.
.