Welcome Guest
You last visited October 22, 2017, 11:31 am
All times shown are
Eastern Time (GMT-5:00)

How to count Col B based on Col A

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

 Page 1 of 1

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

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

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

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6143 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.

 T 1 T 1 3 H 2 2 H 2 3 1 T 2 H 1 T 1 H 1 T T 2 H 1 T T T 3 H 1 T 1

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

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

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

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

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6143 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.

New Jersey
United States
Member #17843
June 28, 2005
67601 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!

 Page 1 of 1