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