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?

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

Counting instances of found text (Excel error? Or user error?)
 

Ditto to the list :)

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

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?

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?

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.