Count Number of Cells that Contain Certain Text?
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!
Comments
-
Try
=COUNTIF([Utilities]:[Utilities]; FIND("ConEd"; @cell) > 0)
-
Thank you for the suggestion! Unfortunately it is returned as "UNPARSEABLE" in Smartsheet.
-
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?
-
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
-
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.
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!