2

I decided to learn how to work with the function SumProduct(), so I created this very basic Excel sheet:

A    B
1    1
2    2

I calculated =SumProduct(A2:A3,B2:B3) and the result was 5, which was as expected.

Then I got the idea to replace B2 by =(1=1), which obviously is TRUE, which is confirmed by what I see.

However, the =SumProduct() result changed into 4, apparently booleans are treated as zero in Excel.
So, I decided to write another formula: =A2*B2+A3*B3 (which is exactly the same as the SumProduct()), but instead of getting the expected value 4, the value remained 5.

So, apparently, regular mathematical operators (*, +, ...) treat boolean values as "to be expected" (TRUE becomes 1 and FALSE becomes 0), but for some more elaborated functions (like SumProduct()) this seems not to be the case.

What's the explanation behind this? I would like to understand what's happening in order to avoid unpleasant surprises. I think I'm working with Excel-365 (how can I be sure?).

Dominique
  • 16,450
  • 15
  • 56
  • 112

1 Answers1

2

Refer to this document: https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

which explains that you need to prepend -- before your value in order to convert it into the 1 or 0 you have expected. Thus, the function does not support boolean values, as you have correctly observed. You can also use an IF function whose first parameter is the logical expression that you have, the second parameter being 1 and the third being 0. As about the reason for the lack of support for implicit conversion of logical data into arithmetic data at this function, I think we will not be able to factually determine its reason at an online forum, but that is rather a question that you could direct to the authors of the function.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • So basically you are saying "*Most Excel functions should handle booleans in a correct way, but `SumProduct()` is a known exception, for which the double negation trick can be used.*"? – Dominique Sep 28 '21 at 12:57
  • That is why you'd mostly discard the 2nd parameter and write `=SUMPRODUCT(A2:A3*B2:B3)` @Dominique. The conversion of boolean to numeric is done in the 1st parameter in the same fashion as you wrote `=A2*B2+A3*B3`. Another example is `MMULT()` that will not handle boolean input. Good question though =) – JvdV Sep 28 '21 at 13:06
  • @JvdV: your comment makes it really bizar: `=sumproduct(A2:A3,B2:B3)` means the same as `=sumproduct(A2:A3*B2:B3)`, but why would you want to write a multiplication (`*`) when you already mention a multiplication in your function (after all, the function is called Sum**Product**())? ... and in top of this, there seem to be cases where both are not equal anyway. (I have the strong impression that this function has not been tested very thouroughly :-) ) – Dominique Sep 28 '21 at 13:25
  • 3
    It's not that bizar IMHO. The function itself is not going to work through a hardcoded multiplication like `*` would. Just like `SUM(1,2,TRUE)` will still ignore the boolean values and return `3`. In that regard, `SUMPRODUCT()` does not stand alone. It needs *numeric* input. Whereas boolean values *can* be used in hardcoded calculations, they have no meaning to these functions on itself and will be discarded. Therefor we usually perform the hardcoded multiplication inside the 1st parameter. I hope that is more clear, as I'm not sure if what makes sense to me can come across that well =). – JvdV Sep 28 '21 at 13:34
  • @Dominique there are multiple functions with this approach. `SUM` is another example, as JvdV pointed out. I agree with you that it would be good if boolean values could be implicitly converted into integers and, like you, I do not understand why didn't the authors choose this approach. However, what I **do** know is that this is not supported by some functions, `sumproduct` & co. – Lajos Arpad Sep 28 '21 at 15:23