conditional count

[Peter Do]

I have "X" in column A and "Y" in column B, in addition, "X" and "Y" is not
in a same row.
I want to count how many "X" (in column A) if the "Y" are in upper and lower
row (in column B).
eg : A B
X
X
Y
X
Y

I will have 1 "X".
Thank you so much for any help.

 

[T. Valko]

It depends on where this data is located because you have to use a triple
offset of the ranges in a formula.

This is the general syntax:

=SUMPRODUCT(--(A2:A19="x"),--(B1:B18="y"),--(B3:B20="y"))

 

[Peter Do]

I did try this formula but it does not work well.
I want to make the example clearer :

A B
1 x
2 x
3 y
4 y
5 x
6 y
7 x
8 y
9 x
10 x
11 x
12 y
....

In this eg, I have 2 "x" matched the conditions. I call these "x" are
"single x", and I want to count how many "single x" in defined range (eg:
A1:A20).
Thank so much for your quickly help.

 

[T. Valko]

The formula does work. You just have to get the range offsets correct which
is why it would be good idea to tell us *exactly* where your *REAL* data is
located.

This returns 2 based on your sample:

=SUMPRODUCT(--(A2:A11="x"),--(B1:B10="y"),--(B3:B12="y"))

 

[Peter Do]

My problem, in another word, I want to count how many "x" in , for example,
A1:A20 if the cells in upper and lower rows and same column are blank.

 

[Peter Do]

Ya !! The formula does work very very well !! Thanks for your help so much.

 

[T. Valko]

You're welcome. Thanks for the feedback!