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

swirt009

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

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!

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,