# 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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.

That was exactly what I needed.

Thanks for the assistance.

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

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.

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?

Nevermind, I was able to come up with it.

Replies
24
Views
840
Replies
7
Views
379
Replies
5
Views
232
Replies
1
Views
84
Replies
14
Views
377

1,220,979
Messages
6,157,182
Members
451,402
Latest member
Lyv

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back