4

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
  • 1
    @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
4

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.

2

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)

  • 1
    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
  • 1
    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
  • 1
    =sum(arrayformula(A20:A30*1)) – daniel Mar 7 '16 at 13:39
  • @daniel That didn't work.. – rkantos Mar 9 '16 at 5:54
1

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
  • 1
    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
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.