wildcard in countif formula that uses cell references
 

Hi

I have a spreadsheet that contains a column which has several items in
each cell. I have created a column (A) on another sheet (in the same
workbook) that lists those items separately.
Is there any way to have a formula like this, that actually works?:

=COUNTIF('sheet2'!I:I,sheet1!A10)

At the moment, it only returns a non-zero value for those cells which
ONLY contain the item listed in A10. I have tried inserting a wildcard
(*) but it just returns an error.

Thanks!

Terry

wildcard in countif formula that uses cell references
 

Try it like this:

=COUNTIF('sheet2'!I:I,"*"&sheet1!A10&"*")

HTH,
Elkar

"Terry Freedman" wrote:

Hi

I have a spreadsheet that contains a column which has several items in
each cell. I have created a column (A) on another sheet (in the same
workbook) that lists those items separately.
Is there any way to have a formula like this, that actually works?:

=COUNTIF('sheet2'!I:I,sheet1!A10)

At the moment, it only returns a non-zero value for those cells which
ONLY contain the item listed in A10. I have tried inserting a wildcard
(*) but it just returns an error.

Thanks!

Terry

wildcard in countif formula that uses cell references
 

On Nov 2, 5:15 pm, Elkar wrote:
Try it like this:

=COUNTIF('sheet2'!I:I,"*"&sheet1!A10&"*")

HTH,
Elkar

"Terry Freedman" wrote:
Hi

I have a spreadsheet that contains a column which has several items in
each cell. I have created a column (A) on another sheet (in the same
workbook) that lists those items separately.
Is there any way to have a formula like this, that actually works?:

=COUNTIF('sheet2'!I:I,sheet1!A10)

At the moment, it only returns a non-zero value for those cells which
ONLY contain the item listed in A10. I have tried inserting a wildcard
(*) but it just returns an error.

Thanks!

Terry

WOW! That is fantastic!!! Thank u so much. I spent an hour trying to
figure that out!

Terry