How to count Col B based on Col A

Reply #7

Raven62 — Wed, 05 Aug 2009 21:16:31 GMT

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

Reply #6

CARBOB — Wed, 05 Aug 2009 16:34:58 GMT

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.

Reply #5

KnuckleHead — Wed, 05 Aug 2009 13:48:42 GMT

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... [ More ]

Reply #4

phileight — Mon, 03 Aug 2009 22:07:00 GMT

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

Reply #3

KnuckleHead — Mon, 03 Aug 2009 11:33:40 GMT

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.

Reply #2

CARBOB — Sun, 02 Aug 2009 10:31:34 GMT

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

Reply #1

phileight — Sat, 01 Aug 2009 23:31:14 GMT

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... [ More ]

How to count Col B based on Col A

KnuckleHead — Sat, 01 Aug 2009 16:25:41 GMT

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