Sumproduct with an added "OR" condition

johnny_trauma

New Member
Joined
Sep 12, 2006
Messages
14
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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
Joined
Sep 12, 2006
Messages
14
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?
 

Forum statistics

Threads
1,141,915
Messages
5,709,320
Members
421,628
Latest member
Big Boss

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?

Disable AdBlock

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
Top