Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Count number of cells containing specific text

michelangelo
edited 12/09/19 in Archived 2016 Posts

Hi,

 

I need to count the number of cells that contains a part of a text.

Is there any wildcart symbol I could use? 

 

for example

 

abcd

abfd

abpd

jged

 

In this case I need a formule that count the cells with the text starting with the letters ab.

 

/Michelangelo

Tags:

Comments

  • I don't think there is a wildcard, but you could use 

    =COUNTIF(LEFT(text1, 2), "ab")

     

    Christine

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I see where Christine is going with her COUNTIF, but I couldn't quite get it to work.

     

    I added a column (a checkbox column) with this formula:

     

    =IF(LEFT([Primary Column]9, 2) = "ab", 1, 0)

     

     

    The check box is checked if the [Primary Column] starts with "ab".

     

    If your data is under a parent hierarchy, then this at the parent row

    =COUNTIF(CHILDREN(), 1) + ""

     

    will get you a count (in text format) of the number of 'ab' found in the children.

    I convert to text because checkboxes won't take numbers greater than 1.

     

    Or change the column to a text/number column and use a SUM on the CHILDREN instead.

     

    If your data is NOT in a hierarchy, then 

     

    =COUNTIF([Primary Column]:[Primary Column], 1) + ""

     

    If you need to use the number in a formula, convert it back using VALUE.


    Craig

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 05/07/16

    Oh, and if you want to find any instance of "ab" (jabc) not jus the first two letters, try FIND.

     

    =IF(FIND("ab", [Primary Column]23) > 0, 1, 0)

     

     

    Craig

This discussion has been closed.