Testing same cell across multiple sheets for a string and counting each instance?

[rtilghman]

Okay, so I've got an interview workbook that does aggregates and
analyzes subject responses across 65 user interviews. Each interview
is a sheet, and each question also has an aggregate "analysis" where I
pull in the responses to specific questions for review. On these
pages I have aggregate sections that count the normalized responses
from each interview so they can be graphed.

The problem is the aggregation routines. For many questions I just
have a single valid response, and have been using a COUNTIF routine to
count all instances of a string across the interview scripts.
However, for other responses I allow multiple options, meaning a
subject could specify "x, y, z" instead of just z.

Below is the current routine I'm using to do the aggregation:

=IF(ISTEXT(A253),SUMPRODUCT(COUNTIF(INDIRECT("'"&$O
$6&""&ROW(INDIRECT("1:65"))&"'!I28"),A253)),"")

What this basically does is check to see if there is a entry in the
legend for this row, and then if so run the SUMPRODUCT routine to add
up all instances of that value across the responses.

What I basically need to do is change the component within the COUNTIF
routine to go in and test the string for instances of the answer
rather than to test the entire cell value. For the life of me I
cannot figure out how to right a script that will do this... I've
tried using FIND inside COUNTIF to test the various responses for the
value, but I can't seem to get it to work.

Below is my best attempt so far, but it fails and gives me a VALUE
entry right near the end.

=IF(ISTEXT(A259),SUMPRODUCT(FIND(A259,INDIRECT("'"&$O
$6&""&ROW(INDIRECT("1:65"))&"'!I28"))),"")

Anyone have any thoughts on how I can get this to work? Any help is
appreciated.

Thanks,
Rick

 

[Joel]

find returns only the poistion where the string is found. thsi only make
sense if the position is the 1st position, otherwise yu would be getting
strange answers. If think you need to put the find function inside an if
statements like

=sum(if(FIND(A259,SearchString)>0,1,0))

Thsi will return a 1 if a the string is found and 0 if it is not found.

 

[rtilghman]

find returns only the poistion where the string is found. thsi only make
sense if the position is the 1st position, otherwise yu would be getting
strange answers. If think you need to put the find function inside an if
statements like

=sum(if(FIND(A259,SearchString)>0,1,0))

Thsi will return a 1 if a the string is found and 0 if it is not found.

Interesting, thanks for the reply. Okay, makes sense, however, how do
I get this routine to cycle through an array of cells rather than just
one? In the original routine the SUMPRODUCT entry gives me an array
of the different cells to reach across. When I remove it the INDIRECT
sequence just returns a reference to the first SHEET/CELL with no
array.

Sorry if this seems basic, I found the SUMPRODUCT routine used by
someone else and basically adapated it, so I don't really know why
SUMPRODUCT allows me to get an array of cells as opposed to just one
from the INDIRECT method.

Thanks,
Rick

 

[T. Valko]

Try using wildcards:

=IF(ISTEXT(A253),SUMPRODUCT(COUNTIF(INDIRECT("'"&$O
$6&ROW(INDIRECT("1:65"))&"'!I28"),"*"&A253&"*")),"")

Biff

 

[rtilghman]

Try using wildcards:

=IF(ISTEXT(A253),SUMPRODUCT(COUNTIF(INDIRECT("'"&$O
$6&ROW(INDIRECT("1:65"))&"'!I28"),"*"&A253&"*")),"")

Biff

Bingo, appears to work like a charm. Thanks a bunch Biff. I'll admit
I don't even understand how the wildcards are working here, but I'm
happy they do.

BTW, regarding Joel's option and my response... had a "duh" moment and
figured out that I hadn't held down CTRL/ALT when I clicked Enter to
make the entry an Array for the SUM approach. I did and was able to
get an array of variables, though they resolve to VALUE! for some
reason that I wasn't able to determine. I think that approach could
probably work too, but I'm afraid I'm not good enough to figure it
out.

Again, thanks for the help, GREATLY appreciated!

Best,
Rick

 

[rtilghman]

Try using wildcards:

=IF(ISTEXT(A253),SUMPRODUCT(COUNTIF(INDIRECT("'"&$O
$6&ROW(INDIRECT("1:65"))&"'!I28"),"*"&A253&"*")),"")

Biff

Okay so one last question. Is there a way to reconcile this wild-card
approach with my original 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
testibg for one entry that matches the option) it comes up with a "0"
even when valid entries are there.... The following are the two
versions and where they work:

single only =IF(ISTEXT(A237),SUMPRODUCT(COUNTIF(INDIRECT("'"&$O
$6&""&ROW(INDIRECT("1:65"))&"'!I27"),A237)),"")
multiple only =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".

The wierdest thing is that the wildcard DOES work in another context
that is "intra-page" in nature (does not test between pages, but runs
across a column in the page... the aggregation is already done to the
page level. Below is the routine that does work.

=IF(ISTEXT(A78),COUNTIF($I$8:$I$73,"*"&A78&"*"),"")

Very strange... any help is appreciated.

Thanks,
Rick