SUMPRODUCT with OR Condition

I've got what is probably a fairly simple problem, but the answer is just not coming to me right now.  

I need to formula to add up a commission array E5:E10 if either of two adjacent arrays, C5:C10 or D5:D10, has a "Y" in it.  In the attached spreadsheet, the correct total would be 120.


I'm pretty sure SUMIF can only have one condition, so I "believe" I need to use SUMPRODUCT formula.  But most of the  instances where I've used a SUMPRODUCT is where I'm test two or three conditions to all be true, such as =SUMPRODUCT ((condition1)*(condition2)*(array)).  But in my current situation, the logic is "if condition 1 or condition 2 multiply array."  I'm just not sure how to format the OR condition.

Or if there is a better way to do it, I'm all ears. :-)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
with SUMPRODUCT you use + sign for OR
* multiple means AND and + plus sign means OR


or like this   =SUMPRODUCT(E3:E8, SIGN(--((C3:C8="Y")+(D3:D8="Y"))))

just to give you a clarification that when you use + in sumproduct it works well, when either of condition will have a value but in cases where you will havee Y in Column C and Column D then it will result 2 and then it will result wrong, therefore I used the SIGN function to rectify that.

Sign function return 1 for a positive number and returns 0 for zero and returns negative for a negative value. hence for 2 there it will return 1 and for all other 1s and 0s it will return the same.  Hence, the SIGN function help comes handy here and then your formula will not double count if values exist in both column.

hope my clarification is clear.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
witzph1Author Commented:
That gives me the right answer.  Great.  

I must admit, I was not familiar with the SIGN function.  I read that it returns the sign of a number, 1 if positive, 0 if zero and -1 if negative.  Easy enough.  Is SIGN then required anytime you need to use OR logic within a SUMPRODUCT?  

What is confusing me is that you say "with SUMPRODUCT you use + sign for OR."  But the + is within the SIGN function, and doesn't appear to be part of the SUMPRODUCT.
ProfessorJimJamMicrosoft Excel ExpertCommented:
if your data can always have either value in column C or if not in column C then in Column D then you can simply use + as OR but if your data can have either on C or D or sometimes in BOTH then in this case you must add SIGN function as well.

+ sign without SIGN only used, if you are sure that your data cannot have value in both columns.

if you are unsure of your data, then there is no harm to use the SIGN function with +   unless you want to double count if value exists in both columns.
witzph1Author Commented:
Perfect.  Thank you so much!  I apologize for asking about the SIGN function when you had already explained it in your original answer.  For some reason, perhaps my window was not fully open, I did not see your explanation.  I only saw the top part.
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome.  i am glad, i was able to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.