First problem:

Assume:

A253 = this

Sheet1 I28 = this
Sheet2 I28 = is this
Sheet3 I28 = is this all

The wildcard version result = 3 which is correct.

Second problem: Good luck with that one! I would suggest that you put one
value per cell instead of x, x, z all in one cell. Then you could just
expand the range in the Countif:

......&"'!I28:N28"),.....

Biff

wrote in message
oups.com...
Okay so for those that don't know I had a problem testing cell strings
across different sheets of the same workbook... basically I needed to
go beyond COUNTIF into cell strings. I got a solution using wild-
cards (thanks) that worked in at least a limited capacity, but I have
a couple of remaining issues I'm hoping I can get some help with.

FIRST PROBLEM
Is there a way to reconcile this wild-card approach with my original
single cell approach? What I'm finding is that if I put in the wild-
card version in places where I don't have multiple entries to test for
(say spots where I'm testing for one entry that matches the option) it
comes up with a "0" even when valid entries are there.... The
following are the versions I'd like to reconcile:

original (single) =IF(ISTEXT(A237),SUMPRODUCT(COUNTIF(INDIRECT("'"&$ O
$6&""&ROW(INDIRECT("1:65"))&"'!I27"),A237)),"")
modified (multiple) =IF(ISTEXT(A253),SUMPRODUCT(COUNTIF(INDIRECT("'"&
$O
$6&ROW(INDIRECT("1:65"))&"'!I28"),"*"&A253&"*"))," ")

To clarify, single would be a cell with "x" where you are testing for
"x". Multiple would be a cell with "x, y, c" where you are testing
for "y".

SECOND PROBLEM
Also, I actually kind of need the ability to count all instances of a
string within the cells, not just to see if any instance of a string
appears in a cell. The modified approach (multiple above) seems to
check if the string is anywhere in the cell, but it doesn't count all
of the instances... say I have a cell with "x, x, x" in it, that would
count as "1" and not "3". I need it to count each instance.

Thanks in advance for any help, really appreciate it.

Thanks,
Rick