Formula for numbering rows based on specific conditions

Hello!
I need to create formulas for two columns that number rows based on specific conditions. I have a helper column (Number).
First, I want to number rows that: Type is "Focus Group Volunteer" and Internal is "yes."
Second, I want to number rows that: Type is "Focus Group Volunteer", Internal is "no", and Patron Type is "Faculty", "Instructor", or "Visiting Scholar."
When I added my formulas, the second is skipping 3 for some reason. Any assistance is greatly appreciated!
Here is an example:
Formula for "Number: Internal" column:
=IF(Type@row = "Focus Group Volunteer", IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, [Internal]:[Internal], OR(@cell = "Yes")), 1), ""))
Formula for "Number: External Faculty" column:
=IF([Internal]@row = "No", IF(Type@row = "Focus Group Volunteer", IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholar")), 1), "")))
Best Answer
-
Hi @Brooks,
This is happening because the COLLECT statement is including the row with "Feedback" as a type, we need to ensure the COLLECT only has the "Focus Group Volunteer".
Try this one.
=IF(AND(Internal@row = "No", Type@row = "Focus Group Volunteer"), IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, Type:Type, "Focus Group Volunteer", Internal:Internal, "No", [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholor")), 1), ""))
Dave
Answers
-
Hi @Brooks,
Even though your initial IF is excluding the 2nd row, the COLLECT is including it in the results, you need to tweak the COLLECT portion. Try this.
=IF(AND(Internal@row = "No", Type@row = "Focus Group Volunteer"), IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, Internal:Internal, "No", [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholor")), 1), ""))
Hope this helps,
Dave
-
Hi @DKazatsky2 ,
Thank you for your quick reply! I made the change that you suggested and we got 3 back. But sadly, it's skipping 4 now.
-
Hi @Brooks,
This is happening because the COLLECT statement is including the row with "Feedback" as a type, we need to ensure the COLLECT only has the "Focus Group Volunteer".
Try this one.
=IF(AND(Internal@row = "No", Type@row = "Focus Group Volunteer"), IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, Type:Type, "Focus Group Volunteer", Internal:Internal, "No", [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholor")), 1), ""))
Dave
-
That worked! Thank you for your help, Dave 😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 146 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!