Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts.
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error #VALUE!.

Try =SUM(VALUE(A1:A6)) to confirm. Also use View > Value Highlighting Ctrl+F8 to check for my thesis.

(Down with silly automatisms!)

The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts.
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error #VALUE!.

Try =SUM(VALUE(A1:A6)) {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use View > Value Highlighting Ctrl+F8 to check for my thesis.

(Down with silly automatisms!)

The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts.
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error #VALUE!.

Try {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use View > Value Highlighting Ctrl+F8 to check for my thesis.

A valuable way to always clearly distinguish text from numbers is to NOT set an explicit alignment for the respective cells. The default alignment aligns texts left and numbers right.

(Down with silly automatisms!)

The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts. texts, and that the only true number in the range is in A3.
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error #VALUE!.

Try {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use View > Value Highlighting Ctrl+F8 to check for my thesis.

A valuable way to always clearly distinguish text from numbers is to NOT set an explicit alignment for the respective cells. The default alignment aligns texts left and numbers right.

(Down with silly automatisms!)

The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts, and that the only true number in the range is in A3.
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error #VALUE!.

Try {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use View > Value Highlighting Ctrl+F8 to check for my thesis.

A valuable way to always clearly distinguish text from numbers is to NOT set an explicit alignment for the respective cells. The default alignment aligns texts left and numbers right.

(Down Down with silly automatisms!)automatisms! (There are a few clever ones, too.)

(Explaining the comment by @Mike Kaganski to more detail:)

The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts, and that the only true number in the range is in A3.
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error #VALUE!.

Try {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use View > Value Highlighting Ctrl+F8 to check for my thesis.

A valuable way to always clearly distinguish text from numbers is to NOT set an explicit alignment for the respective cells. The default alignment aligns texts left and numbers right.

Down with silly automatisms! (There are a few clever ones, too.)