View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] rtilghman@gmail.com is offline
external usenet poster
 
Posts: 19
Default Testing same cell across multiple sheets for a string and counting each instance?


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