Struggling with Formula...not a qualified programmer!!!

"In sheetname ??? If column Risk Score is between 0 to 5 and Risk/Item column contains "Risk" then count how many risk are between 0-5"

This is what I tried and did not work.....

"=COUNTIFS({BT RAID Log Range 1} Risk Score, ">0", Risk Score, "<=5", Risk/Item, "*Risk*")"

Can someone please advise on how to create this formula in Smartsheet????

Struggling when there are no decent YouTube channels on creating formulas.

Call me on +44 07930 508 813

Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    I'm going to assume based on your description that your data is organized something like this:

    If you place this formula:

    =COUNTIFS([Risk Score]:[Risk Score], >=0, [Risk Score]:[Risk Score], <=5, [Risk/Item]:[Risk/Item], "Risk")

    In the first cell of the column Total Risks, you get the count of 4, which is the total number of Risk Scores that are greater than or equal to 0 but less than or equal to 5 AND that are categorized as "Risk" in the Risk/Item column.

    If the columns you are trying to count are on a different worksheet than the worksheet you are using to count the relevant results, you would need to use a cross-sheet reference. You can learn more about these here: https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

    You can also find a lot of helpful information about working with formulas in Smartsheet here: https://help.smartsheet.com/topics/formulas-and-functions

  • rakmis
    rakmis ✭✭

    I am trying to create formula for this scenario..

    are Formulas correct....?

    =COUNTIF({BT RAID Log Range 1}), >=0, [Impact]:[Impact], <=2, [Risk/Issue]:[Risk/Issue], "Issue") (LOW)

    =COUNTIF({BT RAID Log Range 1}), >=3, [Impact]:[Impact], <=4, [Risk/Issue]:[Risk/Issue], "Issue") (MEDIUM)

    =COUNTIF({BT RAID Log Range 1}), >=5, [Impact]:[Impact], [Risk/Issue]:[Risk/Issue], "Issue") (HIGH)

  • Hi @rakmis

    There are a few small tweaks you would need to do to this formula:

    1 - Use CountifS plural because you are looking for multiple criteria

    2 - You have a {cross sheet reference} and a reference to your column in your sheet. I believe you only need the one reference, so change {BT RAID Log Range 1} to [Impact]:[Impact],


    Try:

    =COUNTIFS([Impact]:[Impact], >=0, [Impact]:[Impact], <=2, [Risk/Issue]:[Risk/Issue], "Issue")


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • rakmis
    rakmis ✭✭

    what does this "[Impact]:[Impact]," ...I don't understand!

  • Genevieve P.
    Genevieve P. Employee
    edited 05/15/23

    Hi @rakmis

    The words in [these] are the column names. I can't see the column names in your example so I was going off of what you have here:

    However in Smartsheet these would need to be here:

    Then if you're building a formula in the same sheet, you'll reference that column by using its name. You can do this by clicking on any cell in that column to bring it into the formula:

    In your case, you want to reference the entire column, so you'll want to reference the column name twice with a colon between, like so:

    The process is different if you're using cross-sheet formulas (if you're writing the formula in a second sheet).

    Here are two free webinars that you may find helpful as you learn about formulas:

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • rakmis
    rakmis ✭✭

    Thank you so much...now this makes a lot more sense!!! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!