SUMPRODUCT Function

[chief_abound]

Snap11.jpg

why both results are different ? SUMPRODUCT({FALSE;FALSE;TRUE},{1;2;3}) <> =SUMPRODUCT({0;0;1},{1;2;3})

i want to know why true , false cannot be converted to 1,0 automatically in sumproduct function ?

[XOR LX]

Hi,

A good question.

in order to coerce Boolean TRUE/FALSE entries into their numerical equivalents (TRUE=1, FALSE=0), we must either use some function designed for that purpose, i.e. N, or else apply a suitable mathematical operation, e.g. multiplication, as can be seen if we use the "product" version equivalent of the formula you give, i.e.:

=SUMPRODUCT({FALSE;FALSE;TRUE}*{1;2;3})

According to the official Microsoft site:

"SUMPRODUCT treats array entries that are not numeric as if they were zeros."

(https://support.office.com/en-us/art...c-4d2145a2fd2e)

Conclusion: the above statement applies equally to Booleans.

Regards

[crzu]

To add on, this is why in many formula for sumproduct. You could see people add "--", "*1", "*". This is to convert True and False from text to numerical, thus the sumproduct is able to multiply.

[chief_abound]

Hi crzu thank you!

[chief_abound]

Hi XOR LX

Thank you very much!

[Tony Valko]

[Almost] everything you always wanted to know about SUMPRODUCT (but were afraid to ask)...

http://xldynamic.com/source/xld.SUMPRODUCT.html

[chief_abound]

Thank you so much!

[Tony Valko]

You're welcome!