Count Number of Cells that Contain Certain Text?

ecosbar
ecosbar
edited 12/09/19 in Formulas and Functions

Hello,

I am trying to count the number of cells that contain certain words, such as "NYSEG" or "RGE," when the cells may contain more than one of these words.

I have tried using =COUNT(FIND("ConEd", [Utilities]:[Utilities])), but it always turns back a 1 (no matter how many cells actually contain "ConEd"). 

I am trying to return something along the lines of :

Utility | Number of Cells that mention

ConEd | 5

NYSEG | 10

etc.

Thank you!

Screen Shot 2018-12-03 at 1.20.22 PM.png

«1

Comments

  • Malcolmwa
    Malcolmwa
    edited 12/03/18

    Try 

    =COUNTIF([Utilities]:[Utilities]; FIND("ConEd"; @cell) > 0)

  • Thank you for the suggestion! Unfortunately it is returned as "UNPARSEABLE" in Smartsheet. 

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Did you get it working?

    Happy Holidays & Happy New Year!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi! Thank you for checking in; unfortuantely I have not been able to. Do you have any suggestions?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Try this.

    Place the formula below in the "Number of Cells that mention" column.

    The Utility@row part in the formula looks at the Utility row and search for the text there like "ConED" or "NYSEG".

    =COUNTIF(Utilities:Utilities; FIND(Utility@row; @cell) > 0)    

    The same version but with the below changes for your and others convenience.    

    =COUNTIF(Utilities:Utilities, FIND(Utility@row, @cell) > 0)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Happy New Year!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi there! Thank you for your help; unfortunately this is still returning an #UNPARSEABLE error. Do you have any further suggestions?

    Emily

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Emily,

    Can you share the formula or the sheet?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I've sometimes seen that #UNPARSEABLE error when there are blank fields within the range you are searching. Try filling in blank fields (save) and run the formula again or TIP: Add the IFERROR function to your formula to ensure that you don't get an error if @cell encounters a blank cell in the formula. 

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    edited 09/28/20

    I'm trying to use this same formula to look up any instance of great_dane in a column and am getting the same #UNPARSEABLE result.

    If we wanted to add IFERROR as suggested, how would this be added to the example below:

    =COUNTIF(Dogs:Dogs, FIND(great_dane@row, @cell) > 0)

  • Hello @Nancy Heater ,

    When searching for greast_dane, is this a text value in a cell?

    You may instead want to try "great_dane", or if the underscore is to account for a space, you may instead opt for "great dane", but please ensure you are case sensitive with the letter included in the words you are searching for, as well as include quotation marks when searching for words or phrases.

    In addition to this, the @row section and FIND may not be necessary if you are searching for this on the same Sheet. If on the same Sheet, you could just use =COUNTIFS(Dogs:Dogs, "great_dane")

    Regards

    Sean

  • Irene D
    Irene D ✭✭

    Hi @Sean Morgan ,


    When I tried the formula you suggested on my sheet (replacing Dogs and "great_dane" with the applicable terms), I unfortunately get the following error message: #BLOCKED


    Do you know what the cause is and how to fix it?


    Thanks,

    Irene

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Irene D

    Where are you placing the formula, is it in a different column in the same sheet?

    Can you check through the column you're referencing (Dogs:Dogs) to see if there's an error in this column? Sometimes errors can create a domino effect, so if the column you're referencing has a #BLOCKED message this will come through in your other formula.

    If neither of this has helped, it would be useful to see a screen capture of your sheet, but please block out sensitive data.

    Cheers,

    Genevieve

  • Irene D
    Irene D ✭✭

    Hi @Genevieve P,

    I think the Smartsheet plan may have been glitching since changing the column properties, then changing them back fixed the issue. However, a new issue arose since I learned that if the Discipline column contains "CMC," but isn't a match case, then it doesn't go towards the count in the formula below. Could you potentially help me add "or" statements to include all disciplines that contain the word CMC or is there a way to change it so it picks up anything in the discipline column that contains the word CMC?


    =COUNTIFS(Document:Document, =1, Discipline:Discipline, "CMC", Status:Status, "Not Started")


    Thank you!

    Irene

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Irene D

    No problem! There are two potential ways for doing this, depending on the type of column your "Discipline" column is set as - is it a Multi Select column? If so, we would use the HAS function. Otherwise, if it's a text/number column, we could use the CONTAINS function.


    Here's how you would use HAS, if it's Multi-Select:

    =COUNTIFS(Document:Document, =1, Discipline:Discipline, HAS(@cell, "CMC"), Status:Status, "Not Started")

  • Hi @Genevieve P.

    Another similar question arose for me recently. Instead of specifying the discipline, is there a way for me to include everything in the discipline column except anything that contains CMC?


    Thanks again!

    Irene

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!