1

Hi and thank you in advance for your help.

enter image description here

I am trying to count the number of times a text string, for example "UML" occurs within a column. The column may contain the text by itself or it may contain the text(UML) in a list with other text(UML,SAS,Excel). I need to be able to count it even if it's within a list. I understand using two * on each side of the text string will work if I write it out but I don't want to write it over and over again.

I am open to alternatives other than countif formula but if there is a way to do it with countif would you please share both.

Thanks again for your help!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
BetterEveryDay
  • 271
  • 1
  • 3
  • 6

2 Answers2

0

If column F contains your lookup value, and it's contained in square brackets, then building on your formula I'd use:

=COUNTIF($A$1:$A$4247,SUBSTITUTE(SUBSTITUTE("*" & F2 & "*","[",""),"]",""))

We substitute the square brackets for nothing, to remove them on the fly from the lookup value. Then we search the strings, basically for *UML*. And this formula is draggable downable (F2 will move with you). Is this what you need?

David
  • 1,222
  • 1
  • 8
  • 18
0

Just to put my two cents in:

The formula to count column A on what is entered in F2 is:

=Countif(A:A,"*" & F2 & "*")
Scott Craner
  • 148,073
  • 10
  • 49
  • 81