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))

On 9 Mar, 02:41, "T. Valko" wrote:
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" wrote in message

ups.com...



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.

On Mar 8, 8:24 pm, wrote:
On Mar 8, 2:37 pm, "T. Valko" wrote:

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- Hide quoted text -

- Show quoted text -