Count consecutively number of text...

[fgtc87]

Hi all,


I have used a formula to output yes and no, now i am trying to figure out how to count how many consecutively yes and no there are in a coloumn


For example

A..... B ......C..... D....... F
ID
1......5......6......4....... No
2......1......8......10..... No
3......3......7......2....... No
4......12....3......1 .......Yes
5......4......2......11..... Yes
6......8......1......3....... Yes
7......12....3......1 .......Yes
8......4......2......11..... No
9......8......1......3....... No


So for example in column F there are 3 number of no.... then 4 number of Yes... and then 2 number of No

Is there a way i can have this probably next to the column F may be in G.

Any help will be greatly appreciated

Thanks in advance

 

[martindwilson]

what answer do you expect?

 

[fgtc87]

what answer do you expect?

A formula to be able to do this if possible, or any other way, ive looked online but so many different answers most of them I could not figure out

Thanks

 

[martindwilson]

thats not the question! is the answer 2 groups of no and 1 group of yes
or
1 group of 3 no
1 group of 4 yes
1 group of 2 no

 

[fgtc87]

Ill try to clarify this i dont want it to say 4 groups of 3 no's (but i am trying to find this question online atm Lol)


Some thing like this below i hope it clarifies my question


A.....B......C..... D.......F..........G
ID
1......5......6......4.......No
2......1......8......10.....No
3......3......7......2.......No........3
4......12....3......1.......Yes
5......4......2......11.....Yes
6......8......1......3.......Yes
7......12....3......1.......Yes.......4
8......4......2......11..... No
9......8......1......3....... No.......2
10......3......7......2.......Yes
11......12....3......1.......Yes
12......4......2......11.....Yes
13......8......1......3.......Yes
14......12....3......1.......Yes.....5
15......4......2......11..... No......1
16......8......1......3.......Yes
17......3......7......2.......Yes
18......12....3......1.......Yes.....3

Thanks

 

[Peter_SSs]

Try this formula in G2, copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	ID*
2	1	5	6	4		No
3	2	1	8	10		No
4	3	3	7	2		No	3
5	4	12	3	1		Yes
6	5	4	2	11		Yes
7	6	8	1	3		Yes
8	7	12	3	1		Yes	4
9	8	4	2	11		No
10	9	8	1	3		No	2
11	10	3	7	2		Yes
12	11	12	3	1		Yes
13	12	4	2	11		Yes
14	13	8	1	3		Yes
15	14	12	3	1		Yes	5
16	15	4	2	11		No	1
17	16	8	1	3		Yes
18	17	3	7	2		Yes
19	18	12	3	1		Yes	3
20
Count

 

[fgtc87]

Thank you Peter_SSs it works well

 

[Peter_SSs]

Thank you Peter_SSs it works well

No problem. Thanks for letting us know.

 

[fgtc87]

Hi,

New Question relating to above example... I would have started new thread but thought this might be easier since it already has my example.


I did some research and wanted to find out for example how many times in Column G the number 3 came or the number 1,2,3,4,5,etc

I found the formula =countif(G$2:G$19,H2) Where Column H would be 1,2,3,4,5,etc

This works well but i need to count every second value.

Just to clarify i need the formula to count Column G but only for 'No' values e.g from above table,

Number.........Occurrence
1...................1
2...................1
3...................1
4...................0
5...................0


Any help will be greatly appreciated

Thanks in advance

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ID							Number	Count
2	1	5	6	4		No		1	1
3	2	1	8	10		No		2	1
4	3	3	7	2		No	3	3	1
5	4	12	3	1		Yes		4	0
6	5	4	2	11		Yes		5	0
7	6	8	1	3		Yes
8	7	12	3	1		Yes	4
9	8	4	2	11		No
10	9	8	1	3		No	2
11	10	3	7	2		Yes
12	11	12	3	1		Yes
13	12	4	2	11		Yes
14	13	8	1	3		Yes
15	14	12	3	1		Yes	5
16	15	4	2	11		No	1
17	16	8	1	3		Yes
18	17	3	7	2		Yes
19	18	12	3	1		Yes	3
20
Count 2