Is there a way to create a formula that only uses part of the information in a cell?

Nancy JOnes

I am trying to count the total number of times a name is used in a cell that is a free text field and could have multiple names. Is there a formula that will allow me to do that? I have tried tried CONTAINS and OR functions but neither lets me pull specific values. Below is an example of a formula that works, but the work put into it was time consuming.

In the example, I am trying to get the count for each time Prisma is used:

=COUNTIF([Employer Not Listed]:[Employer Not Listed], OR(@cell = "Prisma Health", @cell = "Prisma health, Pruitt Health, MUSC", @cell = "Prisma Health, Spartanburg Regional", @cell = "Prisma Children's Hospital", @cell = "Prisma", @cell = "Bon Secours or Prisma Health", @cell = "Lexington Hospital, Prisma or MUSC", @cell = "Medical University of South Carolina, Roper Hospital, Prisma Health, Lexington Medical Center", @cell = "MUSC, Summerville Medical Center, Trident Medical Center, Prisma Health", @cell = "AnMed or Prism", @cell = "Prisma Tuomey or Carolina Pines", @cell = "Anmed, Prisma Health"))

I appreciate any help you can provide.

Thank you,

Nancy

Debbie Sawyer

@Nancy JOnes

Hi Nancy, I think I understand the issue. I set up a simple example to show an option:

I have a Name column that if I just use:

=COUNTIF(Name:Name,"Debbie") returns 0. as None of the values is just the word Debbie.

So my formula is looking at 1 column and counting the cell if the word Debbie is present anywhere in that cell.

To expose the formula as text for explanatory purposes I omitted the first =, so if you were to use this you will need an = at the start.

So for your example this would be:

=COUNTIF([Employer Not Listed]:[Employer Not Listed],CONTAINS("Prisma",@cell))

Would this work?

Kind regards

Debbie

Nancy JOnes

Hi Debbie,

Yes, that worked. I thought I had already tried it and it didn't work. I appreciate your time.

Thank you!

Nancy

Debbie Sawyer

No problem!

I know what its like! You try so many things and get that dreaded unparseable!! ha ha.

I'm glad this worked and might simplify things for you in the future.

Kind regards

Debbie

Kelly R.

Hi all,

I would like to use this formula but for a column not a specific cell. Any suggestions? Thank you.

Paul Newcome

@Kelly R. Are you able to provide more details? The formulas throughout do reference an entire column.

Kelly R.

Hi Paul,

I was able to figure this one out...thank you for your willingness to help!