count itmes in a column that is populated with multiple data

Happy to help!

Happy to help Chris!

Hi Debbie,

Thanks for the explanation ... I really appreciate your support and yes you and Paul have both helped me to solve my issue.

Many thanks for that again ...

Cheers!

Chris

Happy to help too

I ditto Paul's opening paragraph!

I started using Smartsheet in Oct 2016 and for the last 2 years have written a few courses in it which I teach either onsite if the client is in the UK, or remotely in 90 minute modules delivered via Zoom Meetings conference tool, if the client is overseas. The modules I teach cover all aspects of using Smartsheet.

How did I learn it? Well - from 1996 - 2007 My main role was as an IT Trainer predominantly a Microsoft Office Applications Trainer. My favourite application to train was MS Access, with MS Excel coming in a close second. I dabbled in MS Project training too, but generally used a freelance trainer back then called James Rezin (who you may have come across in this community (also an SBP consultant! It was James who introduced me to Smartsheet in Oct 16!). From 2007 - 2016 I consulted in ITIL Processes and helped clients set up service desk tools. The reporting side of that saw me as a Crystal Reports consultant (where my formula's were tested to the max!!)

My approach to any "problem" is that if the problem follows logical pattern, then there must be a way of providing a solution. I must say I love using Smartsheet and have picked it up via learning from my colleagues at SBP and by trial and error. I like the way you have to think outside the box sometimes to find a way of getting what you want! I am always up for learninig more and by having a play with your scenario here, Paul has introduced me to his amazing formula!

I am relatively new to the Community and find it a great source of wisdom. Hopefully people don't mind me popping my thoughts in here too now!

Have a great day Chris and let us know if between us we have solved your problem.

Kind regards

Debbie

Hi Chris

Please see the image below for an explanation of the functions used.

I am certain other consultants would have different solutions, but this works and isn't too laborious!

Kind regards

Debbie

Proj A B C.jpg

https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.66067626.529420994.1554725353-1302373248.1552411124

Happy to help!

As far as learning goes... It took me a lot of patience, trial and error, creativity, thinking outside of the box, a solid brick wall to beat my head against, a lifetime supply of Advil, and even more patience. Haha.

There are two things I regularly consult when building formulas. There is a template htat you can download called "Smartsheet Formula Examples" that is an interactive sheet with a listing of all functions along with how they are used and what type of data they are used for and to display.

I start with the big picture. I need a formula that will display THIS. So I search the template for what will display that. I then look at the syntax and what type of data needs to be input. If it requires a number, and I want to automate that number, I look at all of the functions that display a number. I look at that syntax and what type of data needs to be input into that one. I just keep breaking it down further and further until I have a series of bare bones functions.

I then start by testing each portion separately to ensure it really is giving me the values I need. Once that is done and I can scan across a row to ensure every detail is correct, I then start to piece them together using cell references. When I have a formula consisting of cell references, I start replacing the cell references with the actual functions/formulas that are in those specific cells.

Long story short... Break it down into as small of pieces as you can. Look at the fine details. Make sure they all work. Then build.

In addition to the template I use this page a lot:

It is a listing of all error messages, what causes them, and some trouble shooting tips. It will help you find exactly what the issue is if you get an error.

Hi Paul,

Thanks for this amazing formula ... you really helped me out!

@you and Debbie: where did you learn all this? Are there books or videos with these great ideas?

Thanks for helping!

Cheers

Chris

Hi Debby,

Thanks for your contribution ... Would you be so kind to explain how you solved it ?

Thanks again ...

Andrée Starå

Happy to (try and) help!

Best,

Andrée

Hi Andrée,

I just came back from a business trip and saw your answer, and the other ones as well.

Thanks anyways ...

Cheers

Happy to help. It's a little something I've come up with for parsing collected data that has a delimiter. I know that L@123 has another parsing system buried somewhere here in the Community too. If I come across it again, I'll try to remember to post a link here.

Andrée Starå

I saw that you've got excellent answers from others!

Let me know if I can help with anything else!

Best,

Andrée

Thank you Paul!

What a formula.. I like it!

The above will give you the first number. Numbers after that, you will need to start taking advantage of the start_position field built into the FIND function. To find the second number, you would use the above formula, but in each of the FIND functions, you would use

FIND(";", Probabilities@row)

in the start_position field.

=VALUE(MID(Probabilities@row, FIND("(", Probabilities@row, FIND(";", Probabilities@row)) + 1, FIND(")", Probabilities@row, FIND(";", Probabilities@row)) - FIND("(", Probabilities@row, FIND(";", Probabilities@row)) - 1))

Values after that only seem to get more complicated because of nesting FIND functions within start_position fields, but if you take your time and work on one piece at a time, it really should be more tedious that complicated.

The key is to remember that FIND returns a numeric value based on the position of the "text" within the text string of the cell.