CountIF scenario

Hi Team, I have a scenario listed below where have created 5 options under dropdown

Have placed the score based on the expected outcome.

Not all KPIs have month #4 applicable since these are confined to quarterly measure and not Trimester.

Each Month met gives you 33% if applicable for a Q and 25% of for a Trimester based on the user selection.

Can you please help with the formulae.

There was a formuale provided to me in the past for a similar scenario but dont work in this case. Coying below for ref


=IFERROR((COUNTIF([Monthly Coaching Month(s) when goals met]@row, CONTAINS("M1", @cell)) 

+ COUNTIF([Monthly Coaching Month(s) when goals met]@row, CONTAINS("M2", @cell)) 

+ COUNTIF([Monthly Coaching Month(s) when goals met]@row, CONTAINS("M3", @cell))) 

(COUNTIF([Monthly Coaching Month(s) when goals met]@row, 

OR(CONTAINS("M1 - Met", @cell), 

CONTAINS("M2 - Met", @cell), 

CONTAINS("M3 - Met", @cell))) * 3), "")

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Gaurav Chauhan

    I hope you're well and safe!

    Try something like this.

    =IF[Monthly Coaching Month(s) when goals met]@row="","", IF(CONTAINS("M4 - Not Applicable", [Monthly Coaching Month(s) when goals met]@row), SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 3, SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 4)

    Did that work?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    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.

Answers

  • Paul McGuinness
    Paul McGuinness Overachievers

    HI @Gaurav Chauhan

    This formula seems to do what you need.

    =IF(CONTAINS("M4 - Not Applicable", [Monthly Coaching Month(s) when goals met]@row), SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 3, SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 4)

    Tested as below

    Hope this helps

    Thanks

    Paul

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    Thank you Paul for the same. Indeed works when the users make the right selection from dropdown. Thank you


    If the cell formulae is converted to column, it is populating 0% for all blank rows. Have wrapped the formluale beytween IFERROR( ,"") but doesn work.

    Can you help correct this.

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    Hi @Paul McGuinness - Can you please confirm on how to wrap the formulae between IFERROR ( ,"") so that defaulted 0% is replaced with blank.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Gaurav Chauhan

    I hope you're well and safe!

    Try something like this.

    =IF[Monthly Coaching Month(s) when goals met]@row="","", IF(CONTAINS("M4 - Not Applicable", [Monthly Coaching Month(s) when goals met]@row), SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 3, SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 4)

    Did that work?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    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 McGuinness
    Paul McGuinness Overachievers

    Hi @Gaurav Chauhan

    Apologies for not responding sooner and thanks @Andrée Starå for the solution.

    Much appreciated

    Paul

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

    @Paul McGuinness

    Happy to help!

    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.

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    Hi @Andrée Starå - The formulae as shared by you is throwing an Unapparseable error

    =IF[Monthly Coaching Month(s) when goals met]@row="","", IF(CONTAINS("M4 - Not Applicable", [Monthly Coaching Month(s) when goals met]@row), SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 3, SUM(COUNTIF(CONTAINS("M1 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M2 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M3 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell), COUNTIF(CONTAINS("M4 - Met", [Monthly Coaching Month(s) when goals met]@row), @cell)) / 4)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!