Count the number of times a text value appears in a single cell (or alternatively, split the cell)
Hi Smartsheets community,
I have a column of data with cells that contain text values that I need to count. For example, the cell in one of these columns may contain something like:
"ABC123  TEXT TEXT TEXT
ABC008  TEXT TEXT TEXT"
The two lines of "ABC" text are separated by a carriage return.
I'm trying to count how many times each "ABC000" value occurs in my spreadsheet. So I need to know how many times we find ABC001, ABC002, and so on. Sometimes, there is only one "ABC" value in a cell, sometimes there are two or more.
Any suggestions on how to do this? Smartsheets does not seem to have a SPLIT function, where I could split out the values via the carriage return and then attempt to count them that way.
Thanks in advance.
Best Answer

Hi @LEllis,
A good way to do this would be to create a sheet with each ABC value in a column, then add a column with a crosssheet formula to count the number of times each value appears on your original sheet.
Here's my pretend original sheet:
And here is the new sheet to count the ABCs:
The count column formula is:
=COUNTIFS({Data column on original sheet}, CONTAINS([Primary Column]@row, @cell))
www.linkedin.com/in/juliefortneypmpsmartsheetpartnerlssblackbelt
Answers

Hi @LEllis,
A good way to do this would be to create a sheet with each ABC value in a column, then add a column with a crosssheet formula to count the number of times each value appears on your original sheet.
Here's my pretend original sheet:
And here is the new sheet to count the ABCs:
The count column formula is:
=COUNTIFS({Data column on original sheet}, CONTAINS([Primary Column]@row, @cell))
www.linkedin.com/in/juliefortneypmpsmartsheetpartnerlssblackbelt

Wow, this is amazingly simple and appears to do what I need. You have no idea how complicated I was making this! Thank you so much.

@LEllis You're welcome! I know just what you mean, but I've found that all that trial and error usually helps me learn something that comes in handy later on.
www.linkedin.com/in/juliefortneypmpsmartsheetpartnerlssblackbelt

Thanks @Julie Fortney This just saved me a bunch of headache searching and figuring out too!!
Help Article Resources
Categories
Check out the Formula Handbook template!