count itmes in a column that is populated with multiple data

ChrisM68

Hi guys,

I have a new challenge for you . I have a list of projects. Each project contains different products. I have one probabilty column that contains all the probabilities to sell the different products contained in each individual project.

An exaple: I have a project (1 row) where I can sell machines types A, B and C. The respective probabilities are: Prob_Machine_A(90), Prob_Machine_B(70) and Prob_Machine_C(90). The number in the parentheses are the actual probabilities. All these probabilities are contained in the cell crossing the prject name and the probability column, and they are separated by a semi-column.

Now imagine that I have a full list of different projects.

Now the challenge: I would like to count:

- all the project with the different machine types (how many proj. with machine type A, B etc...)

- all projects with probabilities > 70%

If anyone could help me with the first one, that would be a great help!

Thanks in advance.

Andrée Starå

Hi Chris,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

ChrisM68

HI Andrée,

First I would like to wish you a Happy Easter Monday... 

Thanks for the quick reaction, I really wasn't expcting this during the Easter weekend ... big thumbs up for that.

I shared a sheet called sample with you, but I also uploaded a screen shot of the file.

Thanks in advance and cheers

Chris

 

Andrée Starå

Thanks and I wish you the same!

Happy to help!

I'll take a look and get back to you!

Best,

Andrée

Debbie Sawyer

Hi Guys

I thought this was an interesting scenario so I thought I'd also have a play.  I managed to get to get the right answers, but there are a few hidden columns! (1 hidden column per machine type) but if that is feasible, then this works!

Happy to share the sheet and explain the formulas if required

Kind regards

​Debbie Sawyer Consultant & Training Manager

debbie.sawyer@smarterbusinessprocesses.com

Debbie Sawyer

Looking at your second requirement (>70%) this is going to be tricky as the Probabilities value is treating the number as text, therefore isolating the number from the text string then treating it as a numeric will take some thinking.

 

Paul Newcome

Isolating the number shouldn't be too hard if the numbers are always wrapped in parenthesis using a MID/FIND formula. Converting that into a useable number would be as simple as wrapping the formula in a VALUE function.

 

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

Paul Newcome

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.

Debbie Sawyer

Thank you Paul!

What a formula.. I like it!

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

Paul Newcome

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.

ChrisM68

Hi Andrée,

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

Thanks anyways ...

Cheers

Andrée Starå

Happy to (try and) help! 

Best,

Andrée

ChrisM68

Hi Debby,

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

Thanks again ...

ChrisM68

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

Paul Newcome

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:

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

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.