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

Options
✭✭
edited 12/29/23

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)")))))))))

• ✭✭✭✭✭✭
Options

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

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.

• ✭✭
Options

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!