Home » Questions » Computers [ Ask a new question ]

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

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:

Asked by: Guest | Views: 72
Total answers/comments: 2
Guest [Entry]

"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."
Guest [Entry]

"Here is another solution using multi-conditions in SUMPRODUCT:

=Sumproduct((A3:A159=B3:B159)*(D3:D159={""Target A"",""Target B""}))"