count itmes in a column that is populated with multiple data

Options
ChrisM68
ChrisM68 ✭✭
edited 12/09/19 in Smartsheet Basics

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.

«1

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • ChrisM68
    Options

    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

     

    counting probabilities.PNG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Thanks and I wish you the same!

    Happy to help!

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

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    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

    Machine Counting.jpg

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    edited 04/22/19
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    edited 04/22/19
    Options

    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
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    Thank you Paul!

    What a formula.. I like it! wink

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

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

    Let me know if I can help with anything else!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    Happy to (try and) help! wink

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • ChrisM68
    Options

    Hi Debby,

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

    Thanks again ...

  • ChrisM68
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help! yes

     

    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.