witzph1
asked on
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.
SUMPRODUCT-with-OR.xlsx
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)
Or if there is a better way to do it, I'm all ears. :-)
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.
SUMPRODUCT-with-OR.xlsx
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)
Or if there is a better way to do it, I'm all ears. :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
+ 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.
ASKER
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.
you are welcome. i am glad, i was able to help.
ASKER
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.