counting in sequence of appearance of a string on a column

[Abdmujib]

I have a column B, which has a string of "yes" and "No". I want to find a way to number in sequence on column C, where Yes appears. when "No" occurs it should leave a blank space and start all over from 1 for appearance of "Yes. I want for "No" on column C

attached is the picture of how i want it to be.

 

[Trebor76]

Hi Abdmujib,

Assuming the Yes/No flags are in the range B3:B17 try these formulas in the initial cell stated and then copy them down to Row 17:

Cell C3 =IF(B3<>"Yes","",IF(B2="Yes",C2+1,1))
Cell D3 =IF(B3<>"No","",IF(B2="No",D2+1,1))

I think your image is out as there's there's two No's about half way down but only the first is flagged.

Regards,

Robert

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C
1		If Yes	if No
2	Yes	1
3	No		1
4	Yes	1
5	Yes	2
6	Yes	3
7	No		1
8	No
9	Yes	1
10	No		1
11	Yes	1
12	No		1
13	No		2
14	No		3
15	No		4
16	No		5
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=IFS(AND(A2="Yes",OR(A1="No",A1="")),1,AND(A2="Yes",A1="Yes"),B1+1,TRUE,"")
C2:C16	C2	=IFS(AND(A2="No",A1="Yes"),1,AND(A2="No",A1="No",OR(A3="No",A3="")),C1+1,TRUE,"")

 

[Peter_SSs]

Another option.

23 12 11.xlsm
	B	C	D
1
2		If Yes	if No
3	Yes	1
4	No		1
5	Yes	1
6	Yes	2
7	Yes	3
8	No		1
9	No		2
10	Yes	1
11	No		1
12	Yes	1
13	No		1
14	No		2
15	No		3
16	No		4
17	No		5
Yes No (2)

Cell Formulas
Range	Formula
C3:C17	C3	=IF(B3="Yes",N(C2)+1,"")
D3:D17	D3	=IF(B3="No",N(D2)+1,"")

 

[Rick Rothstein]

Since you are using XL365, you could put this single formula in cell C3 (do not copy it down) and let it spill all of the "Yes" sequence counts...

=SCAN("",B3:B17,LAMBDA(a,x,IF(x="Yes",(0&a)+1,"")))

For the "No" sequence counts, put this single formula in cell D3 (do not copy it down)...

=SCAN("",B3:B17,LAMBDA(a,x,IF(x="No",(0&a)+1,"")))

Change the B3:B17 in each formula to match your actual range containing your "Yes" and "No" values.

 

[SunnyAlv]

Since you are using XL365, you could put this single formula in cell C3 (do not copy it down) and let it spill all of the "Yes" sequence counts...

=SCAN("",B3:B17,LAMBDA(a,x,IF(x="Yes",(0&a)+1,"")))

For the "No" sequence counts, put this single formula in cell D3 (do not copy it down)...

=SCAN("",B3:B17,LAMBDA(a,x,IF(x="No",(0&a)+1,"")))

Change the B3:B17 in each formula to match your actual range containing your "Yes" and "No" values.

@Rick Rothstein
I was looking for something like this, and you found it and it worked, but why is it that a few moments later the formula becomes

#Value

 

[Rick Rothstein]

@Rick Rothstein
I was looking for something like this, and you found it and it worked, but why is it that a few moments later the formula becomes

#Value

What do you mean "later the formula becomes #Value? As far as I know, that cannot happen on its own. What did you do between the time the formula was placed in the cell and when the error appeared? If nothing, do you have any VBA code, specifically event code, running in the workbook?

 

[SunnyAlv]

What do you mean "later the formula becomes #Value? As far as I know, that cannot happen on its own. What did you do between the time the formula was placed in the cell and when the error appeared? If nothing, do you have any VBA code, specifically event code, running in the workbook?

I mean when I copy the formula, it works in a moment, but a few minutes later it becomes value

 

[Rick Rothstein]

I mean when I copy the formula...

Did you miss the part in my post where I said "do not copy it down"? You are using XL365... that single formula will spill the full result for you automatically.