COUNTIF for presence of string in parenthesis while excluding non-parenthetical presence

VinceInVA

New Member
Joined
Apr 12, 2013
Messages
6
Humble gratitude is offered to overlook my ignorance here.
I want to COUNT for the the presence of the "RA" in the parenthesis while not including the ra in Sarah's name.
The RA I want to count will always be Capitalized and in parenthesis.
EXAMPLE

Connor, John (8675309) Wilson (RA, FSET, HBBQ)
Connor, Sarah (123456) Wilson (RA, FlET, FSFB)

<tbody>
</tbody>

Thank you,
Vince
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome to the board.

Will the RA always follow a ( ?

Perhaps
=COUNTIF(A1:A100,"*(RA*")
 
Upvote 0
Hi and welcome to Mr Excel Forum

If you want to count how many rows in a range (assumed A2:A100) contain "(RA" maybe

=SUMPRODUCT(--(ISNUMBER(FIND("(RA",A2:A100))))

M.
 
Upvote 0
I should have known it was too good to be true JonMo1...

What I have here is a schedule for testing and the data consists of
StudentLastName, First (stuIDNO) TeacherLastName (Acronmyms for accommodations)
Your function worked great until I ran into a teacher named Branson with ra technically within the outer parentheses like this:

Connor, Sarah (1234567) Branson (RD, RA, FSFB)

So this does not count the ra in Sarah but does count the ra in Branson (and of course the RA in the acronyms)

Any further suggestions are appreciated.
 
Upvote 0
The formula is only counting the RA in parentheses. That string will return 1 to the count because the RA is in parentheses. If you take the ra out of the names, it will still return 1 because of the RA in parentheses, which is what you said you wanted to count.

Excel Workbook
ABC
1Connor, Sah (1234567) Bnson (RD, RA, FSFB)*1
Sheet1


Excel Workbook
ABC
1Connor, Sarah (1234567) Branson (RD, RA, FSFB)1
Sheet1
 
Upvote 0
The formula is only counting the RA in parentheses. That string will return 1 to the count because the RA is in parentheses. If you take the ra out of the names, it will still return 1 because of the RA in parentheses, which is what you said you wanted to count.

Sheet1

*ABC
1Connor, Sah (1234567) Bnson (RD, RA, FSFB)*1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:305px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C1=COUNTIF(A1:A100,"*(*RA*)")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Sheet1

ABC
1Connor, Sarah (1234567) Branson (RD, RA, FSFB) 1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:305px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C1=COUNTIF(A1:A100,"*(*RA*)")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Scott,
You are correct in that the formula counts the RA within the parenthesis as requested and I am extremely grateful for this guidance.
The problem is my fault...my request did not anticipate the presence of ra in teacher name but only ra in student name preceding any parenthesis. Since the teacher name technically occurs within a set of parenthesis, the count would increase from "Branson" even if there were a set of Acronyms without and RA such as

Connor, Sarah (1234567) Branson (RD, RT, FSFB)

PS I tried to install Excel Jeanie on Office 2k10 but cant get it to work...sorry.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top