Sumproduct with an added "OR" condition

[johnny_trauma]

Hi, I'm hoping someone can help me with the following problem.

I am currently using a Sumproduct formula to provide a cell count when both x and y are true. I now have to add a third condition to this statement to modify it to count if x and (y or z) are true.

I've tried to incorporate the OR statement into my original formula, but to no success.

Any suggestions?

 

[just_jon]

Welcome to MrExcel - a couple ways --

=SUMPRODUCT(--(RangeA=x),(RangeB=y)+(RangeB=z),RangeC)

=SUMPRODUCT(--(RangeA=x),--(ISNUMBER(MATCH(RangeB,yzRange,0))),RangeC)

Where yzRange is a 2-cell x 1 column range holding the y/z criteria.

 

[johnny_trauma]

That was exactly what I needed.

Thanks for the assistance.

 

[pgc01]

Hi just_jon

About your first formula. I believe that with the OR, you have to account for the case where both conditions are true. Since your are adding the conditions you'd get for that case the value 2 instead of 1.

For the general case of the OR where the n conditions may be non-exclusive its better to test against zero or use the SIGN function

In this case:

=SUMPRODUCT(--(RangeA=x),--(((RangeB=y)+(RangeB=z))>0),RangeC)

or

=SUMPRODUCT(--(RangeA=x),sign((RangeB=y)+(RangeB=z)),RangeC)

Cheers
PGC

 

[just_jon]

About your first formula. I believe that with the OR, you have to account for the case where both conditions are true. Since your are adding the conditions you'd get for that case the value 2 instead of 1.

I was assuming that the =y/=z criteria were mutually exclusive, and could not therefore ever both be True.

I may have erred by assuming too much ... thanks for pointing that out.

 

[johnny_trauma]

Along a similar topic, how would one set this formula up in order to calculate percentiles, taking the above AND / OR requirements into consideration?

I was using the formula

=percentile(if(rangeA=x,if(rangeB=y,rangeC)),0.25)

with Ctrl+Shift+Enter for the two requirements of x and y. Now if I wanted to incorporate x + y or z what would you suggest?

 

[johnny_trauma]

Nevermind, I was able to come up with it.