Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How do you include an OR inside the sumproduct function in Excel? I currently use two sumproduct formulas because I have two variables I want it to count:

=Sumproduct((A3:A159=B3:B159)*(D3:D159="Target A"))
=Sumproduct((A3:A159=B3:B159)*(D3:D159="Target B"))

Is there a simple way to write that in one formula? It's not killing me to write it in two, but if it can be done better, then I'm all ears - always willing to learn.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

The "plus sign" (+) is for OR in array formulas (and SUMPRODUCT).

=Sumproduct((A3:A159=B3:B159)*((D3:D159="Target A")+(D3:D159="Target B"))

With SUMPRODUCT, every comparison is evaluated to TRUE and FALSE. In Excel, TRUE can be represented by any non-zero number, while FALSE is equivalent to 0. If D3="Target A", the first element of that array will be 1 (True=1). That means that D3 cannot be Target B and that element of the that array will be 0. When you add them together, it will be 1, and so will be counted as TRUE.

It can be tough to get your head around how arrays work in formulas. Try reading this.

share|improve this answer
    
Works like a charm. I'm a big fan of Sumproduct and the + sign just adds another bit of icing on the cake. –  RocketGoal Jan 20 '10 at 11:47
add comment

There is an OR function in Excel: OR(logical1, [logical2], [logical3], ...)

This example will be TRUE if either C1 OR B1 contain values greater than 100 or FALSE if they both don't:

=OR(B1>100,C1>100)

I'm not 100% sure how you want to apply this, but I'm sure this can help.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.