conditional count
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.
|