Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 6 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

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:


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

share|improve this answer

Here is another solution using multi-conditions in SUMPRODUCT:

=Sumproduct((A3:A159=B3:B159)*(D3:D159={"Target A","Target B"}))
share|improve this answer

You could also range name the list of criterias. So RangeName = Target A and Target B in Cells A1 and A2.

Then =Sumproduct( .... )*(D3:D159=RangeName)

Worked for me.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .