sumproduct?

[Guest]

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

 

[Guest]

Maybe:-

=SUMPRODUCT((A1:A10>D1)*(B1:B10>D2)*(C1:C10=1))

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

Mike

 

[David Biddulph]

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

 

[Guest]

David,

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

Perhaps I meant:-

=SUMPRODUCT(-(A1:A5>D1),-(C1:C5=1))+SUMPRODUCT(-(B1:B5>D2),-(C1:C5=1))-SUMPRODUCT((A1:A5>D1)*(B1:B5>D2)*(C1:C5=1))

But I bet there's a simpler way

Mike

 

[RagDyeR]

Does this work for you?

With D1 = x
and D2 = y

=SUMPRODUCT(((A1:A10>D1)*(B1:B10<D2)*(C1:C10=1))+((B1:B10>D2)*(C1:C10=1)))
--

HTH,

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


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

 

[RagDyeR]

A little shorter:

=SUMPRODUCT((((A1:A10>D1)*(B1:B10<D2))+(B1:B10>D2))*(C1:C10=1))

--

HTH,

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

Does this work for you?

With D1 = x
and D2 = y

=SUMPRODUCT(((A1:A10>D1)*(B1:B10<D2)*(C1:C10=1))+((B1:B10>D2)*(C1:C10=1)))
--

HTH,

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


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