Count Number of Cells that Contain Certain Text?

12/03/18 Edited 12/09/19

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

Comments

  • 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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 [email protected] part in the formula looks at the Utility row and search for the text there like "ConED" or "NYSEG".

    =COUNTIF(Utilities:Utilities; FIND([email protected]; @cell) > 0)    

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

    =COUNTIF(Utilities:Utilities, FIND([email protected], @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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • John SwainJohn Swain ✭✭✭✭✭

    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. 

  • 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([email protected]@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

  • 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

  • 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

  • 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

  • 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")

Sign In or Register to comment.