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

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

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

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

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

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

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

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

wrote in message
ps.com...
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

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

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

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

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

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

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 -