SUMIF function. need help!!

  • Thread starter Thread starter Mark Livingstone
  • Start date Start date
M

Mark Livingstone

Hi,

I have the following table:

A: a b b b b a b a a
B: z m m z z m z z z

I calculate "z" by =COUNTIF(A1:A9,"z")

Question:

How do I calculate "z" ONLY if there is "a" somewhere from A1 to A9? I
can do it by individually checking A1, A2, etc.. but how do I
automatically go through the whole column?

Thank you!
 
one way:

=SUMPRODUCT(--(B1:B9="z"),--(A1:A9="a"))

or maybe:

=if(countif(a1:a9,"a")=0,"",countif(b1:b9,"z"))

The first says you have to have a Z and A in the same row.

The second says that if you have any A's, then count the Z's.
 
Back
Top