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?
 

Some videos you may like

Excel Facts

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

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,870
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,090
Messages
5,545,899
Members
410,711
Latest member
Josh324
Top