IF Formula assigning scores

Hello,


I have a formula (below) that assigns scores depending on a criteria and whether or not the checkbox is checked. I.e. If the Governor's Dashboard is checked, it will assign 4 points to the total project score. I have one item that I no longer want to be a checkbox (bolded below).

I would like "Do you have required funding? (3,0) NA=Check" To be a drop down where if "Yes" or "NA" is selected, it applies a score of 3. How would I implement that into this formula?


=IF([Governor's Dashboard (4,0)]@row, 4) + IF([Dept WIG (3,0)]@row, 3) + IF([Office WIG (2,0)]@row, 2) + IF([Division WIG (1,0)]@row, 1) + IF([CStat (1,0)]@row, 1) + IF([Savings over $1 Million (3,0)]@row, 3) + IF([Savings $500k -$999k (2,0)]@row, 2) + IF([Savings of over $100K-$499 (1,0)]@row, 1) + IF([CDHS Strategic Plan (2,0)]@row, 2) + IF([Legislative or Regulatory Requirement - Statu(4,0)]@row, 4) + IF([Legislative or Regulatory Requirement -Fed AU(3,0)]@row, 3) + IF([Legislative or Regulatory Requirement OSA(2,0)]@row, 2) + IF([Legislative or Regulatory Requirement- Int A (1,0)]@row, 1) + IF([Client Safety (4,0)]@row, 4) + IF([Broad and Direct Stakeholder Impact (2,0)]@row, 2) + IF([Improve Security IT-Risk etc (4,0)]@row, 4) + IF([Better Outcomes for Our Clients (4,0)]@row, 4) + IF([Do you have required funding? (3,0) (NA=check)]@row, 3) + IF([How many stakeholders impacted? or ppl trained]@row < 99.9, 0, IF([How many stakeholders impacted? or ppl trained]@row < 499.9, 1, IF([How many stakeholders impacted? or ppl trained]@row < 999.9, 2, 3)))

Tags:

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    @Maria Watters

    Assuming your options are "Yes, NO, NA" then your IF() statement could be...

    IF( [Do you have required funding? (3,0) NA=Check]@row <> "NO", 3)

    Otherwise, you can include OR() in your IF() statement...

    IF( OR([Do you have required funding? (3,0) NA=Check]@row = "Yes", [Do you have required funding? (3,0) NA=Check]@row = "NA" ), 3)

    You can also use a nested IF() statement...

    IF( [Do you have required funding? (3,0) NA=Check]@row = "Yes",3, IF([Do you have required funding? (3,0) NA=Check]@row@="NA", 3))

    Additional documentation on Smartsheet functions can be found here, https://help.smartsheet.com/functions

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    @Maria Watters

    Assuming your options are "Yes, NO, NA" then your IF() statement could be...

    IF( [Do you have required funding? (3,0) NA=Check]@row <> "NO", 3)

    Otherwise, you can include OR() in your IF() statement...

    IF( OR([Do you have required funding? (3,0) NA=Check]@row = "Yes", [Do you have required funding? (3,0) NA=Check]@row = "NA" ), 3)

    You can also use a nested IF() statement...

    IF( [Do you have required funding? (3,0) NA=Check]@row = "Yes",3, IF([Do you have required funding? (3,0) NA=Check]@row@="NA", 3))

    Additional documentation on Smartsheet functions can be found here, https://help.smartsheet.com/functions

  • Maria Watters
    Maria Watters ✭✭✭✭✭

    @Toufong Vang Thank you so much! I ended up using the IF/OR statement so it wouldn't count if it was blank! This worked wonderfully.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!