Count Number of Cells that Contain Certain Text?

ecosbar

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!

Malcolmwa

Try 

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

ecosbar

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

Andrée Starå

Hi,

Did you get it working?

Happy Holidays & Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

ecosbar

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

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

ecosbar

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å

Hi Emily,

Can you share the formula or the sheet?

Best,

Andrée

John 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. 

Nancy Heater

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)

Sean Morgan

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

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.

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

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.

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

Irene Dee

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