Count if X, if not count Y, if not count Z
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!