How to count Col B based on Col APrev Topic Next Topic

New Topic New Poll

KnuckleHead

United States
Member #73,035
April 3, 2009
147 Posts
Offline

Aug 1, 2009, 12:25 pm

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

United States
Member #41,845
June 23, 2006
713 Posts
Offline

Aug 1, 2009, 7:31 pm

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

COLUMBUS,GA.
United States
Member #4,924
June 3, 2004
6,719 Posts
Offline

Aug 2, 2009, 6:31 am

Quote: Originally posted by KnuckleHead on Aug 1, 2009

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

KnuckleHead

United States
Member #73,035
April 3, 2009
147 Posts
Offline

Aug 3, 2009, 7:33 am

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

United States
Member #41,845
June 23, 2006
713 Posts
Offline

Aug 3, 2009, 6:07 pm

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

United States
Member #73,035
April 3, 2009
147 Posts
Offline

Aug 5, 2009, 9:48 am

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

COLUMBUS,GA.
United States
Member #4,924
June 3, 2004
6,719 Posts
Offline

Aug 5, 2009, 12:34 pm

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

25

New Jersey
United States
Member #17,842
June 28, 2005
180,983 Posts
Offline

Aug 5, 2009, 5:16 pm

Quote: Originally posted by KnuckleHead on Aug 1, 2009
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!

Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.

Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.

The best way to learn is to never stop being an Experiential Student!

New Topic New Poll

Subscribe to this topic

How to count Col B based on Col APrev TopicNext Topic

How to count Col B based on Col APrev Topic Next Topic