Is there any way to next an "or" function within sumproduct?

I have about 1000 lines of data representing individual cases. I want to
create a formula that uses 3 columns A, B and C: Return the # of cases that
demonstrate a value greater than x in column A or y in column B. In
addition, each must also have a "1" in column C to be included. those that
have meet criteria in both column A and B should be counted only once.

anand

Maybe:-

=SUMPRODUCT((A1:A10D1)*(B1:B10D2)*(C1:C10=1))

Where D1 is your X value and D2 is your Y value

Mike

"anand" wrote:

Is there any way to next an "or" function within sumproduct?

I have about 1000 lines of data representing individual cases. I want to
create a formula that uses 3 columns A, B and C: Return the # of cases that
demonstrate a value greater than x in column A or y in column B. In
addition, each must also have a "1" in column C to be included. those that
have meet criteria in both column A and B should be counted only once.

anand

Doesn't that give an AND function, rather than an OR, Mike?
--
David Biddulph

"Mike H" wrote in message
...
Maybe:-

=SUMPRODUCT((A1:A10D1)*(B1:B10D2)*(C1:C10=1))

Where D1 is your X value and D2 is your Y value

"anand" wrote:

Is there any way to next an "or" function within sumproduct?

I have about 1000 lines of data representing individual cases. I want to
create a formula that uses 3 columns A, B and C: Return the # of cases
that
demonstrate a value greater than x in column A or y in column B. In
addition, each must also have a "1" in column C to be included. those
that
have meet criteria in both column A and B should be counted only once.

anand

David,

You are of course correct, perhaps I should learn to read more carefully:)

Perhaps I meant:-

=SUMPRODUCT(-(A1:A5D1),-(C1:C5=1))+SUMPRODUCT(-(B1:B5D2),-(C1:C5=1))-SUMPRODUCT((A1:A5D1)*(B1:B5D2)*(C1:C5=1))

But I bet there's a simpler way

Mike

"David Biddulph" wrote:

Doesn't that give an AND function, rather than an OR, Mike?
--
David Biddulph

"Mike H" wrote in message
...
Maybe:-

=SUMPRODUCT((A1:A10D1)*(B1:B10D2)*(C1:C10=1))

Where D1 is your X value and D2 is your Y value

"anand" wrote:

Is there any way to next an "or" function within sumproduct?

I have about 1000 lines of data representing individual cases. I want to
create a formula that uses 3 columns A, B and C: Return the # of cases
that
demonstrate a value greater than x in column A or y in column B. In
addition, each must also have a "1" in column C to be included. those
that
have meet criteria in both column A and B should be counted only once.

anand

Does this work for you?

With D1 = x
and D2 = y

=SUMPRODUCT(((A1:A10D1)*(B1:B10<D2)*(C1:C10=1))+( (B1:B10D2)*(C1:C10=1)))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"anand" wrote in message
...
Is there any way to next an "or" function within sumproduct?

I have about 1000 lines of data representing individual cases. I want to
create a formula that uses 3 columns A, B and C: Return the # of cases that
demonstrate a value greater than x in column A or y in column B. In
addition, each must also have a "1" in column C to be included. those that
have meet criteria in both column A and B should be counted only once.

anand

A little shorter:

=SUMPRODUCT((((A1:A10D1)*(B1:B10<D2))+(B1:B10D2) )*(C1:C10=1))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Does this work for you?

With D1 = x
and D2 = y

=SUMPRODUCT(((A1:A10D1)*(B1:B10<D2)*(C1:C10=1))+( (B1:B10D2)*(C1:C10=1)))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"anand" wrote in message
...
Is there any way to next an "or" function within sumproduct?

I have about 1000 lines of data representing individual cases. I want to
create a formula that uses 3 columns A, B and C: Return the # of cases that
demonstrate a value greater than x in column A or y in column B. In
addition, each must also have a "1" in column C to be included. those that
have meet criteria in both column A and B should be counted only once.

anand