# Thread: Count data Thanks: 0 Likes: 0

1. ## Count data

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

 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" 1 2 A 3 4 A A 5 A 6 5 count "1" 1 2 A 3 4 5

2. ## Re: Count data

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),""))

3. ## Re: Count data

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:

ABCDEFGHIJKLMNOPQ
1
2data11A100A1100AA0A0
3
4count "0"A12A34AA5A6
5count "1"12A3A45AAA
6
7count "0"12A34AA5A6
8count "1"12A345

Sheet333

Worksheet Formulas
CellFormula
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,""))

4. ## Re: Count data

Thank you for the help.

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

*BDT.

5. ## Re: Count data

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. .

*BDT

6. ## Re: Count data

Originally Posted by bdtran
Thank you for the help.
Just got a chance today to test your formula and it produced the result I need.
*BDT.
Originally Posted by bdtran
Jtakw,
I tested your formulas and it produced the result of B7 and B8 is what I am looking for.
*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 …?

7. ## Re: Count data

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

8. ## Re: Count data

Originally Posted by 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
I don't think so, see in Red below:

ABCDEFGHIJKLMNOPQ
2data11A100A1100AA0A0
3
6
7count "0" my formula12A34AA5A6
8count "1" my formula12A345
9
10
15formulas from Post #712A345

Sheet333

Worksheet Formulas
CellFormula
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),""))