wildcard in countif formula that uses cell references

[Terry Freedman]

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

 

[Guest]

Try it like this:

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

HTH,
Elkar

 

[Terry Freedman]

Try it like this:

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

HTH,
Elkar

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

Terry

 

[Hassan Arsjad]

Hi Terry,

I have similar issue and my formula is like this

=COUNTIF(Issued.xlsx!$AF$2:$AF$100000, "=" &A3)

$AF$2:$AF$100000 are like this 31/08/2016 11:27:23 and &A3 31/08/2016

Any solutions please

Thanks