Count if X, if not count Y, if not count Z

swirt009
swirt009 ✭✭✭
edited 12/29/23 in Formulas and Functions

Hi All,

I'm hoping someone can help me with my dilemma below.

I have a form where people register for a training series (e.g., Series 1 (date/time)). Their response feeds into the "P&I Series" column in the screenshot below.

I need a formula to count the number of participants who've selected that series and display the number in the "P&I Series Participant" column in the screenshot below. Example: If "P&I Series" is "Series 1 (date/time)" then count, if not then see if the "P&I Series" is "Series 2 (date/time)" then count, if not then see if the "P&I Series" is "Series 3 (date/time)" then count.

I need this as a column so I can create an automation to notify me once a session has the maximum number of participants (16).

Thanks!

-----------------------------------------------------------------------------------------------


Update:

I figured it out! The formula I used was:

=IF([P&I Series]@row = "Series 1 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 1 (date/time)"), IF([P&I Series]@row = "Series 2 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 2 (date/time)"), IF([P&I Series]@row = "Series 3 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 3 (date/time)"), IF([P&I Series]@row = "Series 4 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 4 (date/time)"), IF([P&I Series]@row = "Series 5 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 5 (date/time)"), IF([P&I Series]@row = "Series 6 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 6 (date/time)"), IF([P&I Series]@row = "Series 7 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 7 (date/time)"), IF([P&I Series]@row = "Series 8 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 8 (date/time)")))))))))

Answers

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

    Hi @swirt009

    I hope you're well and safe!

    Excellent! Glad you got it working!

    Have a fantastic weekend & Happy Holidays!

    Best,

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

    Please support the Community by marking your post with the accepted answer/helpful. 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.

  • swirt009
    swirt009 ✭✭✭

    I figured it out!

    The formula I used was:

    =IF([P&I Series]@row = "Series 1 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 1 (date/time)"), IF([P&I Series]@row = "Series 2 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 2 (date/time)"), IF([P&I Series]@row = "Series 3 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 3 (date/time)"), IF([P&I Series]@row = "Series 4 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 4 (date/time)"), IF([P&I Series]@row = "Series 5 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 5 (date/time)"), IF([P&I Series]@row = "Series 6 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 6 (date/time)"), IF([P&I Series]@row = "Series 7 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 7 (date/time)"), IF([P&I Series]@row = "Series 8 (date/time)", COUNTIF([P&I Series]:[P&I Series], "Series 8 (date/time)")))))))))


    Thanks,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!