Auto-numbering sequentially the instances of a certain character/string

[wollyka]

Hi
I attached an excel template of what i want to do.
I want to number the instances of "Yes" in columns B to D (from 1 to Max number of "Yes") sequentially in column A.However, if a row has more than one "Yes", it should be reflected in column A.

I cannot make it work.
Any help is appreciated

 

[Hui]

A2:
=CHOOSE(COUNTIFS(B2:D2,"YES")+1,"",COUNTIFS($B$1:D1,"YES")+1,COUNTIFS($B$1:D1,"YES")+1&","&COUNTIFS($B$1:D1,"YES")+2,COUNTIFS($B$1:D1,"YES")+1&","&COUNTIFS($B$1:D1,"YES")+2&","&COUNTIFS($B$1:D1,"YES")+3)

Copy down

 

[NARAYANK991]

Hi ,

In case you are retaining your helper column F , then you can also use :

in A2 : =IF(F2=0,"",CHOOSE(F2,"1","1,2","1,2,3"))

in A3 and beyond :

=IF(F3=0,"",CHOOSE(F3,(SUM($F1:F$2)+1) & "",(SUM($F1:F$2)+1) & "," & (SUM($F1:F$2)+2),(SUM($F1:F$2)+1) & "," & (SUM($F1:F$2)+2) & "," & (SUM($F1:F$2)+3)))

Narayan

 

[wollyka]

@Hui and @NARAYANK991
Thank you both I did not know about CHOOSE.. I learned something new today
Both solutions work brilliantly