Count number of cells that contain specific text

kat_h

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.

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!