SUMPRODUCT, how do you include an OR with it?

Question

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

Answer 1

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/

Answer 2

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.