COUNTIF coll A has x and Coll B has y

  • Thread starter Thread starter jimd
  • Start date Start date
J

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?
 
One way:

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


If you're using XL07 or XL08:

=COUNTIFS(A:A,"x",B:B,"y")
 
Thanks, that worked as long as I didn't use A:A, A1:A100 works. must be the
range is too big.
 
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.
 
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
 
Back
Top