Count number of cells that contain specific text

Hi,

I am trying to work out the formula to count the number of cells that contain specific text (eg "FG"). The cells may contain more than the specific text so I want to ensure that any cell with the text is included. Some of the cells may contain text with an extension such as "FGL". If possible, I would like to exclude "FGL" from any "FG" count. The "FG" may be anywhere in the cell.

I am referencing a single column that exists in an alternate sheet as well.

Some examples of text in the cells:

- FG

- FGL

- DR

- FG/DR

- DR/FG

- FGL/DR

Hopefully that makes sense.

Find more posts tagged with

Formulas

Comments

Paul Newcome

Hmm... This could get a little tricky. I like it. I am going to have to do some thinking and testing on this one and get back to you.

kat_h

Thanks, I feel like I am going crazy with this. Technically, there is an added difficulty that I was just going to drop where I have both "FG" and "FGL" in the same cell as well and would like to include that in the count. I can live without that though!

Paul Newcome

Honestly that doesn't have an effect on the solution. The reason being that we are already working on discounting FGL while continuing to count FG.

The good news is... When reading through this comment of yours, and idea struck me that I was excited to test.

But then while typing my reply, another idea struck me that seems like it may be even more efficient and accurate.

Either way, I now have two different ideas I am going to test out and then get back to you on.

Paul Newcome

Ok. So here we go...

Create a helper column that we will just call "Helper" for this example. In this column we will use...

="/" + [Text Column]@row + "/"

This will essentially use a set of / to surround each different value.

Then you can use a COUNTIFS incorporating this.

=COUNTIFS(Helper:Helper, CONTAINS("/FG/", @cell))

See the screenshot below.

kat_h

Thank you so much! This has worked great. I just need to get the staff doing data entry not to use spaces before or after the "/"

Paul Newcome

Spaces can be relatively easy to account for. We just use a SUBSTITUTE function to essentially remove them.

So we want to SUBSTITUTE a space " " (quote space quote) for a blank "" (quote quote) which basically removes the space. We also want to do this within each CELL.

SUBSTITUTE(@cell, " ", "")

Now that we have removed spaces from the data we are looking at, we can drop that into the appropriate portion of our CONTAINS function that is within our COUNTIFS formula.

=COUNTIFS(Helper:Helper, CONTAINS("/FG/", SUBSTITUTE(@cell, " ", "")))

Now they can put in all the spaces they want and it won't make a difference in regards to the calculations.

kat_h

Thanks again!

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.