# SUMPRODUCT, how do you include an OR with it?

How do you include an OR inside a sumproduct? 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? I know 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).

Thanks Mike

-

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 zero. 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 zero. When you add those two 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 http://www.dailydoseofexcel.com/archives/2004/04/05/anatomy-of-an-array-formula/

-
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
``````=OR(B1>100,C1>100)