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

Options
edited 07/21/23

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

## Answers

• ✭✭✭✭✭✭
Options

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

• Options

Hi Debbie,

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

Thank you!

Nancy

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Hi all,

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Hi Paul,

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!