# How do you include an OR inside a SUMPRODUCT function, in Microsoft Excel?

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.

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.

• 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
• The parentheses don't match. – user3932000 Aug 27 '18 at 17:17

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.

Here is another solution using multi-conditions in SUMPRODUCT:

``````=Sumproduct((A3:A159=B3:B159)*(D3:D159={"Target A","Target B"}))
``````
• This does not work for me. I don't know if excel version is causing trouble but i can only use {} in sumifs. However this will work : `=Sumproduct((A3:A159=B3:B159)*(D3:D159=TRANSPOSE({"Target A","Target B"})))` – Lich4r Jun 13 '17 at 13:23

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.