Create Order of Response If Text is in Cell

Sarah123

I have a multiple select as part of a form. I want to create a column that looks for a specific text within the multiple select and create an order of response. So if multiple forms are submitted with the item A selected in them, I would like the first submitted be an order of 1 while the 2nd time item A is selected then it would an order of 2, and so on. Thank you!

Paul Newcome

You would need an auto-number column with no special formatting to first indicate the order of the form submissions. Then you would use a COUNTIFS to count how many cells containing "A" were submitted (have a lower auto-number) than "@row".

=COUNTIFS([Multi-Select]:[Multi-Select], HAS(@cell, "A"), [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row)

Paul Newcome

You would drop the COUNTIFS into the output if true section of an IF statement.

=IF(HAS([Multi-Select]@row, "A"), COUNTIFS(...............))

Paul Newcome

You would need an auto-number column with no special formatting to first indicate the order of the form submissions. Then you would use a COUNTIFS to count how many cells containing "A" were submitted (have a lower auto-number) than "@row".

=COUNTIFS([Multi-Select]:[Multi-Select], HAS(@cell, "A"), [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row)

Sarah123

Thank you very much! Is there anything to have the cell blank if the text isn't found in the cell? If not, it's no worries since this will work for what I'm looking for.

Paul Newcome

You would drop the COUNTIFS into the output if true section of an IF statement.

=IF(HAS([Multi-Select]@row, "A"), COUNTIFS(...............))