COUNTIF coll A has x and Coll B has y

[jimd]

I can use the countif to check for coll. A having "x" =COUNTIF( A:A,"x") I
get 3. but I need to count how many rows also have "Y" in B
A B
x y
z y
x y
x k

in this case, I should get a answer of 2. Any suggestions?

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A100="x"),--(B1:B100="y"))


If you're using XL07 or XL08:

=COUNTIFS(A:A,"x",B:B,"y")

 

[jimd]

Thanks, that worked as long as I didn't use A:A, A1:A100 works. must be the
range is too big.

 

[JE McGimpsey]

Prior to XL07/08, SUMPRODUCT, like all other Array formulae (though you
don't need to use CTRL-SHIFT-ENTER), cannot be used on entire colums.

 

[Shane Devenshire]

Hi,

Just change the reference to read either A2:A65536 or A1:A65535. Just one
cell less than the entire column.

Or you can use the cludgy trick:
=SUMPRODUCT(--(A1:A65535=1))+SUMPRODUCT(--(A65536=1))

If this helps, please click the Yes button

Cheers,
Shane Devenshire