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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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.

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" wrote:

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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.

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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" wrote:

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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.

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" wrote:

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" wrote:

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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.

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

"T. Valko" wrote:

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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" wrote:

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
Ya !! The formula does work very very well !! Thanks for your help so
much.

"T. Valko" wrote:

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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" wrote:

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"))

--
Biff
Microsoft Excel MVP


"Peter Do" wrote in message
...
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.