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.
Best Answer
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 is required to apply the "magic" of N() to a range.