How to count how many times a word appear in multiple cells?

Bernardo Ticom

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?

Paul Newcome

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)

Bernardo Ticom

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.

Paul Newcome

Could the word appear multiple times within the same cell?

Bernardo Ticom

They Appear many times in an single cell.

Paul Newcome

Ok. Then yes. You are going to have to do the row by row count then sum the count column.