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

Options

"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 ✭✭✭✭✭✭
    Options

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

    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)

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • rakmis
    rakmis ✭✭
    Options

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

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

    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

  • rakmis
    rakmis ✭✭
    Options

    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!