How to count how many times a word appear in multiple cells?
I am trying to count how many times a word appear in multiple cells from a different sheet.
I am using =SUM((LEN([Column6]2)  LEN(SUBSTITUTE([Column6]2, [Part Number]@row, ""))) / LEN([Part Number]@row)) and it works if I look just for one cell, but if I try to count from two cells appear Invalid type data.
Anyone?
Answers

If you generate the number by row, you can SUM the column that houses your per row counts.
Or you could use a JOIN function to bring the entire range together into a single string and evaluate that way.
=(LEN(JOIN([Column6]:[Column6], "/"))  LEN(SUBSTITUTE(JOIN([Column6]:[Column6], "/"), [Part Number]@row, "")) / LEN([Part Number]@row)

The problem with the JOIN formula is that I have way more than 4000 characters to be analysed. I exported everything to excel tables and my formula worked. It is probably something with the Smartsheet system.

Could the word appear multiple times within the same cell?

They Appear many times in an single cell.

Ok. Then yes. You are going to have to do the row by row count then sum the count column.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!