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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!