Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default 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?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default Counting instances of found text (Excel error? Or user error?)

Pardon me!!! A typo:

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****.


Should read *5*, in that the count is INcorrect.
-Sean

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default Counting instances of found text (Excel error? Or user error?)

Ditto to the list :)

Me............................................... ...***4*** -- Should be *5*


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Counting instances of found text (Excel error? Or user error?)

=SUMPRODUCT(--(A1:A100="me"),--(B1:B100="job title"))
change your range to suit, and the criteria


"S Davis" wrote:

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?


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default Counting instances of found text (Excel error? Or user error?)

That's ok, but will give no results unless there is an exact match.
Meaning, it will find no results for "Me", but 4 results for
"Mechanic". I have over 100 seperate job titles, so its a bit tedious.


mama no teeth wrote:
=SUMPRODUCT(--(A1:A100="me"),--(B1:B100="job title"))
change your range to suit, and the criteria


"S Davis" wrote:

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?





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default Counting instances of found text (Excel error? Or user error?)

Just as a follow up, it appears that my named ranges are causing the
problem. By simply typing in the references manually (RBCJobTitle06 is
= 'Ret Best Case Detail'!B5:B154), the results are accurate once again.

This worries me... extremely... as my entire workbook may be
innaccurate, and it's 3 months of work. Ugh. I have 181 named ranges
being used right now, all fairly similar. Man alive this is frustrating.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop excel from dropping the 0 in the beginning of a number? Rosewood Setting up and Configuration of Excel 12 April 4th 23 02:12 PM
Excel Spreadsheet Not Saved Dialog Error KNETTEK Excel Discussion (Misc queries) 0 June 3rd 06 12:14 AM
Enter numeric as text in Excel worksheet should not create error evansight Excel Discussion (Misc queries) 1 May 1st 06 06:00 PM
How paste text from note pad to excel w/o using (Text to column) f nginhong Excel Worksheet Functions 0 April 18th 06 02:07 PM
I Visual Basic Error "File Not Found" when Excel opens Brent E Excel Discussion (Misc queries) 1 March 2nd 05 04:20 AM


All times are GMT +1. The time now is 11:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"