Results 1 to 8 of 8

Thread: Count data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    873
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count data

    Admiral100,

    Thank you for the help.

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

    *BDT.

  5. #5
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Much appreciated with your help.
    *BDT

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Count data

    Quote Originally Posted by bdtran View Post
    Admiral100,
    Thank you for the help.
    Just got a chance today to test your formula and it produced the result I need.
    *BDT.
    Quote Originally Posted by bdtran View Post
    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 …?

  7. #7
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Count data

    Quote Originally Posted by bdtran View Post
    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
    14Your modified12A34A5A6
    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),""))


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •