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.

4 Answers 4


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, 2010 at 11:47
  • 1
    The parentheses don't match. Aug 27, 2018 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:


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, 2017 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.

You must log in to answer this question.

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