Formula for numbering rows based on specific conditions

Brooks
Brooks ✭✭✭✭
edited 01/17/25 in Formulas and Functions

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

Tags:

Best Answer

  • DKazatsky2
    DKazatsky2 Community Champion
    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

  • DKazatsky2
    DKazatsky2 Community Champion

    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

  • Brooks
    Brooks ✭✭✭✭

    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.

  • DKazatsky2
    DKazatsky2 Community Champion
    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

  • Brooks
    Brooks ✭✭✭✭

    That worked! Thank you for your help, Dave 😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!