I'm trying to update a working solution of incrementing one ID based on several conditions, so I was using the ROW() function without any issue. But now I'm trying to increment 2 different IDs based on selected option as shown in the screenshot below, where I've started the following so far:
=ARRAYFORMULA(IF(LEN(A2:A),COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A),A2:A,"Option 2"),))
Can anyone bring some light on this scenario: thanks
Link of spreadsheet illustrating my situation: here
Option 1/Option 2 or not. A way to do this without using OR (which can't be iterated over an array) is this:IF(A2:A="Option 1",0,1)*IF(A2:A="Option 2",0,1)
IF so that the returned value depends on whether the previous condition is true. So, if option is not 1 nor 2, the corresponding value should result from the count of all the previous values which are not 1 or 2. So the COUNTIFS should check if the option is not 1 nor 2. Something like this:29999 + COUNTIFS(A2:A,"<>Option 1",A2:A,"<>Option 2",ROW(A2:A), "<="&ROW(A2:A))
1 or 2, the returned value should result from hte count of all previous 1 and 2 values. Since that's an OR condition, you have to sum two different COUNTIFS, one for option 1 and one for 2. Could be like this:9999 + COUNTIFS(A2:A,"=Option 1",ROW(A2:A), "<="&ROW(A2:A)) + COUNTIFS(A2:A,"=Option 2",ROW(A2:A), "<="&ROW(A2:A))
=ARRAYFORMULA(IF(LEN(A2:A),IF(IF(A2:A="Option 1",0,1)*IF(A2:A="Option 2",0,1),
29999 + COUNTIFS(A2:A,"<>Option 1",A2:A,"<>Option 2",ROW(A2:A), "<="&ROW(A2:A)),
9999 + COUNTIFS(A2:A,"=Option 1",ROW(A2:A), "<="&ROW(A2:A)) + COUNTIFS(A2:A,"=Option 2",ROW(A2:A), "<="&ROW(A2:A))),""))
slight alternative:
=ARRAYFORMULA(IF(A2:A="",,IF(REGEXMATCH(A2:A, H2&"$|"&H3&"$"),
9999+COUNTIFS(REGEXMATCH(A2:A, H2&"$|"&H3&"$"),
REGEXMATCH(A2:A, H2&"$|"&H3&"$"), ROW(A2:A), "<="&ROW(A2:A)),
29999+COUNTIFS(A2:A, "<>"&H2, A2:A, "<>"&H3, ROW(A2:A), "<="&ROW(A2:A)))))
