Welcome Guest
Log In | Register )
You last visited January 17, 2017, 12:05 pm
All times shown are
Eastern Time (GMT-5:00)

How to count Col B based on Col A

Topic closed. 7 replies. Last post 7 years ago by Raven62.

Page 1 of 1
PrintE-mailLink
KnuckleHead's avatar - box

United States
Member #73037
April 3, 2009
147 Posts
Offline
Posted: August 1, 2009, 12:25 pm - IP Logged

Morning all,

I realise that this is probably simple, but I don't know how to do it.

How do you write a formula that counts all of the "1's" in column "B"that correspond to the "t's" in column "A"?

Col A   Col B

  t          1
  h
  h         2
  t          1
  h         1
  t          1
  h         1
  t
  t          2
  h         1
  t
  t
  t          3
  h         1

  t          1

I "range named" the 2 columns and I used the "Countif", but it counted all of the "1's" instead of just the "t's".

Thanks for any help...

          The only DUMB question is the one question you DID NOT ask...

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Offline
    Posted: August 1, 2009, 7:31 pm - IP Logged

    Knucklehead

    as I recall you use the other spreadsheet so adjust accordingly.  I'm not good enough to combine a countif and an if/and statement.  I use a two step aproach. do the if/and to see if it meets your criteria then do a count on the result.

    you probably know the structure for the if and and  but i'll include it if anyone else is interested.

    =if(and(a3="t",b3=1),1,0)

    leading spaces may not be inportant to a human but it does cause problems for computers so be sure your col a has no leading spaces

    good luck

    p8

      CARBOB's avatar - FL LOTTERY_LOGO.png
      ORLANDO, FLORIDA
      United States
      Member #4924
      June 3, 2004
      5962 Posts
      Offline
      Posted: August 2, 2009, 6:31 am - IP Logged

      Morning all,

      I realise that this is probably simple, but I don't know how to do it.

      How do you write a formula that counts all of the "1's" in column "B"that correspond to the "t's" in column "A"?

      Col A   Col B

        t          1
        h
        h         2
        t          1
        h         1
        t          1
        h         1
        t
        t          2
        h         1
        t
        t
        t          3
        h         1

        t          1

      I "range named" the 2 columns and I used the "Countif", but it counted all of the "1's" instead of just the "t's".

      Thanks for any help...

      Here you are! =SUMPRODUCT(--($A$6:$A$20=$C$6),--(B$6:B$20=$D6)) When you enter the formula use Ctrl-Shift-Enter, it's an array formula.

       

      T1T13
      H  22
      H2 31
      T2   
      H1   
      T1   
      H1   
      T    
      T2   
      H1   
      T    
      T    
      T3   
      H1   
      T1   
        KnuckleHead's avatar - box

        United States
        Member #73037
        April 3, 2009
        147 Posts
        Offline
        Posted: August 3, 2009, 7:33 am - IP Logged

        Morning phileight  , and CARBOB,

         

        I tried both examples.

        phileight, it didn't accept the "and" in that position.

        CARBOB, because Lotus 123 doesn't accept "array" formulas, it didn't work.

        I need to figure it out without trying to create an "array". You both gave me another idea to check out though. I'll let you know if it works. Thank you.

                  The only DUMB question is the one question you DID NOT ask...

          Avatar

          United States
          Member #41846
          June 23, 2006
          460 Posts
          Offline
          Posted: August 3, 2009, 6:07 pm - IP Logged

          knucklehead

           

          that must be another difference, that structure does work in excel

          I used google and entered   lotus if and    it took me to a site that showed how to set up an array formula like carbod suggested.

           

          p8

            KnuckleHead's avatar - box

            United States
            Member #73037
            April 3, 2009
            147 Posts
            Offline
            Posted: August 5, 2009, 9:48 am - IP Logged

            Morning phileight,

            Thank you. I had not discovered that site before.

            I followed your search and discovered what you are talking about.

            I set-up the formula as: @DCOUNT(A1...B101,"LANDS",LANDS="h"#AND#COUNTS="1")

            The formula as shown above equals "25". That is the number of "h's" in column "A" that correspond to the "blank" cells in column "B". The correct answer should be "14". I tried to exchange the places for the "COUNTS" and the "LANDS", that didn't work either. I also tried @DPURECOUNT, it resulted in "ERR". I tried to fill the "blank" cells with a "space", but then the answer came to "0". For some reason, the formula is counting the "blank" cells not the "1" cells.

            Have I made a mistake setting-up the formula? Thanks for assisting...

                      The only DUMB question is the one question you DID NOT ask...

              CARBOB's avatar - FL LOTTERY_LOGO.png
              ORLANDO, FLORIDA
              United States
              Member #4924
              June 3, 2004
              5962 Posts
              Offline
              Posted: August 5, 2009, 12:34 pm - IP Logged

              I have forgot all I ever knew for 1-2-3, but just looking at your formula, what is "Lands"? Look at the format of my formula, one side covers the column with the the values T & H, the other side of the formula covers the values. If lands is the column where the T & H is located, where's the column that covers the values? Also how does your formula count the T's. You want some good advice, drop 1-2-3, go to Excel, less trouble.

                Raven62's avatar - binary
                New Jersey
                United States
                Member #17843
                June 28, 2005
                51019 Posts
                Online
                Posted: August 5, 2009, 5:16 pm - IP Logged

                Morning all,

                I realise that this is probably simple, but I don't know how to do it.

                How do you write a formula that counts all of the "1's" in column "B"that correspond to the "t's" in column "A"?

                Col A   Col B

                  t          1
                  h
                  h         2
                  t          1
                  h         1
                  t          1
                  h         1
                  t
                  t          2
                  h         1
                  t
                  t
                  t          3
                  h         1

                  t          1

                I "range named" the 2 columns and I used the "Countif", but it counted all of the "1's" instead of just the "t's".

                Thanks for any help...

                Method #1:

                Cell C2 thru Cell C16=@IF(A2="T"#AND#B2=1,1,0)

                Cell C17=@SUM(C2..C16)

                ===============================================
                Method #2:

                Cell C2 thru C16=A2&@STRING(B2,0)

                Cell C17=@COUNTIF(C2..C16,"=t1")

                A mind once stretched by a new idea never returns to its original dimensions!