conditional count

  • Thread starter Thread starter Peter Do
  • Start date Start date
P

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.
 
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"))
 
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.
 
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"))
 
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.
 
Back
Top