Test for string across multiple cells/sheets... further questions

[rtilghman]

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

 

[T. Valko]

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

 

[rtilghman]

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

Thanks for the reply.

PROBLEM 1
I just rechecked and yes, mistake was mine, it seems the cell
reference for the test wasn't updating and I was checking for terms
where they wouldn't possibly exist. Multiple versions works
fantastically.

PROBLEM 2
ARGH, not the response I was looking for. I have been struggling with
this for about 3.5 hours now to no avail. There seems to be a
workable solution involving length that I found "(total length of
cells in a range) - (total length of cells substituting nothing for a
specific term)/(term length). However, I can't get it to work in
three dimensions (multiple sheets).

There HAS to be a way to do this... my eternal gratitude to the person
that can figure it out!


-rt

 

[T. Valko]

However, I can't get it to work in

three dimensions (multiple sheets).

Therein lies the problem!

There are very few functions that work on 3D references.

Biff

 

[Lori]

Try replacing A253 by REPT(A253&"*",{1,2,3,4,5,6,7,8,9,10})) in your
countif formula to count up to 10 repitions of a non-blank search
string. Use e.g. transpose(indirect(row(1:99))) to extend to more
rows.

 

[T. Valko]

Very nice!

You might need to use an additional wildcard:

REPT("*"&A253&"*",..........

Also, if one uses:

transpose(indirect(row(1:99)))

That makes the formula an array.

Biff

 

[Lori]

Should work anyway as there's "*"&... in front so evaluates to
{"*xyz*","*xyz*xyz*",...}. There was a typo however should have been:
transpose(row(indirect("1:99"))) or column(indirect("c1:c99",0))