# Sumproduct with an added "OR" condition

#### johnny_trauma

##### New Member
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?

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### just_jon

##### Legend
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

##### New Member
That was exactly what I needed.

Thanks for the assistance.

#### pgc01

##### MrExcel MVP
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

##### Legend

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

##### New Member
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

##### New Member
Nevermind, I was able to come up with it.

Replies
1
Views
24
Replies
0
Views
68
Replies
34
Views
1K
Replies
13
Views
344
Replies
18
Views
279