Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Testing same cell across multiple sheets for a string and counting
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. " wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Testing same cell across multiple sheets for a string and counting
On Mar 7, 11:20 pm, Joel wrote:
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Testing same cell across multiple sheets for a string and counting each instance?
Try using wildcards:
=IF(ISTEXT(A253),SUMPRODUCT(COUNTIF(INDIRECT("'"&$ O $6&ROW(INDIRECT("1:65"))&"'!I28"),"*"&A253&"*"))," ") Biff wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Testing same cell across multiple sheets for a string and counting each instance?
On Mar 7, 11:44 pm, "T. Valko" wrote:
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Testing same cell across multiple sheets for a string and counting each instance?
On Mar 7, 11:44 pm, "T. Valko" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting the number of instances of a string within another string | Excel Worksheet Functions | |||
Testing for a consistent value counting blanks | Excel Worksheet Functions | |||
Counting text across multiple sheets with a specific criterion | Excel Worksheet Functions | |||
Counting multiple occurances of a specific string | Excel Worksheet Functions | |||
Find nth instance of a character in a string | Excel Discussion (Misc queries) |