Count data

[bdtran]

Hello All,

I'm trying to get a formula to count with sequence number of "0" and "1" from data row #2 . The result should be looked like
in the row #4 and row #5 . If there is an "A", then I just need to copy it to the same row as number/Letter in the front of it.

I appreciate any help that you can provide.
*BDT


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]data[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]0[/TD]
[TD]A[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]count "0"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]5[/TD]
[TD]A[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]count "1"[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

Try this : (drag as needed)

B4 =IF(B2="A","A",IF(B2=0,COUNTIF($B$2:B2,0),""))
B5 =IF(B2="A","A",IF(B2=1,COUNTIF($B$2:B2,1),""))

 

[jtakw]

Hi,

Your written description and shown sample is a bit unclear for me.

B4 and B5 formulas produces results as I understand your written description.
B7 and B8 formulas produces results as your shown sample.

Formulas copied across:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	data	1	1	A	1	0	0	A	1	1	0	0	A	A	0	A	0
3
4	count "0"			A		1	2	A			3	4	A	A	5	A	6
5	count "1"	1	2	A	3			A	4	5			A	A		A
6
7	count "0"					1	2	A			3	4	A	A	5	A	6
8	count "1"	1	2	A	3				4	5
Sheet333

Cell Formulas
Range	Formula
B4	=IFERROR(IF(B2,"",COUNTIF($B2:B2,0)),B2)
B5	=IFERROR(IF(B2,COUNTIF($B2:B2,1),""),B2)
B7	=IFERROR(IF(B2,"",COUNTIF($B2:B2,0)),IF(OR(A2=0,A7=B2),B2,""))
B8	=IFERROR(IF(B2,COUNTIF($B2:B2,1),""),IF(OR(A2,A8=B2),B2,""))

 

[bdtran]

Admiral100,

Thank you for the help.

Just got a chance today to test your formula and it produced the result I need.

*BDT.

 

[bdtran]

Jtakw,

Sorry for the unclear in my description.

I tested your formulas and it produced the result of B7 and B8 is what I am looking for. .

Much appreciated with your help.
*BDT

 

[jtakw]

Admiral100,
Thank you for the help.
Just got a chance today to test your formula and it produced the result I need.
*BDT.

Jtakw,
I tested your formulas and it produced the result of B7 and B8 is what I am looking for.
Much appreciated with your help.
*BDT

You're welcome, but what you said in Post # 4 and # 5 is contradictory, Admiral100's formulas will produce the Same results as my B4 and B5 formulas, and yet you say his formulas and my B7 & B8 formulas is what you're looking for, But they give Different results …?

 

[bdtran]

Jtakw,

You are corrected. His formulas produced the same results as your B4 and B5. Sorry for the unclear post.

I am actually looking for the exactly result from your formulas B7 and B8.

A minor change of Admiral100's formulas below also produced the same result as your B7 & B8.

B4 =IF(AND(B2="A", A2=0),"A",IF(B2=0,COUNTIF($B$2:B2,0),""))
B5 =IF(AND(B2="A", A2=1),"A",IF(B2=1,COUNTIF($B$2:B2,1),""))


Thanks for catching that.
*BDT

 

[jtakw]

Jtakw,
You are corrected. His formulas produced the same results as your B4 and B5. Sorry for the unclear post.
I am actually looking for the exactly result from your formulas B7 and B8.

A minor change of Admiral100's formulas below also produced the same result as your B7 & B8.

B4 =IF(AND(B2="A", A2=0),"A",IF(B2=0,COUNTIF($B$2:B2,0),""))
B5 =IF(AND(B2="A", A2=1),"A",IF(B2=1,COUNTIF($B$2:B2,1),""))

Thanks for catching that.
*BDT

I don't think so, see in Red below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	data	1	1	A	1	0	0	A	1	1	0	0	A	A	0	A	0
3
6
7	count "0" my formula					1	2	A			3	4	A	A	5	A	6
8	count "1" my formula	1	2	A	3				4	5
9
10
14	Your modified					1	2	A			3	4	A		5	A	6
15	formulas from Post #7	1	2	A	3				4	5
Sheet333

Cell Formulas
Range	Formula
B7	=IFERROR(IF(B2,"",COUNTIF($B2:B2,0)),IF(OR(A2=0,A7=B2),B2,""))
B8	=IFERROR(IF(B2,COUNTIF($B2:B2,1),""),IF(OR(A2,A8=B2),B2,""))
B14	=IF(AND(B2="A", A2=0),"A",IF(B2=0,COUNTIF($B$2:B2,0),""))
B15	=IF(AND(B2="A", A2=1),"A",IF(B2=1,COUNTIF($B$2:B2,1),""))