# Boolean values not calculated by =SUM()

I'm trying to use a Google Spreadsheets template called "multiple person expense sharing template"

It seems there is some possible incompatibility with it. I think the template has become incompatible with newer versions of Google Docs. Boolean values are correctly shown but somehow =SUM does not give the correct value if put in a separate cell (eg true=1, false=0). This breaks the template.

The functions which seem to affect it are on the "splits" tab

``````=AND(LEN(D\$1)>0,ISNUMBER(SEARCH(D\$1,'raw data'!\$E5)))
This equals to TRUE/FALSE
``````

which is expected to be calculated as 1 by the number of people splitting function

``````=SUM(D4:AA4)
``````

however it remains at zero

I managed to get the function to work by using extra INT() which gives the integer value of the boolean function..

``````=INT(AND(LEN(D\$1)>0,ISNUMBER(SEARCH(D\$1,'raw data'!\$E5))))
=This equals to 1/0
``````

This seems a bit counter intuitive however. I can't see doing anything wrong and the incompatibility of the template is the only thing I came up with.

• Hi, is it possible to share an example file with us, that reproduces your problem? This way we can have a look and possibly add a solution down the line. – Jacob Jan Tuinstra Mar 6 '16 at 16:30
• @JacobJanTuinstra To reproduce, fill any range with booleans and try to sum them. – user79865 Mar 6 '16 at 16:39
• @JacobJanTuinstra You can find it by googling "multiple person expense sharing template" it should be the first by author "Brian Minton". What Sally suggested also provides the same problems.. – rkantos Mar 7 '16 at 1:18

One alternative is to use N() to convert a boolean to number. It's required in some formula constructs like some that use MMULT.

Applying this to the formula with SUM in the question will result in the following

``````=ARRAYFORMULA(SUM(N(D4:AA4)))
``````

ARRAYFORMULA is required to apply the "magic" of N() to a range.

Indeed, `sum` applied to a range of booleans always returns zero in new Google Sheets: e.g., if A1:A3 are filled with TRUE, FALSE, TRUE, the function `sum(A1:A3)` evaluates to 0. This is despite the fact that `=A1+A2+A3` returns 2. This is a jarring inconsistency, which I would consider a bug.

You already found one workaround: convert the booleans to integers with `int` command. Another one is to use `countif`: for example, replace `=sum(D4:AA4)` by `=countif(D4:AA4, true)`

• I also found a 2nd(third by now) alternative, which doesn't seem as counter intuitive as the INT function.. Replacing SUM with SUMPRODUCT calculates the boolean values correctly as well. I don't quite understand it, since I've never heard about it, but just found it while playing around.. I'm on the verge if I could call it a solution.. hmh – rkantos Mar 7 '16 at 1:24
• It makes sense: calling `sumproduct` with one argument means the second argument is taken to be 1,1,1,... and multiplication by 1 coerces a boolean to integer. Feel free to post this as an answer of your own. – user79865 Mar 7 '16 at 1:42
• Do you know if `sumproduct` can be used in place of `sum` in the same way or does it affect something you can think of? – rkantos Mar 7 '16 at 2:12
• =sum(arrayformula(A20:A30*1)) – daniel Mar 7 '16 at 13:39
• @daniel That didn't work.. – rkantos Mar 9 '16 at 5:54

I ended up using

``````=SUMPRODUCT(D4:AA4)
``````

I still cannot think of a reason better than incompatibility with older Google Spreadsheets. As Sally suggested, this could or should be regarded as a bug.

However when playing around with different functions in Google Spreadsheets, I came across the `SUMPRODUCT` function. It allowed me to modify only the `SUM` function. Thus it was the easiest solution for this problem as I didn't have to replace a much larger amount of Boolean functions in all columns with the integer converting `INT` function. Rubén's suggestion `=ARRAYFORMULA(SUM(N(D4:AA4)))` also worked. For this case the added complexity was not necessary. I'm not advanced enough to comment on the use of `SUMPRODUCT` in all cases; It might not be a solution for everything.

• I upvoted the question few minutes ago, but I don't like the last line: "...just adding the `PRODUCT` after `SUM` was the easiest method...". While it works, I think that being the easiest method is too broad. Perhaps it's the one that requires less keystrokes. By the other hand, it only works with 1D ranges/arrays. – Rubén Mar 9 '16 at 11:05
• @Rubén Are you saying this is only good for this particular problem or that my answer formatting should be different? I will edit it if someone feels it's wrong.. I'm not advanced enough with excel or Google spreadsheets to comment whether using `SUMPRODUCT` for any other use case is a good way forward.. – rkantos Mar 9 '16 at 11:57
• What I said is more close to the first than to the second. Regarding the format, as it is, looks more like a comment than an answer. See How to Answer. – Rubén Mar 9 '16 at 12:21
• @Rubén I agree it looks more like a comment.. However using `SUMPRODUCT` was the answer for this particular problem for me, while I understand that both Sally's and your answer explain the issue and why it is happening more. I will try to rephrase, but I don't want to take credit away from other answers by copying them.. – rkantos Mar 9 '16 at 12:29
• I like that you post an answer about the use of SUMPRODUCT but I don't think that it is "the answer" understanding that as the one and only answer. It could be the accepted answer because it's the one that talks about you the alternative that you choose. Bear in mind that this site it's not only about answering the poster question in order to help only to him/her but to help others that could have the same question. – Rubén Mar 9 '16 at 12:41