Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting instances of found text (Excel error? Or user error?)
Hello,
Im trying to find the number of times certain variations of text are within a list. For instance, I need to count the number of Mechanics, with job titles Mechanic, Mechanic 1st6, Trainee Mechanic, Mechanic Lead, Mech. Eng., Me Appr 1st6, etc. Best I can come up with is this: {=SUM((ISNUMBER(FIND("Me",RBCJobTitle06)))*1)} This should, theoretically grab all of the positions with "Me" in them and count them, and works when I apply it to a list of data. However, when I use it for the list above ('RBCJobTitle06' <-- named range) it returns values that are not there. For instance, that range is 150 cells long, and only contains 5 cells with actual data in them, being: 1) Mechanic 2) Trolley O-head Lineperson 3) Mechanic 4) Mechanic 5) Mechanic Cells 1 - 150 contain the formula: =IF(ISERROR(IF(A5<='Ret Best & Worst'!$B$28,(OFFSET('Supply Data'!$D$2,(MATCH(A5,RBCBestRank2006,0)),0)),"")), "DUPLICATE VALUE",(IF(A5<='Ret Best & Worst'!$B$28,(OFFSET('Supply Data'!$D$2,(MATCH(A5,RBCBestRank2006,0)),0)),""))) .... which is used to return job titles below a certain rank. (I only provide the above just in case the formula is messing things up, but I doubt it.) Now, the problem then is that by using the formula I first spoke of to count the positions with "Me" in them, I get a result of 4. Puzzled, I tried it with other searches for job titles, again using the same list of data containing only those job titles. Here are the results When searching for............ formula returns.... Me................................................ ..4 El................................................ ....0 Ma................................................ ..1 Pai............................................... ...0 Bo................................................ ...3 Par............................................... ...0 We................................................ ..1 Tro............................................... ...2 Tri............................................... .....0 Ti................................................ .....2 So there is obviously something going on here that doesnt make sence. First, there are no combinations of Ti or Bo within the data to be found. Second, of those that are present (Me) & (Tro), the count is incorrect. Lastly, as a final test, even when I delete all data from the entire range, I receive the same results. This is making me crazy Is there any help? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Excel Spreadsheet Not Saved Dialog Error | Excel Discussion (Misc queries) | |||
Enter numeric as text in Excel worksheet should not create error | Excel Discussion (Misc queries) | |||
How paste text from note pad to excel w/o using (Text to column) f | Excel Worksheet Functions | |||
I Visual Basic Error "File Not Found" when Excel opens | Excel Discussion (Misc queries) |